Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

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 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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

What additional code would I use to rename the Excel workbook that is produced?

try refer to line:

Worksheet.Name = CSheetName 

Open in new window


Where CSheetName is the String variable that you need to passing in

Avatar of Murray Brown

ASKER

Thanks but I want to rename the workbook from Book1 to something else
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.

@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.
@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