Problems exporting to Excel - selecting correct sheet

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

I am using some code from Ken Snell to export data from Access to an Excel
spreadsheet which is set up already with formatting and formulas.

When I use this code:
Set xls = xlw.Worksheets("005")
the information goes to the correct spreadsheet.

But when I use this code

Set xls = xlw.Worksheets(rst!LineNumber) ' where rst!LineNumber =
"005"
Set xlc = xls.Range("a5") ' this is the first cell into which data go

For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).value
Next lngColumn

The info for spreadsheet "005" goes into sheet number "004", and all of the
info goes into the previously numbered sheet.
There are 100 sequentially numbered sheets.

I have stepped through the code and cannot tell why it is choosing the wrong
sheet - any ideas?

Mich
 
Answering my own question - this works but I don't know why:

Set xls = xlw.Worksheets(Format(Val(rst!LineNumber), "000"))

Mich
 
I don't speak the access, but maybe:

Set xls = xlw.Worksheets(format(rst!LineNumber, "000"))

There's a difference between:
xlw.worksheets("005")
and
xlw.worksheets(5)

The first is using the name of the worksheet. The second is using the 5th
worksheet (counting from the left hand side).
 
Back
Top