Create an Excel 2007 workbook from Access 2007 db via VBA

[ksahara]

I hope someone will be there to help me. I have a table with 1000 records. I want to generate an Excel 2007 workbook(.xlsx) every time when user hit a button on an Access form. I managed to do it by using following code but it allows me to create only a .xls file. I tried changing the file extension and “Excel 8.0” to “Excel 12.0” in the SQL statement but it gives me the runtime error 3734. Deeply appreciate if someone could kindly tell me how to create a .xlsx file.

Note: Below code is working without selecting DAO as mentioned in the code. When I try to select Microsoft DAO 3.6 Object Library from the reference list then I get a message saying "Name conflicts with existing module, project, or object library". I have no knowledge in the area to understand the issue. I have attached an screen shot to show you the references this db is using at the moment.

Private Sub cmdExcelOutput_Click()
'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED
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:\MyProject\Output\Result.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\MyProject\Output\MyDatabase.accdb"
strTable = "tblMasterData"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
End Sub

 

[boblarson]

I hope someone will be there to help me. I have a table with 1000 records. I want to generate an Excel 2007 workbook(.xlsx) every time when user hit a button on an Access form. I managed to do it by using following code but it allows me to create only a .xls file. I tried changing the file extension and “Excel 8.0” to “Excel 12.0” in the SQL statement but it gives me the runtime error 3734. Deeply appreciate if someone could kindly tell me how to create a .xlsx file.

I have code on my website which could help you out. Go to the website in my signature and look for the CODE SNIPPETS. I have code you can put into a standard module and then call it. It should do the default file type of xlsx if you have 2007.

When I try to select Microsoft DAO 3.6 Object Library from the reference list then I get a message saying "Name conflicts with existing module, project, or object library".

That is because 2007 has a slightly different DAO reference. If you look you will see a reference named Microsoft Office 12.0 Access Database Engine Object Library. That is the reference which is already selected and so you can't put the eariler reference in.

 

[sullyscsu]

Probably an easier way of doing it is to put your data in a temporary table then use the TransferSpreadsheet method using the acExport argument then finally clearing your temporary table.

 

[boblarson]

Probably an easier way of doing it is to put your data in a temporary table then use the TransferSpreadsheet method using the acExport argument then finally clearing your temporary table.

Why? Why a temp table when a query and the code I provided will take care of it all? Temp tables are good sometimes but many times they aren't needed (and this is one of them it would appear from my reading of it).

 

[sullyscsu]

Why? Why a temp table when a query and the code I provided will take care of it all? Temp tables are good sometimes but many times they aren't needed (and this is one of them it would appear from my reading of it).

The only reason why I suggested a temporary table is because I saw he had a SELECT INTO statement that I assume put the data in the Excel spreadsheet, so if that was the way he wanted to go he can accomplish it that way. Otherwise, yes, a query is probably the best way to go.

 

[boblarson]

The only reason why I suggested a temporary table is because I saw he had a SELECT INTO statement that I assume put the data in the Excel spreadsheet, so if that was the way he wanted to go he can accomplish it that way. Otherwise, yes, a query is probably the best way to go.

As they are putting a TABLE into Excel, this code should work great for that.

 

[maniac]

Team:

I played with the same original example & had issue too.

The TransferSpreadsheet method worked great for one line of code tied to event. It was just what I needed.

I needed this for xlsx version:
msdn.microsoft.com/en-us/library/bb225982(v=office.12).aspx

For argument's sake, the query would be beneficial if one didn't want to export the entire table.