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