Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Access VBA Error if Excel = Nothing


In the code below I got an error at the line following line. What should this be?
If Excel = Nothing Then

Open in new window

Public Function ExcelExportAndFormat(ByVal CRecordset As DAO.Recordset, ByVal CSheetName As String) As Object

  On Local Error Resume Next

  Dim Excel As Object ' Excel.Application
  Dim Workbook As Object ' Excel.Workbook
  Dim Worksheet As Object ' Excel.Worksheet
  Dim Count As Long
  Set ExcelExportAndFormat = Nothing
  Set Excel = CreateObject("Excel.Application")
If Excel = Nothing Then
    MsgBox "There seems to be a problem connecting to your Excel. Please check whether you have Excel on this computer."
    Exit Sub
End If
  Set Workbook = Excel.Workbooks.Add
  Set Worksheet = Workbook.Sheets(1)
  Worksheet.Name = CSheetName
  For Count = 0 To CRecordset.Fields.Count - 1
    Worksheet.Range("A1").Offset(, Count).Value = CStr(CRecordset.Fields(Count).Name)
  Next Count
  '//NOTE: The following line is highly important to stop green error messages
  Excel.Application.ErrorCheckingOptions.NumberAsText = False '// stop number store as text error
  Worksheet.Range("A2").CopyFromRecordset CRecordset
  Worksheet.Rows(1).Font.Bold = True
  Excel.ActiveWindow.DisplayGridlines = False
  Excel.Rows("1:1").RowHeight = 21.6

  Set ExcelExportAndFormat = Worksheet
  Excel.Visible = True
  Set Worksheet = Nothing
  Set Workbook = Nothing
  Set Excel = Nothing

End Function

Open in new window

Microsoft AccessVBA

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon