asked on Access Rename workbook when exporting to Excel
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
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.Applic
Set Workbook = Excel.Workbooks.Add
Set Worksheet = Workbook.Sheets(1)
Worksheet.Name = CSheetName
For Count = 0 To CRecordset.Fields.Count - 1
Worksheet.Range("A1").Offs
Next Count
'//NOTE: The following line is highly important to stop green error messages
Excel.Application.ErrorChe
Worksheet.Range("A2").Copy
Worksheet.Cells.EntireColu
Worksheet.Rows(1).Font.Bol
Excel.ActiveWindow.Display
Excel.Rows("1:1").RowHeigh
Set ExcelExportAndFormat = Worksheet
Excel.Visible = True
Set Worksheet = Nothing
Set Workbook = Nothing
Set Excel = Nothing
End Function
Public Function ExcelExportAndFormat(ByVal
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.Applic
Set Workbook = Excel.Workbooks.Add
Set Worksheet = Workbook.Sheets(1)
Worksheet.Name = CSheetName
For Count = 0 To CRecordset.Fields.Count - 1
Worksheet.Range("A1").Offs
Next Count
'//NOTE: The following line is highly important to stop green error messages
Excel.Application.ErrorChe
Worksheet.Range("A2").Copy
Worksheet.Cells.EntireColu
Worksheet.Rows(1).Font.Bol
Excel.ActiveWindow.Display
Excel.Rows("1:1").RowHeigh
Set ExcelExportAndFormat = Worksheet
Excel.Visible = True
Set Worksheet = Nothing
Set Workbook = Nothing
Set Excel = Nothing
End Function
Microsoft AccessVBA
Last Comment
Anders Ebro (Microsoft MVP)
ASKER
Thanks but I want to rename the workbook from Book1 to something else
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
@Anders:
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.
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.
Have you tried Workbook.SaveAs
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
@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
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