How to export data from a query to a .xlsx file.

[Nitin Negi]

Hi ,
I tried to export data from a query to a .xlsx file using the
transferspreadsheet but when i open the spreadsheet , it gives me a error.
The command i used is
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 ,
"1_cQrsCoreRecsCommentLdn", pgblRoute & "Data\Current.xlsx", True, "".

Although if i use the below command

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 ,
"1_cQrsCoreRecsCommentLdn", pgblRoute & "Data\Current.xlsx", True, "".
It create the xlsx file but the sheet name is preceeded with _ in this
case the sheet name created is "_1_cQrsCoreRecsCommentLdn".

Can some one help.
Nitin

 

[nathan_savidge]

I generally use.

Dim XL as object
dim strSQL as string
dim rst as recordset
dim con as connection

set xl = createobject("Excel.Application")
xl.visible = true
xl.workbooks.add

strSQL = "Select * from [MY QUERY]" 'or even the SQL from your query.
set rst = new recordset

set con = application.currentproject.connection
rst.open strSQL, con,1

xl.range("a1").copyfromrecordset rst

rst.close

set rst = nothing
set con = nothing

 

[Klatuu]

That is a lot of overhead for just dumping a query to a spreadsheet. And,
there are issues with your code. You should reference a worksheet with the
range rather than the workbook. Also, you don't save or close the work book
and you don't Quit the application or set the object references to Nothing.

You stand a very good chance of leaving an instance of Excel running that
you don't know about. That can cause problems if the user then tries to open
excel. It will hang and never open a file.

--
Dave Hargis, Microsoft Access MVP

I generally use.

Dim XL as object
dim strSQL as string
dim rst as recordset
dim con as connection

set xl = createobject("Excel.Application")
xl.visible = true
xl.workbooks.add

strSQL = "Select * from [MY QUERY]" 'or even the SQL from your query.
set rst = new recordset

set con = application.currentproject.connection
rst.open strSQL, con,1

xl.range("a1").copyfromrecordset rst

rst.close

set rst = nothing
set con = nothing

Hi ,
I tried to export data from a query to a .xlsx file using the
transferspreadsheet but when i open the spreadsheet , it gives me a error.
The command i used is
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 ,
"1_cQrsCoreRecsCommentLdn", pgblRoute & "Data\Current.xlsx", True, "".

Although if i use the below command

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 ,
"1_cQrsCoreRecsCommentLdn", pgblRoute & "Data\Current.xlsx", True, "".
It create the xlsx file but the sheet name is preceeded with _ in this
case the sheet name created is "_1_cQrsCoreRecsCommentLdn".

Can some one help.
Nitin

 

[Klatuu]

I don't see any difference between the two lines.