Results 1 to 5 of 5
  1. #1
    tdoolittle is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    18

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


    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:

    Code:
    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!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,939
    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.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    691
    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:

    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

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,939
    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.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    tdoolittle is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    18
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 08-07-2014, 10:28 AM
  2. Replies: 11
    Last Post: 06-30-2014, 11:34 PM
  3. Replies: 1
    Last Post: 07-13-2012, 07:58 PM
  4. Replies: 0
    Last Post: 01-11-2012, 12:34 PM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums