How can I simplify this? VB 6.0

Printable View

May 11th, 2005, 12:54 PM
PeterGriffin

How can I simplify this? VB 6.0

I'm a real noob when it comes to programming but I know there has to be a more effcient way to write the program I did below.

Is there a way to use an array, or while statement or something?

Bisically, I'm opeing an excel documents and printing various worksheets in that document. Can I somehow loop thru the select and print statements instead of typing our each statement for all the sheets?

Code:

Sub Main() Dim xlfile As Excel.Application Set xlfile = New Excel.Application xlfile.Application.Visible = True '--------------------------------- '----------OPEN DOCUMENT---------- '--------------------------------- xlfile.Workbooks.Open "\\roi-primary\data\Engineering\San Antonio\Forms & Procedures\C9 Tooling Change Log.xls", UpdateLinks:=1, ReadOnly:=True, Notify:=False '--------------------------------- '---------------10A--------------- '--------------------------------- xlfile.Worksheets("Op10 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------20A--------------- '--------------------------------- xlfile.Worksheets("Op20 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------30A--------------- '--------------------------------- xlfile.Worksheets("Op30 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------40A--------------- '--------------------------------- xlfile.Worksheets("Op40 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------60A--------------- '--------------------------------- xlfile.Worksheets("Op60 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------70A--------------- '--------------------------------- xlfile.Worksheets("Op70 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------80A--------------- '--------------------------------- xlfile.Worksheets("Op70 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------90A--------------- '--------------------------------- xlfile.Worksheets("Op70 Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------CLOSE WORKBOOK---------- '--------------------------------- xlfile.Application.DisplayAlerts = False xlfile.Workbooks.Close '--------------------------------- '----------CLOSE EXCEL------------ '--------------------------------- xlfile.Application.Quit End Sub
May 11th, 2005, 12:59 PM
Cander

Re: How can I simplify this? VB 6.0

Should be able to compress it down with a For

Code:

For i = 10 To 90 Step 10 xlfile.Worksheets("Op" & i & " Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 Next i
May 11th, 2005, 01:04 PM
PeterGriffin

Re: How can I simplify this? VB 6.0

But I also have this:

Code:

Sub Main() Dim xlfile As Excel.Application Set xlfile = New Excel.Application xlfile.Application.Visible = True '--------------------------------- '----------OPEN DOCUMENT---------- '--------------------------------- xlfile.Workbooks.Open "\\roi-primary\data\Engineering\San Antonio\Forms & Procedures\C7 Tooling Change Log.xls", UpdateLinks:=1, ReadOnly:=True, Notify:=False '--------------------------------- '---------------10B--------------- '--------------------------------- xlfile.Worksheets("Op10B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------10C--------------- '--------------------------------- xlfile.Worksheets("Op10C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------20B--------------- '--------------------------------- xlfile.Worksheets("Op20B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------20C--------------- '--------------------------------- xlfile.Worksheets("Op20C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------30B--------------- '--------------------------------- xlfile.Worksheets("Op30B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------30C--------------- '--------------------------------- xlfile.Worksheets("Op30C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------40B--------------- '--------------------------------- xlfile.Worksheets("Op40B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------40C--------------- '--------------------------------- xlfile.Worksheets("Op40C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------60B--------------- '--------------------------------- xlfile.Worksheets("Op60B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------60C--------------- '--------------------------------- xlfile.Worksheets("Op60C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------70B--------------- '--------------------------------- xlfile.Worksheets("Op70B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------------70C--------------- '--------------------------------- xlfile.Worksheets("Op70C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 '--------------------------------- '---------CLOSE WORKBOOK---------- '--------------------------------- xlfile.Application.DisplayAlerts = False xlfile.Workbooks.Close '--------------------------------- '----------CLOSE EXCEL------------ '--------------------------------- xlfile.Application.Quit End Sub
May 11th, 2005, 01:12 PM
Cander

Re: How can I simplify this? VB 6.0

Code:

For i = 10 To 90 Step 10 xlfile.Worksheets("Op" & i & "A Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 xlfile.Worksheets("Op" & i & "B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 xlfile.Worksheets("Op" & i & "C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 Next i
May 11th, 2005, 01:15 PM
PeterGriffin

Re: How can I simplify this? VB 6.0

Ok, what if I have different worksheets that have nothing in common in their names, such as Big, Small, Little, Huge, etc.?

Is there a similar way to do this?

Re: How can I simplify this? VB 6.0

Quote:

Originally Posted by Cander

Code:

For i = 10 To 90 Step 10 xlfile.Worksheets("Op" & i & "A Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 xlfile.Worksheets("Op" & i & "B Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 xlfile.Worksheets("Op" & i & "C Tool Change Log").Select xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6 Next i

That will not work as not all of them have those A, B and C sheets, try this: (Untested)
VB Code:
Sub Main()
  Dim xlfile As Excel.Application
  Set xlfile = New Excel.Application
  xlfile.Application.Visible = True
  xlfile.Workbooks.Open "\\roi-primary\data\Engineering\San Antonio\Forms & Procedures\C7 Tooling Change Log.xls", UpdateLinks:=1, ReadOnly:=True, Notify:=False
  For i = 10 To 90 Step 10
    xlfile.Worksheets("Op" & i & " Tool Change Log").Select
    xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6
    If i = 10 Or i = 20 Or i = 30 Or i = 40 Or i = 60 Or i = 70 Then
      xlfile.Worksheets("Op" & i & "B Tool Change Log").Select
      xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6
      xlfile.Worksheets("Op" & i & "C Tool Change Log").Select
      xlfile.ActiveWindow.SelectedSheets.PrintOut Copies:=6
    End if
  Next i
  xlfile.Application.DisplayAlerts = False
  xlfile.Workbooks.Close
  xlfile.Application.Quit
End Sub
Cheers,

Ryanj