Results 1 to 2 of 2

Automation Error with msAccess creating Excel Spreadsheet

Threaded View

  1. #1
    Guest
    Join Date
    01-02-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    30

    Automation Error with msAccess creating Excel Spreadsheet

    I have this code below to create an excel workbook, it spits out the queries and saves the file in the first procedure, then the next procedure opens the workbook for formatting.

    this all works fine on one pc but not another, they have identical office versions (2003), same n/w access, BUT the one that fails is running under Windows 8

    On the bad pc it opens 2 copies of excel, both with blank windows (no worksheet displayed), when the code finishes they are still both blank.
    the line of code that fails is shown in Italic, as no worksheet is visible it cannot select the worksheet.

    Can anybody see what may be wrong with the sub - format_sheets_now()



    Private Sub Command94_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim gg As String
    sdt = Format(start_date, "dd-mm-yy")
    edt = Format(End_date, "dd-mm-yy")
    gg = "C:\aaa\timesheets\Employee Time Report master.xls"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("create excel time sheets for selected employees on main menu")
    With rs
    .MoveFirst
    Do While Not .EOF
    fn = rs.Fields("First Name")
    Ln = rs.Fields("Last Name")
    bc = rs.Fields("Barcode")
    Me.barcode = bc
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln
    DoEvents
    .MoveNext
    Loop
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Call format_sheets_now
    T = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
    End Sub
    
    Sub format_sheets_now()
    
    Dim ws As excel.Worksheet
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strPath As String
    Dim stDocName As String
    'Early binding
    'Dim Excel_Application As excel.Application
    'late binding
    Dim Excel_Application As Object
    Dim Excel_Workbook As Workbook
    Dim Current_Worksheet As Worksheet
    Dim gg As String
    gg = "C:\aaa\timesheets\Employee Time Report master.xls"
    sdt = Format(start_date, "dd-mm-yy")
    edt = Format(End_date, "dd-mm-yy")
    Set Excel_Workbook = GetObject(gg)
    Set Excel_Application = Excel_Workbook.Parent
    Set Current_Worksheet = Excel_Workbook.Worksheets(1)
    Excel_Application.WindowState = xlMaximized
    Excel_Application.Visible = True
    Excel_Workbook.Windows(1).Visible = True
    Excel_Workbook.Worksheets(1).Select
    Excel_Application.DisplayAlerts = False
    Excel_Workbook.SaveAs filename:="C:\aaa\timesheets\Employee Time Report for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Excel_Application.DisplayAlerts = True
    Code continues with lots of formatting...
    Last edited by trevor69; 02-03-2015 at 08:30 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Facing 'run-time error -2147217871 (80040e31) Automation error' in excel VBA
    By junaidanjum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2013, 03:48 AM
  2. Automation Error: Invalid Oleverb Structure when Creating Chart
    By Sharanya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2013, 04:56 PM
  3. Run time error 91 (MSACCESS, EXCEL)
    By mobidv in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2012, 02:52 PM
  4. Automation error when creating graphs
    By tacojoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2008, 12:20 PM
  5. [SOLVED] When Running DDEInitiate, Error - Can't find MSAccess.exe?
    By bmupward in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2006, 10:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1