Exporting Access Data into Excel Programatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all,

I want to export access data into excel in a MS Access form based on when
the user clicks the button some fields from a main form and subform should be
ported into excel sheet. Is this possible programatically ? If yes how do I
go about it ?

Thanks
Madhuri
 
Assuming that your report is using a query as its record source, you can use
DoCmd.TransferSpreadsheet to export that query to an EXCEL file. See
TransferSpreadsheet in Help file.
 
Hi Ken

I am trying to do thisin the MSaccess form coding

I added a button to run Excel application and the code for the button looks
like this

Private Sub Command93_Click()
On Error GoTo Err_Command93_Click
DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel9,
qKPWC_RECOUP, "C:\TRY", YES)

Exit_Command93_Click:
Exit Sub

Err_Command93_Click:
MsgBox Err.Description
Resume Exit_Command93_Click

End Sub

but the code dosent run and is giving me an error

1) It gives me a error that a = is included somewhere. Does the
transferdatasheet action return any value. If it does what datatype does it
return

I tried modifying and added a
Dim x as integer
and assigned
x = DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel9,
qKPWC_RECOUP, "C:\TRY", YES)
thinking that it might be returning a numeric value
but now while executing the code it gives me
Compile error : highlighted on Transferspreadsheet method
"Expected function or variable"

Please help

Madhuri
 
Don't put ( ) around the arguments. Also, specify the full path to the EXCEL
file, including the filename. And, use True not Yes.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qKPWC_RECOUP,
"C:\TRY\FileNameIs.xls", True
 
Thanks Ken, that worked

Please also tell me how to open the excel sheet after its created without
closing msaccess.

Thanks in advance
Madhuri
 
Thank you,

I solved it. I used shell command to open the excel sheet.

Thanks for all the support Ken.

Madhuri
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Madhuri said:
Thank you,

I solved it. I used shell command to open the excel sheet.

Thanks for all the support Ken.

Madhuri
Click to expand...
 
I am trying to do a similar thing but I have the error msg - The MS Jet
Database Engine could not find the object (table name).

What is qKPWC_RECOUP?

Thanks for your help!
 
Back
Top