Hi. I am using the following Access VBA code to export data to Excel. What additional code would I use to rename the Excel workbook that is produced?
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")
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
Our community of experts have been thoroughly vetted for their expertise and industry experience.