Switching EXCEL worksheets from ACCESS VBA

  • Thread starter Thread starter michael.f.shorkey
  • Start date Start date
M

michael.f.shorkey

I have no trouble opening EXCEL, and saving excel files
and inserting values from a ADO recordset to cells.
My ONLY problem is when using an existing spreadsheet with
mulitple sheets and I want to re-direct (activate) a
SPECIFIC sheet with a variable that I have as a recordset
variable. Here's what I have:
dim xlapp as excel.application, myXL as object
SET XLapp = CreateObject("Excel.Application")
set myXL = xlapp.open("filename.xls")
myxl.worksheets("Sheet1").activate

....insert working data here; NOW

myxl.worksheets("& rs!nextWS &").activate

This is always where the error occurs. Any ideas? I can
type in the value and this works fine, but I really need
to pass worksheet reference programmatically.
 
We'd be much more able to help you if you told us what the error was.

Where have you defined the recordset rs? Assuming it's defined properly, is
what rs!nextWS is returning valid? Assuming it is, try:

myxl.worksheets(rs!nextWS).activate
 
The "" surrounding Sheet1 are there to tell the compiler
that it is a string, not a variable. You do not need them
when you are referencing the recordset column.
Try
myxl.worksheets(rs!nextWS).activate

Hope This Helps
Gerald Stanley MCSD
 
Back
Top