Open and export data in an excel file using a macro with access

Those codes are to open and/or export a data created by Microsoft Access into an excel file, I am showing an exportation by putting a simple name but also by putting name that includes a date.

 

When I use the macro ?

Each time that I need to open and/or extract a data from Access in an excel file.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

To open multiple files in a different folder:

Function test()
Dim mypath As String
' put the full path of the folder
mypath = "\\path\folder\"
End Function

To open multiple files in a same folder:

Function test()
Dim mypath As String
mypath = CurrentProject.Path& "\"
End Function

To export in a different folder:

Function test()
Dim myexport As String
' change xxx by a name you want
myexport = "\\path\folder\xxx.xlsx"
' change yyy by the name of your table
DoCmd.TransferSpreadsheetacExport, acSpreadsheetTypeExcel12Xml, "yyy", myexport, True
End Function

To export in the same folder by adding a date:

Function test()
Dim myexport As String
' change xxx by a name you want
myexport = CurrentProject.Path& "\xxx_" & Format(Date, "YYYY-MM-DD") & ".xlsx"
' change yyy by the name of your table
DoCmd.TransferSpreadsheetacExport, acSpreadsheetTypeExcel12Xml, "yyy", myexport, True
End Function

To know what means “12Xml” and for other options, check this Microsoft help AcSpreadSheetType enumeration (Access).

Interesting Management