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
As Stefan has already suggested, its just a matter of saving the workbook. I just wanted to add that I think its going to cause headaches if you use the excel class names as your object names (Excel, Workbook, Worksheet). I know you are using latebinding but still.
It is a general export function. I don't can come up wit some better names here (assumption: not using abbreviations, cause they are also not very developer friendly, no Hungary notiation).
What are better names, adding some value to them? CurrentApplication, CurrentWorkbook, CurrentWorksheet seems redundant to me.
@Ste5an I have just had cases where code that was originally latebinding is copied into a projekt that uses early binding (e.g. if you want to/need to use WithEvents for something). So my preference for names are usually: worksheet: ws workbook: wb Application: appExcel
try refer to line:
Open in new window
Where CSheetName is the String variable that you need to passing in