Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Printing page1 in 39 wrkbks then page 2, etc...

  Asked By: Dukker    Date: Dec 08    Category: MS Office    Views: 722
  

I have to print 12 worksheets in 39 workbooks, but want to print them in
order i.e... all 39 books pages 1, then all 39 books pages 2

I am thinking I could open them all up and store them in a collection...
where my mind gets confused is how to iterate thru and do it...


Code:
Public Sub FinalSubmittedBVCW_Status()

Dim iBooks As Integer
Dim iSheets As Integer
Dim iSheetCntr As Integer
Dim iLoopBookCnt As Integer
Dim sBook As String
Dim sHotelCode As String
Dim cBook As Collection
Dim oBook As Excel.Workbook

'Iterate through folder to find the BVCW xls files from the GM's
sBook = VBA.Dir(conFinalFrcst_XLS_FOLDER & "*.XLS", vbNormal)

While VBA.Len(sBook) <> 0
'obook is now declared as an OBJECT that needs to be passed
Set oBook = Application.Workbooks.Open(conFinalFrcst_XLS_FOLDER
& sBook)

'need to get the sheet count
iSheets = oBook.Sheets.Count

'add to the collection
cBook.Add oBook

'Directory not null
sBook = VBA.Dir()
Wend

'I have to go thru each book the amount of times there is worksheet
For iBooks = 1 To cBook.Count * iSheets

'Get book 1 thru 39


For iSheetCntr = 1 To iSheets
'Get sheets 1 thru 12ish...

Next iSheetCntr

Next iBooks

'set books back to zero

'Close the first worksheet
oBook.Close SaveChanges:=False

'Clear Memory
Set cBook = Nothing

End Sub

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Debbie Reyes     Answered On: Dec 08

Do the workbooks  and worksheets  have numbers in front of their names?
That is, if you sort a list of them do they come out in the order you
want them printed?

 
Answer #2    Answered By: Leroy Schmidt     Answered On: Dec 08

Not sure... Someone else created it... There are 13 sheets  and each
sheet in all workbooks  are same name but unique within the workbook...
Summary, Consortia, etc...

How would I sort a list of them?

 
Answer #3    Answered By: Edwin Chavez     Answered On: Dec 08

Sorry for not replying earlier.

Are the workbooks  or sheets  within the workbooks called "Page 1",
"Page 2"... and so on?.. or are the sheets called "Summary",
"Consortia"..?

One of the easiest ways to sort them is to paste the list into a sheet
and sort them there.

 
Answer #4    Answered By: Burk Martin     Answered On: Dec 08

They are named... I looked in the VBE and they are referenced by Sheet1,
but not an order... I have sheet  12, sheet 9, sheet 7 and so on...

Obviously the easiest is to open  each workbook 12 times and print  the
first sheet I need, the 2nd, 3rd and so on...

I was thinking I could put them in a collection, have them all open and
then iterate thru the collection  one sheet at a time... It doesn't seem
logical that when I reference the workbook from the collection that the
program would now it's a spreadsheet... Plus I can't think of a good way
to programmatically say here is book  1 print the first page, now go to
book 2 and print the first page... Then go back  to book 1 and print page
2...

 
Answer #5    Answered By: Hubert Taylor     Answered On: Dec 08

It may not seem logical, but if that's what you want to do as per your
description, you may as well just go ahead and do just that.

It's not the names in the VBE ... sheet1, sheet2... etc that matter,
it's the "real" names of the sheets  you see on the tabs at the bottom
of excel.

Is it possible for you to open  all of the workbooks  at the same time?
The only limitation here is memory.

 
Answer #6    Answered By: Lurlene Fischer     Answered On: Dec 08

Yes... I can open  them all...

I'm thinking

Dim cBook as collection


Set cBook = new collection

'I put into the collection  after the sheets  have there data added and
after saved with there proper naming conventions
cBook.add oBook.worksheets 'would I book  just the obook or put each
sheet in the collection


'here's wher my mind goes to spaggetti
For I = 1 to cbook.count

'the workbook (I have 39 or 40)

'then the sheet1
'need sheet  count in here right... I know its 12

'next workbook

****once I have gone thru all 40... How do I get back  to book 1
and to the 2nd sheet...

Next i

 
Answer #7    Answered By: Helene Stewart     Answered On: Dec 08

Try the below code.

It first collects all of the sheet  names in the active workbook.
Then it loops around each workbook that's open  and looks for those
names.
If it finds one it runs subPrintSheet.

I've compiled but not really tested this code. If you find  it works or
not get back  anyway. If it don't work I'll look further.

 
Didn't find what you were looking for? Find more on Printing page1 in 39 wrkbks then page 2, etc... Or get search suggestion and latest updates.




Tagged:            

 
 
 
 

Related Post