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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.