Access Recordset to New Excel Sheet "Method * of object '_Global' failed"

[tdoolittle]

I have got to be missing something simple here. The following code I have generates an Excel sheet for me in an annoying format required to upload through some shipping software we use:

Private Sub btn_Shipment_Upload_Click()
'Generate Excel Workbook
Dim Excel_App As Excel.Application
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Excel_App.Workbooks.Add

'Input required information
With Excel_App
.Worksheets(1).Cells(1, 1) = "PlanName"
.Worksheets(1).Cells(1, 2) = Me.Generate_Name
.Worksheets(1).Cells(2, 1) = "ShipToCountry"
.Worksheets(1).Cells(2, 1) = Me.Country
'ect... until row 13


'Run Make Query to Generate Item Recordset
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qry_Pending_Item_Upload"
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl_Item_Upload")

'Start Pasting at Row 13
lngCount = 13


With rst
    Do Until .EOF
        Worksheets(1).Cells(lngCount, 1) = rst![Item]
        Worksheets(1).Cells(lngCount, 2) = rst![Quantity]
        .MoveNext
        lngCount = lngCount + 1
    Loop

rst.Close

Set rst = Nothing

End With
End With
End Sub

So all of this works totally fine... at least totally fine every other time you hit this button. The first time you select the button, the Excel worksheet is brought up and everything is fanflipintastic! Close the worksheet, select your next source and hit the button again, the worksheet generates with all of the manual info up until row 13 then fails with "Method 'Worksheets' of object '_Global' failed". Close the worksheet, and hit the button again, everything is totally fine. So every other time the button is selected it fails.

It's probably the lame code I put together, and something (hopefully) simple I'm missing. Any help would be appreciated!

[ssanfu]

You are not closing Excel, then you are trying to open Excel again. Won't happen!! An instance of Excel is still running. Check the taskmanager/ processes tab.
After the first upload, Excel should close (shutdown). I would bet it does not....

You should check out Ken Snell's page about Access/Excel automation for examples of how to open and close Excel from Access.
It saved my sanity: http://www.accessmvp.com/kdsnell/default.htm


Edit:
I had a problem with Excel not properly quitting, so I added a "DoEvents" command after the Excel Quit command. This allows Excel to actually quit before the rest of the code in the procedure executes.

[NoellaG]

Hi,

I approach it from the other side: a lot of people have Excel open in the background, so I start with the assumption that it is open. If not, I open it in the error handling code:

Public Sub CreateNewExcel(strType As String)
On Error GoTo Err_CreateNewExcel
   
 Dim appExcel As Excel.Application
    Dim bksBooks As Excel.Workbooks
    Dim wkbBook As Excel.Workbook
    Dim wksSheet As Excel.Worksheet
    Dim rngCatCol As Excel.Range
    Dim rngStart As Excel.Range
    Dim intC As Integer
    Dim strSaveName As String
    Dim strFileName As String
    
    Dim strTemplateExists As String
    
    Set appExcel = GetObject(, "Excel.Application")
    strTemplateExists = Nz(Dir(Me.FileLoctemp), "")
    If strTemplateExists = "" Then
        intError = cerrNoFiles
        Err.Raise 64000
    End If
    Set bksBooks = appExcel.Workbooks
    Set wkbBook = bksBooks.Add(Me.FileLoctemp)
    Set wksSheet = wkbBook.Sheets(1)
    wksSheet.Activate

    ...

    strSaveName = Me.FileLocNewFile & strType & strFileName & "_" & Format(Now, "YYYYMMDDhhmmss") & ".xls"
    wkbBook.SaveAs strSaveName, xlWorkbookNormal
    appExcel.Visible = True

Exit_CreateNewExcel:
    Exit Sub
    
Err_CreateNewExcel:
    If Err.Number = 429 Then 'excel is not running
        Set appExcel = CreateObject("excel.application")
        Resume Next
    Else
        Call gsgErrorHandling
    End If
    Resume Exit_CreateNewExcel
End Sub

[ssanfu]

Don't really see anything wrong with the code, except cleaning up the objects you created.


Look at the code on this page : http://www.accessmvp.com/kdsnell/EXCEL_Export.htm under the heading

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)


There is a boolean variable to hold the open status of Excel.
Then there is a check to determine if Excel should be started or if it is running already.

other code.....


Before exiting, there is a check of the boolean variable to determine if Excel should be left running of shut down.

I found I had to add a "DoEvents" command when cleaning up to allow access enough time to complete the actions, otherwise Excel would stay running in the background and I couldn't run the routine again.

[tdoolittle]

Appreciate all of the replies on this. Literally have had so many projects put on me that trying to implement any of these is going to be a while... seems every time we figure out something new in Access 1,000,000 doors open up for everyone, with me to try to make them all!

I'm going to mark this as solved as I can't believe there isn't something here to solve my problem for me!