Hellow everyone,
I am calling/manipulating Excel spreadsheet through VB 6.0. Most of my objectives are accomplished except some cosmetics. Problem#1:
For example, after running my program to process a single record, when I proceed to print it, it prints the first page containing the processed record. It also prints 5 more blank pages without any record. Problem#2:
Also, my intention is to print an Underline after writing each record, but the underlines are printed whether a record is written or not.
How can I get it to prints only the area that has data and draw an underline only when a record is processed and written?
Below is my entire module: -
-
ii = 5
-
w = 0
-
For Each R In xlWksht. Range("A5:N5"): w = w + R.ColumnWidth: Next
-
-
rht = xlWksht.Range("A5").RowHeight
-
-
Do Until M.qBW.EOF = True
-
ii = ii + 2
-
xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
-
xlWksht.Cells(ii, 2).Value = M.qBW![Description]
-
xlWksht.Cells(ii, 3).Value = ""
-
xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
-
xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
-
-
SrchCriteria = "[Name]= " & "'" & M.qBW![Personnel2] & "'"
-
rsinPers.FindFirst SrchCriteria
-
If rsinPers.NoMatch = False Then
-
xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
-
End If
-
-
SrchCriteria = "[Name]= '" & M.qBW![Personnel3] & "'"
-
rsinPers.FindFirst SrchCriteria
-
If rsinPers.NoMatch = False Then
-
xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
-
End If
-
-
SrchCriteria = "[Name]= '" & M.qBW![Personnel4] & "'"
-
rsinPers.FindFirst SrchCriteria
-
If rsinPers.NoMatch = False Then
-
xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
-
End If
-
-
SrchCriteria = "[Name]= '" & M.qBW![Personnel5] & "'"
-
rsinPers.FindFirst SrchCriteria
-
If rsinPers.NoMatch = False Then
-
xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
-
End If
-
-
SrchCriteria = "[Name]= '" & M.qBW![Personnel6] & "'"
-
rsinPers.FindFirst SrchCriteria
-
If rsinPers.NoMatch = False Then
-
xlWksht.Cells(ii, 10).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
-
End If
-
-
xlWksht.Cells(ii, 11).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
-
xlWksht.Cells(ii, 12).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
-
xlWksht.Cells(ii, 13).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
-
xlWksht.Cells(ii, 14).Value = M.qBW![End Date] & Chr(10) & M.qBW![End Date]
-
-
If M.qBW![Comments] = "" Or IsNull(M.qBW![Comments]) Then
-
mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
-
Else
-
mystr = "Comments:" & "'" & xlApp.Clean(Trim(M.qBW![Comments]))
-
End If
-
-
Do
-
Pos = InStr(Pos + 1, mystr, ":")
-
If Not Pos = 0 Then
-
If Mid(mystr, Pos - 5, 1) = "/" Then
-
mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
-
Pos = Pos + 2
-
End If
-
End If
-
Loop While Not Pos = 0
-
-
-
xlWksht.Cells(ii + 1, 1).Value = "Comments:"
-
xlWksht.Cells(ii + 1, 2).Value = Mid(mystr, 11)
-
-
With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
-
.HorizontalAlignment = xlLeft
-
.VerticalAlignment = xlTop
-
.WrapText = True
-
.Orientation = 0
-
.MergeCells = True
-
.RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
-
End With
-
-
xlWksht.Columns("A:A").ColumnWidth = 9.15
-
-
'Draw Underline after each record:
-
'---------------------------------
-
TStr = "A" & CStr(ii + 1) & ":N" & CStr(ii + 1)
-
xlWksht.Range(TStr).Select
-
If Not IsEmpty(Selection.Range("A1")) Then 'check if first cell is empty
-
With xlWksht.Range(TStr).Borders(xlEdgeBottom)
-
.LineStyle = xlDouble
-
.Weight = xlThin
-
.ColorIndex = xlAutomatic
-
End With
-
End If
-
M.qBW.MoveNext
-
Loop
-
-
xlWksht.PageSetup.LeftFooter = " Legend:" & Chr(10) & "See Estimated Actual Tot.Hrs. Column: Top Number = Estimated Hrs. and Bottom Number = Actual Hrs." & Chr(10) & "See Estimated Actual Start/End Dates Columns: Top dates = Estimated Start/End dates and Bottom dates = Actual Start/End dates"
-
-
-
strAnswer = MsgBox("Would you like to Print this spreadsheet?", vbYesNo, "Print Option")
-
-
If strAnswer = vbYes Then
-
Goto PrintSpreadsheet
-
Else
-
Goto ViewSpreadsheet
-
End If
-
-
PrintSpreadsheet:
-
'Set up Print area:
-
'-----------------
-
xlWksht.PageSetup.PrintArea = xlWksht.Range("A1:" & xlWksht.Cells.SpecialCells(xlCellTypeLastCell).Address)
-
-
xlApp.ActiveWorkbook.Save
-
-
xlApp.Visible = True
-
xlApp.UserControl = True
-
-
Goto CloseAllObjects
-
Thanks.
GiftX.
0 1184 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: B-Dog |
last post by:
I'm using the code from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwinforms/html/printwinforms.asp
to print some info from a database and I've struggled through customizing
the report to fit what I need and thought I had it all working great since
my print preview looked great but when I hit the print button I get blank
pages at the printer. I've tried two different printers with the same
results. Any ideas?
|
by: Andreas Bauer |
last post by:
Hi,
I have to audit some c# code. I know in the options I can adjust how
the code should be formatted while entering it. But is there any way to
apply afterwards a code template to the classes to adjust their
appearance (except ctrl-k ctrl-f)? Foremost how to clear unnecessary
blank lines, e.g reduce 4 blank lines to one. I don´t want to change
every class manually.
Thanks in advance for any hint.
|
by: =?Utf-8?B?Q3ludGlh?= |
last post by:
I'VE ALREADY CHANGED INK CARTRIDGES, UNINSTALLED AND REINSTALLED THE PRINTER,
RESTARTED MY COMPUTER ETC... AND STILL PAGES COME OUT BLANK... THIS PRINTER
WAS WORKING FINE JUST BEFORE!!
CAN SOMEBODY HELP?? THANKS!
--
Cyntia
|
by: TMS |
last post by:
The spreadsheet is due today and i needed help with the last two parts of the spreadsheet, so I went to my teacher. My spreedsheet was working BETTER before he made me change it. Now it prints a widget address in one of the cells for no apparant reason (I can't locate why the event is doing that), and it doesn't evaluate.
He wants me to use 2 dictionaries:
One that holds the cell number and expression, and
Two that holds the result of...
|
by: mix01 |
last post by:
Hi,
I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help.
Many thanks,
Mix01
Version of the program
| |
by: D.Stone |
last post by:
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmatically using TransferSpreadsheet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E". The import works, but I get a table with 64K rows,
all but 43 being blank!
|
by: SMERSH009 |
last post by:
Hi All.
Let's say I have some badly formatted text called doc:
doc=
"""
friendid
Female
23 years old
|
by: GiftX |
last post by:
Good morning members.
I am using VB6 to generate a spreadsheet. My module is working okay but one of my objectives is to print an underline after writing each record in the spreadsheet, just to show where one record ends and the next begins. However, even if I process only one record and try to print it, it prints several blank pages including underlines, even if there are no records.
How can I instruct it to strictly print an underline...
|
by: Perl Beginner |
last post by:
I am using Win32. I have created an excel spreadsheet, formatted the columns and rows, and would like to write to the cells…all of this using Spreadsheet::WriteExcel. My issue is, my script is very vast with a lot of subroutines, but I need the excel spreadsheet created in the main subroutine becasue this is where the data is that i want to capture. So if I create and format the spreadsheet within the main subroutine, and as it loops through,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |