Open and formatting workbook (XP)

Topic

New Reply

Hi
I’m using TransferSpreadsheet (from vba in Access) to export 2 queries to a new workbook. This works fine.
I’m then trying to open the resultant workbook to apply some formatting.

This seems to work ok sometimes but on others I get ‘Run-time error 9 – Subscript out of range’ on the ‘Set sht’ line

Set appExcel = CreateObject(“Excel.Application”)
appExcel.Workbooks.Open (strWorkbookPath) ‘path and filename ‘this works
appExcel.Application.Visible = True

Workbooks(strWorkbookName).Activate

Set sht = ActiveWorkbook.Worksheets(2) ‘this sometime works sometimes fails

Any ideas appreciated – this is driving me nuts
There seems to be no pattern as to why it works or why it fails.

Reply | Quote

Replies

When using Automation, you must fully qualify all objects in the application you automate. Otherwise, you will create a hidden second instance of the application, and this will cause all kinds of problems.

Change

Workbooks(strWorkbookName).Activate

to

appExcel.Workbooks(strWorkbookName).Activate

and change

Set sht = ActiveWorkbook.Worksheets(2)

to

Set sht = appExcel.ActiveWorkbook.Worksheets(2)

Check the rest of your code too, to make sure that there are no unqualified references to Workbooks, ActiveWorkbook, ActiveSheet, ActiveCell etc.

Reply | Quote

Thanks, it seems to be behaving itself now.

I discovered as I ran it and missed out some changes, errors cropped up unpredictably at different points.

Why do the references need full qualification in this instance?

Reply | Quote

If you don’t tell VBA what (for example) Workbooks belongs to, it isn’t always smart enough to guess that you meant appExcel.Workbooks. Sometimes it will create a new instance of Excel as “parent” for the unqualified Workbooks object. Some things will still work, but as you found unpredictable errors will occur.

Reply | Quote

As usual, a huge thanks

Reply | Quote