A) A simple copy-paste using the Clipboard. I selected the entire table and tried the CRL + C shortcut. Unfortunately, the result was the following message:
B) I tried the export feature of Access, but, although the Excel file was created, no data was inserted into the spreadsheet.
Being disappointed by my previous attempts, I decided to try a VBA solution. So, I wrote a VBA function that can export a large Access table into an Excel spreadsheet.
Option Compare Database
Option Explicit
Sub Test()
  Â
   'Change the names according to your own needs.
   DataToExcel "Sample_Table", "Optional Workbook Path", "Optional Target Sheet Name"
              Â
   'Just showing that the operation finished.
   MsgBox "Data export finished successfully!", vbInformation, "Done"
  Â
End SubÂ
Â
Â
Function DataToExcel(strSourceName As String, Optional strWorkbookPath As String, Optional strTargetSheetName As String)
   'Use this function to export a large table/query from your database to a new Excel workbook.
   'You can also specify the name of the worksheet target.
  Â
   'strSourceName is the name of the table/query you want to export to Excel.
   'strWorkbookPath is the path of the workbook you want to export the data.
   'strTargetSheetName is the desired name of the target sheet.
  Â
   'By Christos Samaras
   'https://myengineeringworld.net/////
 Â
   Dim rst        As DAO.Recordset
   Dim excelApp   As Object
   Dim Wbk        As Object
   Dim sht        As Object
   Dim fldHeadings As DAO.Field
      Â
   'Set the desired recordset (table/query).
   Set rst = CurrentDb.OpenRecordset(strSourceName)
  Â
   'Create a new Excel instance.
   Set excelApp = CreateObject("Excel.Application")
  Â
   On Error Resume Next
  Â
   'Try to open the specified workbook. If there is no workbook specified
   '(or if it cannot be opened) create a new one and rename the target sheet.
   Set Wbk = excelApp.Workbooks.Open(strWorkbookPath)
   If Err.Number <> 0 Or Len(strWorkbookPath) = 0 Then
       Set Wbk = excelApp.Workbooks.Add
       Set sht = Wbk.Worksheets("Sheet1")
       If Len(strTargetSheetName) > 0 Then
           sht.Name = Left(strTargetSheetName, 34)
       End If
   End If
  Â
   'If the specified workbook has been opened correctly, then in order to avoid
   'problems with other sheets that might contain, a new sheet is added and is
   'being renamed according to the strTargetSheetName.
   Set sht = Wbk.Worksheets.Add
   If Len(strTargetSheetName) > 0 Then
       sht.Name = Left(strTargetSheetName, 34)
   End If
          Â
   On Error GoTo 0
  Â
   excelApp.Visible = True
                             Â
   On Error GoTo Errorhandler
   'Write the headings in the target sheet.
   For Each fldHeadings In rst.Fields
       excelApp.ActiveCell = fldHeadings.Name
       excelApp.ActiveCell.Offset(0, 1).Select
   Next
  Â
   'Copy the data in the target sheet.
   rst.MoveFirst
   sht.Range("A2").CopyFromRecordset rst
   sht.Range("1:1").Select
  Â
   'Format the headings of the target sheet.
   excelApp.Selection.Font.Bold = True
   With excelApp.Selection
       .HorizontalAlignment = -4108 '= xlCenter in Excel.
       .VerticalAlignment = -4108 '= xlCenter in Excel.
       .WrapText = False
       With .Font
           .Name = "Arial"
           .Size = 11
       End With
   End With
  Â
   'Adjusting the columns width.
   excelApp.ActiveSheet.Cells.EntireColumn.AutoFit
  Â
   'Freeze the first row - headings.
   With excelApp.ActiveWindow
       .FreezePanes = False
       .ScrollRow = 1
       .ScrollColumn = 1
   End With
   sht.Rows("2:2").Select
   excelApp.ActiveWindow.FreezePanes = True
  Â
   'Change the tab color of the target sheet.
   With sht
       .Tab.Color = RGB(255, 0, 0)
       .Range("A1").Select
   End With
   'Close the recordset.
   rst.Close
   Set rst = Nothing
Exit Function
Errorhandler:
   DoCmd.SetWarnings True
   MsgBox Err.Description, vbExclamation, Err.Number
   Exit Function
End Function
How to use it
I have added the above function into a VBA module named mExportToExcel. So, you can import the module and use the above function directly to your database. The video below demonstrates the whole procedure.
Download the mExportToExcel module from here
The file has been developed and tested in Access 2010, but, I suppose it can be used even with Access 2007.
A much simpler solution
The good thing with this blog is that is interactive, so I can learn from your comments, as you learn from my posts. Today a blog reader pointed out that you can avoid using the above VBA code by importing the data directly from Excel. In other words, instead of exporting the data from the Access, open your Excel workbook, go to the Data menu and select from Access. Find the Access file and select the desired table/query. That’s all! I tried it and seems to work perfectly. So, thank you my friend for your suggestion.
Hi, Gourav,
Switch the form to the design view and select the combo box.
On the Property Sheet panel that appears, select the On Change event.
There, click the ellipsis button (the three dots).
The VBA editor should be open with an event procedure.
Then, it is only a matter of one line of code.
The event code will look like this:
Note that MyCombobox is the name of the combo box, so you will have to change it.
Best Regards,
Christos
Chris, thank you for your kind words!
Best Regards,
Christos
Hi Christos, thanks for this amazing solution, it helped a lot.
However, I am not a VBA expert and need some help with customizing the code to pass dynamic parameters.
I have created a small form with Combo Box (name TableList) to select my query and a button (DownloadReport) to pass the query name to ExportToExcel function.
Please advise how can I pass these parameters directly into the function (File name as Report Name.xlsx). Also, I need a file picker to store the file in the location of my reference.
I may be late to this posting, but thank you very much for all of this. You folks saved my job!
I added your solution at the end of the post. Thank you very much for your suggestion!
VBA code not needed, a data connection to Access can be used:in Excel goto Data tabSelect "From Access" …