468,539 Members | 1,737 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

VB 6.0 And spreadsheet - Printing problem

7
VB 6.0 And Spreadsheet - Printing problem
Good morning West.
After much research, I found this code. It scans the spreadsheet and returns up to the last used cell.

Expand|Select|Wrap|Line Numbers
  1.  xlWksht.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
  2.  
Note: The above code returned all the data in the used cells.

Print Problem#1:
Based on the code above, I execute this code:
Expand|Select|Wrap|Line Numbers
  1. Selection.PrintOut
But instead of printing the data returned with the previous code, it skips Column "A" and prints only the data in column "B1:N1", ignoring all the data from "B2:N2 and below.
How can I get it to print all and only the data in the used area?
Below is my entire Module:
Expand|Select|Wrap|Line Numbers
  1.    recordcnt = 0
  2.     ii = 5
  3.      w = 0
  4.     For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
  5.  
  6.     rht = xlWksht.Range("A5").RowHeight
  7.  
  8.     'Do Until M.qBW.EOF = True
  9.      Do While Not M.qBW.EOF
  10.         ii = ii + 2
  11.         xlWksht.Cells(ii, 1).Value = M.qBW![Req No]
  12.         xlWksht.Cells(ii, 2).Value = M.qBW![Description]
  13.         xlWksht.Cells(ii, 3).Value = ""
  14.         xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status]
  15.         xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs]
  16.  
  17.         SrchCriteria = "[Name]= " & "'" & M.qBW![Personnel2] & "'"
  18.         rsinPers.FindFirst SrchCriteria
  19.         If rsinPers.NoMatch = False Then
  20.            xlWksht.Cells(ii, 6).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg2Hrs]
  21.         End If
  22.  
  23.         SrchCriteria = "[Name]= '" & M.qBW![Personnel3] & "'"
  24.         rsinPers.FindFirst SrchCriteria
  25.         If rsinPers.NoMatch = False Then
  26.            xlWksht.Cells(ii, 7).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg3Hrs]
  27.         End If
  28.  
  29.         SrchCriteria = "[Name]= '" & M.qBW![Personnel4] & "'"
  30.         rsinPers.FindFirst SrchCriteria
  31.         If rsinPers.NoMatch = False Then
  32.            xlWksht.Cells(ii, 8).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg4Hrs]
  33.         End If
  34.  
  35.         SrchCriteria = "[Name]= '" & M.qBW![Personnel5] & "'"
  36.         rsinPers.FindFirst SrchCriteria
  37.         If rsinPers.NoMatch = False Then
  38.            xlWksht.Cells(ii, 9).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg5Hrs]
  39.         End If
  40.  
  41.         SrchCriteria = "[Name]= '" & M.qBW![Personnel6] & "'"
  42.         rsinPers.FindFirst SrchCriteria
  43.         If rsinPers.NoMatch = False Then
  44.            xlWksht.Cells(ii, 10).Value = rsinPers![Initials] & Chr(10) & M.qBW![TotalProg6Hrs]
  45.         End If
  46.  
  47.         xlWksht.Cells(ii, 11).Value = "-" & Chr(10) & M.qBW.Fields("Per Hrs")
  48.         xlWksht.Cells(ii, 12).Value = M.qBW.Fields("EstimatedTotalHours") & Chr(10) & M.qBW.Fields("Tot Hrs")
  49.         xlWksht.Cells(ii, 13).Value = M.qBW![Start Date] & Chr(10) & M.qBW![Start Date]
  50.         xlWksht.Cells(ii, 14).Value = M.qBW![End Date] & Chr(10) & M.qBW![End  Date]
  51.  
  52.         If M.qBW![Comments] = "" Or IsNull(M.qBW![Comments]) Then
  53.            mystr = "Comments:" & Chr(10) & "NO COMMENTS FOR THIS RECORD!"
  54.         Else
  55.            mystr = "Comments:" & "'" & xlApp.Clean(Trim(M.qBW![Comments]))
  56.         End If
  57.  
  58.         Do
  59.             Pos = InStr(Pos + 1, mystr, ":")
  60.             If Not Pos = 0 Then
  61.                If Mid(mystr, Pos - 5, 1) = "/" Then
  62.                    mystr = Left(mystr, Pos - 11) & Chr(10) & Mid(mystr, Pos - 10, 10) & Chr(10) & Mid(mystr, Pos + 1)
  63.                    Pos = Pos + 2
  64.                End If
  65.             End If
  66.         Loop While Not Pos = 0
  67.  
  68.  
  69.         xlWksht.Cells(ii + 1, 1).Value = "Comments:"
  70.         xlWksht.Cells(ii + 1, 2).Value = Mid(mystr, 11)
  71.  
  72.         With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14))
  73.             .HorizontalAlignment = xlLeft
  74.             .VerticalAlignment = xlTop
  75.             .WrapText = True
  76.             .Orientation = 0
  77.             .MergeCells = True
  78.             .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size
  79.         End With
  80.  
  81.         xlWksht.Columns("A:A").ColumnWidth = 9.15
  82.  
  83.         'Draw Underline after each record:
  84.         '---------------------------------
  85.         TStr = "A" & CStr(ii + 1) & ":N" & CStr(ii + 1)
  86.         xlWksht.Range(TStr).Select
  87.         If Not IsEmpty(Selection.Range("A1")) Then 'check if first cell is empty
  88.            With xlWksht.Range(TStr).Borders(xlEdgeBottom)
  89.            .LineStyle = xlDouble
  90.            .Weight = xlThin
  91.            .ColorIndex = xlAutomatic
  92.            End With
  93.         End If
  94.         recordcnt = recordcnt + 1
  95.         M.qBW.MoveNext
  96.     Loop
  97.  
  98. xlWksht.PageSetup.LeftFooter = " Legend:" & Chr(10) & "See Estimated Actual Tot.Hrs. Column: Top Number = Estimated Hrs. and Bottom Number = Actual Hrs."     
  99.     'xlWksht.Range("A6").CopyFromRecordset M.qBW  'M.qBW
  100.  
  101.     strAnswer = MsgBox("Would you like to Print this spreadsheet?", vbYesNo, "Print Option")
  102.  
  103.     If strAnswer = vbYes Then
  104.        GoTo PrintSpreadsheet
  105.     Else
  106.        GoTo ViewSpreadsheet
  107.     End If
  108.  
  109. PrintSpreadsheet:
  110. xlWksht.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
  111. xlApp.ActiveWorkbook.Save
  112. xlApp.Visible = True
  113. xlApp.UserControl = True
  114. Selection.PrintOut
  115.  
Thanks.
GiftX
Sep 28 '07 #1
0 1135

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Mark | last post: by
1 post views Thread by Vlad | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.