Dec 6th, 2010, 12:11 AM
Export from Access 2007 to Excel 2007 using vba
I hope there is someone to help me, I want to generate an excel 2007 file (.xlsx) with all the data in a Access table when user hit a button. Below VBA code help me to do it by generating a .xls file but still couldn't figure out how to generate .xlsx file. Please help.
Private Sub cmdExcelOutput_Click()
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "C:\My Project\Output\Result.xlsx"
strWorksheet = "WorkSheet1"
strDB = "C:\My Project\Data Cleansing Tool.accdb"
strTable = "tblMaster"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
"SELECT * INTO [Excel 12.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
Set objDB = Nothing
Dec 7th, 2010, 03:29 PM
Re: Export from Access 2007 to Excel 2007 using vba
"with all the data in a Access table" when user hit a button.
use the DoCmd.TransferSpreadsheet, see sample below.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourTable", "YourFile.xlsx"
Last edited by nubie; Dec 9th, 2010 at 12:53 PM.
Click Here to Expand Forum to Full Width