troubleshooting Question

Access Rename workbook when exporting to Excel

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVBA
7 Comments1 Solution36 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros