troubleshooting Question

Access VBA Error if Excel = Nothing

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBA
2 Comments2 Solutions15 ViewsLast Modified:
Hi

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


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.Cells.EntireColumn.AutoFit
  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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros