Move to Next Sheet in Workbook

[Mike C]

Hi. I think this will be a layup for someone out there. I have an Access
database that is looping through records and placing the data in a
pre-formatted excel template. The excel sheets are January, February, March
etc.

'Start looping through DateMonth table. This is a table with two columns;
MonthID, Month. The code sets the rs to MonthID 1 for January, loops through
the data and places the data in the Worksheet labeled "January", then loops
through, sets the MonthID to 2 for February, and then looks for the sheet
labeled "February". This is where I get stuck. I'll point it out below...

Set rsDateMonth = DB.OpenRecordset("SELECT * " & _
"FROM [DateMonth] ORDER BY MonthID ASC")

If Not rsDateMonth.EOF Then
Do While Not rsDateMonth.EOF

'I get an error that says Subscript Out of Range due to the code below. I
somehow need to write the part that says Sheets(" & '" & rsDateMonth![Month]
& "' & ") so that it knows it's looking for the February sheet next. Any
thoughts? THANKS!!!

Set SheetTEVendor =
GetObject("M:\Endosforce\TE\TEDetailRM.xls").Sheets(" & '" &
rsDateMonth![Month] & "' & ")

 

[John Nurick]

Hi Mike,

I think the problem is just with the apostrophes. Try:

GetObject("M:\blahblah.xls").Sheets(rsDateMonth.Fields("Month").Value)

But I'm worried about this approach creating a new instance of Excel for
each call to GetObject. Why don't you just use one:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook

Set xlBook = xlApp.Open("M:\blahblha.xls")

...
Set SheetTEVendor = xlBook.Worksheets(rsDateMonth![Month])
...

xlBook.Save
Do While xlApp.Workbooks.Count > 0
xlApp.Workbooks(1).Close False
Loop
xlApp.Quit

Hi. I think this will be a layup for someone out there. I have an Access
database that is looping through records and placing the data in a
pre-formatted excel template. The excel sheets are January, February, March
etc.

'Start looping through DateMonth table. This is a table with two columns;
MonthID, Month. The code sets the rs to MonthID 1 for January, loops through
the data and places the data in the Worksheet labeled "January", then loops
through, sets the MonthID to 2 for February, and then looks for the sheet
labeled "February". This is where I get stuck. I'll point it out below...

Set rsDateMonth = DB.OpenRecordset("SELECT * " & _
"FROM [DateMonth] ORDER BY MonthID ASC")

If Not rsDateMonth.EOF Then
Do While Not rsDateMonth.EOF

'I get an error that says Subscript Out of Range due to the code below. I
somehow need to write the part that says Sheets(" & '" & rsDateMonth![Month]
& "' & ") so that it knows it's looking for the February sheet next. Any
thoughts? THANKS!!!

Set SheetTEVendor =
GetObject("M:\Endosforce\TE\TEDetailRM.xls").Sheets(" & '" &
rsDateMonth![Month] & "' & ")

 

[MJC]

John, THANK YOU!! I see what you mean by opening several instances. It took a
few tries but I think I have it opening one instance now. Thanks again for
taking the time.
-Mike

Hi Mike,

I think the problem is just with the apostrophes. Try:

GetObject("M:\blahblah.xls").Sheets(rsDateMonth.Fields("Month").Value)

But I'm worried about this approach creating a new instance of Excel for
each call to GetObject. Why don't you just use one:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook

Set xlBook = xlApp.Open("M:\blahblha.xls")

...
Set SheetTEVendor = xlBook.Worksheets(rsDateMonth![Month])
...

xlBook.Save
Do While xlApp.Workbooks.Count > 0
xlApp.Workbooks(1).Close False
Loop
xlApp.Quit

Hi. I think this will be a layup for someone out there. I have an Access
database that is looping through records and placing the data in a
pre-formatted excel template. The excel sheets are January, February, March
etc.

'Start looping through DateMonth table. This is a table with two columns;
MonthID, Month. The code sets the rs to MonthID 1 for January, loops through
the data and places the data in the Worksheet labeled "January", then loops
through, sets the MonthID to 2 for February, and then looks for the sheet
labeled "February". This is where I get stuck. I'll point it out below...

Set rsDateMonth = DB.OpenRecordset("SELECT * " & _
"FROM [DateMonth] ORDER BY MonthID ASC")

If Not rsDateMonth.EOF Then
Do While Not rsDateMonth.EOF

'I get an error that says Subscript Out of Range due to the code below. I
somehow need to write the part that says Sheets(" & '" & rsDateMonth![Month]
& "' & ") so that it knows it's looking for the February sheet next. Any
thoughts? THANKS!!!

Set SheetTEVendor =
GetObject("M:\Endosforce\TE\TEDetailRM.xls").Sheets(" & '" &
rsDateMonth![Month] & "' & ")