Avatar of Murray Brown
Murray Brown
Flag 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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Anders Ebro (Microsoft MVP)

8/22/2022 - Mon
Ryan Chong

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

Murray Brown

ASKER
Thanks but I want to rename the workbook from Book1 to something else
ASKER CERTIFIED SOLUTION
ste5an

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anders Ebro (Microsoft MVP)

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.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ste5an

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

Anders Ebro (Microsoft MVP)

@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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.