running Excel-Code from Access

[Fritz Schmude]

I have strange errors occurring when I want to run VBA-code

inside Excel when the Excel-file is an application-object in Access.


Access-Code is like:

> Dim objExcel As Excel.Application

> Dim wrkBook As Excel.Workbook

> Dim strDokument As String



> strDokument = "d:\...\test.xls"

> Set objExcel = CreateObject("Excel.Application")

> objExcel.Visible = True

> Set wrkBook = objExcel.Workbooks.Open(strDokument)


In Excel, I have code in workbook-open as follows:


>sub workbook_open

>MsgBox "trying to select table 3..."

>Worksheets("Tabelle3").Select


Now, the message-box appears, but the selection of the table

is not done. Most other commands do not work either, some of them even producing errors.



How can I make my Excel-Code (some thousand lines) run in such a situation?


Any response is strongly welcome, thank you so much.


Fritz Schmude

[richardj]

If I understand you correctly, you have some VBA code which used to work in Excel but now doesn't work when you paste it into Access?

If you really do have thousands of lines of code, I might be able to help, but you won't like it!

Try fully-referencing your code, for example:
"Application.Workbooks(1).Select"
instead of
"Workbooks(1).Select"

Also make sure you have a reference to the Excel object library appropriate to the version of Excel you're using (in module design view, from the 'Tools' menu select 'References').

Forgive me if I've misunderstood; hope this helps though.
When you were using the code in Excel, it didn't matter that the references to objects such as Workbook objects weren't fully qualified (for example, the Workbook object is actually a child of the Application object) - so it was okay to write "Workbooks(1).Select" or whatever. In any other place, however, it DOES matter - you must write:"Application.Workbooks(1).Select".