Solved: Combining VBA Code to an array

[jo15765]

I am currently using the below code that I just repeat over and over just replacing the wb open file name. I have tried several ways to write an array 1) to shorten code and 2) when I need to add/remove a workbook that is being opened it is easier, but have been unsuccessful. Below is the code that I am using, can someone combine it to an array for me....


Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\Richard\Daily\Friday\Friday_Reports.xls"
With ActiveWorkbook
.SaveAs Filename:="C:\Master_Reports\Friday\" & ActiveWorkbook.Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
Run "PrintToPDF_MultiSheetToOne_Early"
.Close& ".xls"
End With
Set wb = Workbooks.Open(Filename:="C:\Mark\Daily\Friday\Friday_Reports.xls")
Run "RefreshOnOpen"
With ActiveWorkbook
.SaveAs Filename:="C:\Master_Reports\Friday\" & ActiveWorkbook.Name & VBA.Format(Date, "mmddyyyy") & ".xls"
Run "PrintToPDF_MultiSheetToOne_Early"
.Close& ".xls"
End With

[Aflatoon]

Do you need to run the 'RefreshOnOpen' sub for all of them or only certain ones? If the latter, it gets a little more complicated. If the former, then:

Dim varBooks
Dim varBook
Dim wb As Excel.Workbook

' adjust array as necessary
varBooks = Array("Richard", "Mark", "Bob", "Nathan")

' process each workbook in the array
For Each varBook In varBooks
Set wb = Workbooks.Open(Filename:="C:\" & varBook & "\Daily\Friday\Friday_Reports.xls")
Run "RefreshOnOpen"
With wb
.SaveAs Filename:="C:\Master_Reports\Friday\" & .Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
Run "PrintToPDF_MultiSheetToOne_Early"
.Close False
End With
Next varBook

[jo15765]

How does the code know what to do when there are no more "Next varBook"

Or in other terms when there are no more worksheets to process?


Also...would the same code work for opening multiple worksheets in the same workbook? Or would this slight variation not work?

varBooks = Array("Sheet1", "Sheet2", "Sheet3")
Set wb = Workbooks.Open(Filename:="C:\Test\Test.xls")
.SaveAs Filename:="C:\Master_Reports\ & Worksheet.Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"

[Aflatoon]

The code loops through each item in the array and then stops.

You do not open worksheets, so I am not sure what you are trying to do with that code, but there is no loop there.

[jo15765]

What I was trying to accomplish with the code above, is to open 1 workbook, but copy the worksheets listed in the array. Maybe the code is wrong, or maybe it isn't possible. I was just 1st posting it here to see if the code would actually work, and then if positive response, would test it in my database.

[jo15765]

This is the code I have it down to now...and it doesn'tlike my Set ws...line it tells me..."Type mismatch" It compiles fine, but when I go to run it, thats what it tells me...


Dim varBooks
Dim varBook
Dim ws As Excel.Worksheet

varSheets = Array("Michael", "Tommy", "Jakob")

For Each varSheet in varSheets
Set ws = Workbook.Open(Filename:="C:\Testing\Testing.xls" & varSheets)
With ws
.SaveAs...
End WIth
Next varSheet
End Sub

Any idea's on this one??

[mdmackillop]

Set workbook and worksheet objects separately
Dim varBooks
Dim varBook
Dim ws As Excel.Worksheet
Dim wb As Excel.Worksbook


varSheets = Array("Michael", "Tommy", "Jakob")

Set wb = Workbooks.Open(Filename:="C:\Testing\Testing.xls")

For Each varSheet In varSheets
Set ws = wb.Sheets(varSheet)
With ws
.SaveAs...
End With
Next varSheet
End Sub

[jo15765]

Now I recieve an object variable or with block variable debug error on:

.SaveAs

[mdmackillop]

Oops!
You need to save the workbook, not the sheet
With wb
.SaveAs...
End With

[jo15765]

Run-Time error:
Object Required

Still on this line of code:


.SaveAs Filename:="C:\Master_Reports\ & Worksheet.Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"

[Aflatoon]

What is Worksheet meant to be? (you have not declared it)

It would be simpler if you were to tell us in English what you are trying to achieve rather than us try to guess what your code is meant to do.

[mdmackillop]

Missing "

.SaveAs Filename:="C:\Master_Reports\" & ws.Name & "_" & Format(Date,
"mmddyyyy") & ".xls"

BTW, for this sort of error, use Debug.Print to see if your argument makes sense as in
Debug.Print "C:\Master_Reports\" & ws.Name & "_" & Format(Date, "mmddyyyy") & ".xls"

[jo15765]

Aflatoon --

I said in post #5 I wanted to use an array to open one workbook, but copy different worksheets and save them.

mdmackillop ---

Adding the missing " now throws a debug error of:
Object variable or With block variable not set

Still highlighting my SaveAs line...

[mdmackillop]

You have the benefit of seeing your code. See Post 11 which I took account of in the code I posted.

[jo15765]

My apologies, I had thought I posted my full code earlier, but it looks like I just kept on using snippets...Here is the code I am working with:


Dim varBooks
Dim varBook
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook

varSheets = Array("Michael", "Tommy", "Jakob")

For Each varSheet In varSheets
Set wb = Workbooks.Open(Filename:="C:\Test\Test.xls")
With ws
.SaveAs Filename:="C:\Master_Reports\" & ws.Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
End With
Next varSheet
End Sub

[Aflatoon]

Dim varBooks
Dim varBook
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook

varSheets = Array("Michael", "Tommy", "Jakob")

For Each varSheet In varSheets
Set wb = Workbooks.Open(Filename:="C:\Test\Test.xls")
With wb.sheets(varSheet)
.Copy
activeworkbook.SaveAs Filename:="C:\Master_Reports\" & .Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
activeworkbook.close False
End With
Next varSheet
End Sub

[jo15765]

That code accomplished it. Again, I apologize for not posting full code, I thought I had. And thank the both of you tremendously for your assistance!