Set range statement working in Excel, not working in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with an Excel spreadsheet from Access 2000.
Can get to excel and open the worksheet just fine, using
Set appExcel = New Excel.Application
...
appExcel.Workbooks.Open (szPath)
...
appExcel.Worksheets(szActivityID).Activate

The problem is that I want to work with a named range within the
spreadsheet. The statement:

Set myRange = appExcel.szActivityID.Range("XLProject")

doesn't throw an error, but it doesn't update myRange, either.
I've tried a couple of variations including
Set myRange = appExcel.Worksheet.Range("XLProject")
and they don't work either.

Thanks for any help

I've tried the same code in a macro in the Excel spreadsheet itself, and it
works fine.
 
Margi said:
I am working with an Excel spreadsheet from Access 2000.
Can get to excel and open the worksheet just fine, using
Set appExcel = New Excel.Application
...
appExcel.Workbooks.Open (szPath)
...
appExcel.Worksheets(szActivityID).Activate

The problem is that I want to work with a named range within the
spreadsheet. The statement:

Set myRange = appExcel.szActivityID.Range("XLProject")

doesn't throw an error, but it doesn't update myRange, either.
I've tried a couple of variations including
Set myRange = appExcel.Worksheet.Range("XLProject")
and they don't work either.

Thanks for any help

I've tried the same code in a macro in the Excel spreadsheet itself,
and it works fine.
Click to expand...

I'm more fan of using object variables for each of the objects I'm
working with.

dim wr as excel.workbook ' or object
dim sh as excel.worksheet ' or object

set wr = appExcel.Workbooks.Open(szPath)
set sh = wr.worksheets(szActivityID)
Set myRange = sh.Range("XLProject")

See if working with the objects a bit more explicit, helps.
 
Back
Top