Populating Excel from Access

[fdfjc]

Hi, I have a problem that has been addressed here before, but I can't seem to make the answer work. I am populating an Excel spreadsheet from MS Access with the following code. It runs correctly the first time, then the second time, it gives the rt error 1004, Method 'Range' of object '_Global' failed. I know I need to fully qualify the range, but I'm having a hard time accomplishing that. Here is my code. Can anybody give me some advice? Thanks for any help you can give.

Dim appExcel As Excel.Application

Dim wbook As Excel.Workbook

Dim wsheet As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")

Set wbook = appExcel.Workbooks.Open("C:\Brenda\Daily1.xls", UpdateLinks:=1, Password:="password")

Set wsheet = wbook.Sheets("Sheet1")

appExcel.Visible = True
appExcel.UserControl = True



With ActiveSheet.QueryTables.Add(Connection:= _
        "OLEDB;Provider=MSDAORA.1;User ID=odsl48_readonly;Password=readmeprod; Data Source=ODSL48P1", _
        Destination:=Range("A1"))
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select /*+ first_rows */        (CASE            WHEN meter_num in ('28187001', '28187002', 'BRPIN01055', 'BRPIN01065') THEN 'PINNACLE'            WHEN meter_num in ('28112001', '28112002', 'BRETCMTR7513', 'BRETCMTR7514') THEN 'FR" _
        , _
        "ANKLIN'

[Stewart Ross]

Two instances of the same problem: lines 18 and 20 do not refer explicitly to the Excel application object. This will work in Excel itself, where it is implied that the code is running within the Excel application object, but it fails when run with Excel as an automation server from another Office application. Failures of this nature often occur in exactly the way you describe - not on the first pass, but on subsequent ones - making debugging very frustrating as the first time round it seemed to work.

Curing the problem is straightforward ; you must fully-qualify all references to Activesheet, Activeworkbook, Range etc with the name of your automation server object, like this:

With appExcel.Activesheet.Querytables....
   .
   Destination := appExcel.Activesheet.Range("A1")

You can use the worksheet or workbook objects you have defined in place of the application object if these are the same sheets or workbooks you want to work with, but whatever you do you must not use unqualified references to ranges (through the Range or Cells objects), the Activesheet, a Selection object, the Activeworkbook, and so on.

-Stewart

[fdfjc]

Thanks Stewart, it works!