|
-
May 11th, 2005, 12:54 PM
#1
Thread Starter
New Member
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
#2
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
#3
Thread Starter
New Member
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
#4
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
#5
Thread Starter
New Member
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?
-
May 11th, 2005, 01:21 PM
#6
Re: How can I simplify this? VB 6.0
 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
