Excel - Problems exporting to Excel - selecting correct sheet

Asked By M Skabialka

02-Feb-10 03:14 PM

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 =
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

02-Feb-10 03:41 PM

Answering my own question - this works but I do not know why:

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

Mich

02-Feb-10 03:44 PM

I do not speak the access, but maybe:

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

There is 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).


--

Dave Peterson

02-Feb-10 11:03 PM

Yes, I concur with Dave's analysis of the situation.
--

Ken Snell
http://www.accessmvp.com/KDSnell/