Opening Excel from Access

[Curry]

Can anybody assist me with this? The file outputs OK to the location I want however I keep getting an Script Out of range error when Access tries to open the file to run the rest.

DoCmd.OutputTo acOutputQuery, "qryExcel_Selected_List", acFormatXLS, "C:\Users\" & Environ("UserName") & "\Desktop\Selection_List.xls"

Dim xlApp As Object, xlWrkb As Object


Set xlApp = CreateObject("Excel.Application")
Set xlWrkb = xlApp.Workbooks.Open("C:\Users\" & Environ("UserName") & "\Desktop\Selection_List.xls")

xlApp.Sheets("qryExcel_Selected_List").Select
xlApp.Sheets("qryExcel_Selected_List").Name = "Selection_List"
xlApp.Sheets.Add After:=Sheets(Sheets.Selection_List)
xlApp.ActiveCell.FormulaR1C1 = "Selection_Summary"

 

[midmented]

Already open

Looks to me like in your first line of code, the workbook is already open.

The script out of range happens when it tries to open (while it is already open) again in your openworkbook code.
-----------------------------
To explain a little more:
I think the workbook is still open and inserting the query when it hits the open workbook line of code. You might try a time delay between the 2 lines of code to fix it.

 

[chergh]

It shouldn't be open, unless he has manually opened it. The poster didn't include the autostart parameter and if that is omitted it defaults to false which doesn't start the application.

 

[Curry]

Thanks for that however I have tried this without the OutputTo command using an existing excel file in the correct location. I get the same problem.

 

[midmented]

If the query is still running when it hits the open workbook, this should delay for 20 seconds to see if I am right. Paste this line in ABOVE your code reading
Dim xlApp As Object, xlWrkb As Object

Dim dblEndTime As Double
Dim TT As Double

TT = Timer
dblEndTime = Timer + 20#

'Here is where it waits for 20 seconds.
Do While dblEndTime > Timer
    DoEvents
Loop

 

[Curry]

Actually I tell a lie. The issue I get without the OutputTo command is "Object Required" and the code stops. If I Add a new Excel Workbook rather then trying to open the existing all works fine. My problem is with opening the existing File.

 

[midmented]

Private Sub Something()
 [COLOR=blue]Dim[/COLOR] oXL [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
    [COLOR=blue]Dim[/COLOR] oExcel [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
    [COLOR=blue]Dim[/COLOR] sFullPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
    [COLOR=blue]Dim[/COLOR] sPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
     
     
     [COLOR=darkgreen]'   Create a new Excel instance[/COLOR]
    [COLOR=blue]Set[/COLOR] oXL = CreateObject("Excel.Application") 
     
     
     [COLOR=darkgreen]'   Only XL 97 supports UserControl Property[/COLOR]
    [COLOR=blue]On Error Resume Next[/COLOR] 
    oXL.UserControl = [COLOR=blue]True[/COLOR] 
    [COLOR=blue]On Error Goto[/COLOR] 0 
     
     
     [COLOR=darkgreen]'   Full path of excel file to open[/COLOR]
    [COLOR=blue]On Error Goto[/COLOR] ErrHandle 
    sFullPath = CurrentProject.Path & "\TestFile.xls" 
     
     
     [COLOR=darkgreen]'   Open it[/COLOR]
    [COLOR=blue]With[/COLOR] oXL 
        .Visible = [COLOR=blue]True[/COLOR] 
        .Workbooks.Open (sFullPath) 
    [COLOR=blue]End With[/COLOR] 
     
     
ErrExit: 
    [COLOR=blue]Set[/COLOR] oXL = [COLOR=blue]Nothing[/COLOR] 
    Exit [COLOR=blue]Sub[/COLOR] 
     
ErrHandle: 
    oXL.Visible = [COLOR=blue]False[/COLOR] 
    MsgBox Err.Description 
    [COLOR=blue]Goto[/COLOR] ErrExit  

End Sub

 

[Curry]

If the query is still running when it hits the open workbook, this should delay for 20 seconds to see if I am right. Paste this line in ABOVE your code reading
Dim xlApp As Object, xlWrkb As Object

Dim dblEndTime As Double
Dim TT As Double

TT = Timer
dblEndTime = Timer + 20#

'Here is where it waits for 20 seconds.
Do While dblEndTime > Timer
    DoEvents
Loop

I put back the OutputTo command and tried the above code and I got the same Object Required Error.

 

[midmented]

Set xlWrkb = xlApp.Workbooks.Open("C:\Users\" & Environ("UserName") & "\Desktop\Selection_List.xls")

Take off the set xlWrkb = part and try your code.
Example:

xlApp.Workbooks.Open("C:\Users\" & Environ("UserName") & "\Desktop\Selection_List.xls")

 

[Curry]

Private Sub Something()
 [COLOR=blue]Dim[/COLOR] oXL [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
    [COLOR=blue]Dim[/COLOR] oExcel [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR] 
    [COLOR=blue]Dim[/COLOR] sFullPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
    [COLOR=blue]Dim[/COLOR] sPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] 
     
     
     [COLOR=darkgreen]'   Create a new Excel instance[/COLOR]
    [COLOR=blue]Set[/COLOR] oXL = CreateObject("Excel.Application") 
     
     
     [COLOR=darkgreen]'   Only XL 97 supports UserControl Property[/COLOR]
    [COLOR=blue]On Error Resume Next[/COLOR] 
    oXL.UserControl = [COLOR=blue]True[/COLOR] 
    [COLOR=blue]On Error Goto[/COLOR] 0 
     
     
     [COLOR=darkgreen]'   Full path of excel file to open[/COLOR]
    [COLOR=blue]On Error Goto[/COLOR] ErrHandle 
    sFullPath = CurrentProject.Path & "\TestFile.xls" 
     
     
     [COLOR=darkgreen]'   Open it[/COLOR]
    [COLOR=blue]With[/COLOR] oXL 
        .Visible = [COLOR=blue]True[/COLOR] 
        .Workbooks.Open (sFullPath) 
    [COLOR=blue]End With[/COLOR] 
     
     
ErrExit: 
    [COLOR=blue]Set[/COLOR] oXL = [COLOR=blue]Nothing[/COLOR] 
    Exit [COLOR=blue]Sub[/COLOR] 
     
ErrHandle: 
    oXL.Visible = [COLOR=blue]False[/COLOR] 
    MsgBox Err.Description 
    [COLOR=blue]Goto[/COLOR] ErrExit  

End Sub

This solved it....Thanks very much.

 

[midmented]

reputation?

Don't forget to tip my scale

Just trying to build a good reputation! Thanks!