Results 1 to 5 of 5

Error using Excel Export routine

  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    43

    Error using Excel Export routine

    Below is a Subroutine I am using to export my reports. Right now it is just exporting one as I am trying to figure out these error message.

    Which sometime appear and sometimes don't

    Error (1)
    Error 462 - Remote Server Machine Does Not Exist Or Is Unavailable

    Error (2)
    “Method 'worksheets' of object '_global' failed” error on every other run


    Sub TabReportsExport()


    Dim strSheetName As String

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object


    Dim fld As DAO.Field
    Dim strPath As String
    Dim sSql As String


    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler


    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.visible = True




    strSheetName = "Equipment Audit"
    Set rst = CurrentDb.OpenRecordset(SqlEquipmentChangeAudit)
    Worksheets.Add.Name = strSheetName

    Set xlWSh = xlWBk.Worksheets(strSheetName)


    xlWSh.Activate
    xlWSh.Range("A2").Select
    For Each fld In rst.Fields
    ApXL.ActiveCell = fld.Name
    ApXL.ActiveCell.Offset(0, 1).Select
    Next

    rst.MoveFirst

    xlWSh.Range("A5").CopyFromRecordset rst
    xlWSh.Range("1:1").Select

    ' This is included to show some of what you can do about formatting. You can comment out or delete
    ' any of this that you don't want to use in your own export.


    With xlWSh.Range(xlWSh.Cells(2, 1), xlWSh.Cells(2, rst.Fields.count))
    .Font.Bold = True
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 3 '1
    .HorizontalAlignment = xlCenter
    End With


    ' With ApXL.Selection.Font
    ' .Name = "Arial"
    ' .Size = 12
    ' .Strikethrough = False
    ' .Superscript = False
    ' .Subscript = False
    ' .OutlineFont = False
    ' .Shadow = False
    ' End With
    '
    ' ApXL.Selection.Font.Bold = True
    '
    With ApXL.Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    'xlWSh.Range("A3").Select

    rst.Close
    Set rst = Nothing


    Exit Sub
    err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.description, vbExclamation, Err.Number
    Exit Sub


    End Sub

  2. #2
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    43
    So I have tried various forms of this export. Uninstalled Excel and reinstall and no matter what I get the same error.



    Dim strSheetName As String
    Dim fld As DAO.Field
    Dim strPath As String
    Dim sSql As String
    Dim rst As DAO.Recordset

    Dim ApXL As Excel.Application 'As Object
    Dim xlWBk As Excel.Workbook 'As Object
    Dim xlWSh As Excel.Worksheet 'As Object


    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler


    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Add
    ApXL.visible = True


    For i = 0 To Me.tabReports.Pages.count - 1
    If i = 0 Then
    strSheetName = "Equipment Audit"
    Set rst = CurrentDb.OpenRecordset(SqlEquipmentChangeAudit)
    ' Worksheets.Add.Name = strSheetName
    'xlWBk.Worksheets("Sheet1").Name = strSheetName
    ' Set xlWSh = xlWBk.Worksheets(strSheetName)
    ElseIf i = 1 Then
    strSheetName = "Cable Audit"
    Set rst = CurrentDb.OpenRecordset(SQLCableAuditChange)
    ' Sheets.Add.Name = strSheetName
    ' Set xlWSh = xlWBk.Worksheets(strSheetName)
    ' xlWBk.Worksheets("Sheet2").Name = strSheetName
    ElseIf i = 2 Then
    strSheetName = "Change Audit"
    Set rst = CurrentDb.OpenRecordset(SQLAuditTrail)
    'xlWBk.Worksheets("Sheet3").Name = strSheetName
    ' Sheets.Add.Name = strSheetName
    ' Set xlWSh = xlWBk.Worksheets(strSheetName)
    End If


    'xlWBk.Worksheets("Sheet1").Name = strSheetName
    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Activate
    xlWSh.Range("A2").Select
    For Each fld In rst.Fields
    ApXL.ActiveCell = fld.Name
    ApXL.ActiveCell.Offset(0, 1).Select
    Next

    rst.MoveFirst

    xlWSh.Range("A5").CopyFromRecordset rst
    xlWSh.Range("1:1").Select

    ' This is included to show some of what you can do about formatting. You can comment out or delete
    ' any of this that you don't want to use in your own export.


    With xlWSh.Range(xlWSh.Cells(2, 1), xlWSh.Cells(2, rst.Fields.count))
    .Font.Bold = True
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 3 '1
    .HorizontalAlignment = xlCenter
    End With


    ' With ApXL.Selection.Font
    ' .Name = "Arial"
    ' .Size = 12
    ' .Strikethrough = False
    ' .Superscript = False
    ' .Subscript = False
    ' .OutlineFont = False
    ' .Shadow = False
    ' End With
    '
    ' ApXL.Selection.Font.Bold = True
    '
    With ApXL.Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    'xlWSh.Range("A3").Select

    Next i

    Set xlWSh = Nothing
    Set xlWBk = Nothing
    Set ApXL = Nothing

    rst.Close
    Set rst = Nothing
    Exit Sub
    err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.description, vbExclamation, Err.Number
    Exit Sub

  3. #3
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    43
    There seems to be no rhyme or reason as to when this sub works and when it fails. Sometimes I can run it 20 times and it works each time. Other times only once.

  4. #4
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    43
    Well after 5 days 40 hours of trying to figure out why these errors are showing randomly . I have come to the conclusion that it probably isn't possible for excel to handle exporting data to more than one page.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,319
    have you got a sample of your database you can hand out. There's no great way to help you without something to work with short of building an entirely new application. I have built exports that go to more than one page so I know it's possible I just am not willing to rebuild something to prove it

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

Similar Threads

  1. Export To Excel Error
    By RayMilhon in forum Programming
    Replies: 10
    Last Post: 03-14-2019, 12:06 PM
  2. Access Export Routine (into Excel file)
    By skydivetom in forum Programming
    Replies: 3
    Last Post: 02-06-2019, 09:06 PM
  3. Import VBA routine does not recognize the Excel File
    By jyellis in forum Import/Export Data
    Replies: 4
    Last Post: 09-08-2017, 01:20 PM
  4. Error when trying to export from table to Excel
    By tobinjames in forum Import/Export Data
    Replies: 3
    Last Post: 12-15-2011, 02:55 PM
  5. Replies: 3
    Last Post: 02-09-2011, 07:43 AM

Posting Permissions

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