Export Data to excel

  • Thread starter Thread starter excel_hari
  • Start date Start date
E

excel_hari

Hi,

I have got the code for importing data to access. I want to know the
code for transfering data from a particular table in my DB to my C:
drive as an Excel file. How to do the same?

I have created a macro using Action OutputTo and then ObjectType as
Table, ObjectName as NotSpecialSKU and Output Format as MS Excel
97-2003 and Auto start as No. Incidentally I tried to call this macro
in code using Call macro1 but I got an error message. Is this not
possible in Access? Also, if it is possible, can we pass arguments to
these macro, like dynamically passing the name of the table and
Location of file to be saved etc?

Also, I would like to know as to how to do the above operation using
VBA code and not using any "recorded" Macro?

Regards,
HP
India
 
If you want to output a table or query to Excel, the best method is the
TransferSpreadsheet method. For details, you can use VBA Help to learn how
to use it. The transfer part is pretty easy. To allow the user to specify
the file name or table name takes a little more work.

Ususally you will do this with a form. On the form you will need text boxes
for the table name and file name and a command button to start the transfer.
For example purposes, we will call the form frmExport, the text box for the
table name txtTable, and the the text box for the file name txtFileName (not
too obtuse, I hope).

So, in the Click event of the command button, the code would look something
like this:

DoCmd.TransferSpreadsheet acExport, , Me.txtTable, Me.txtFileName, True

Now, this does not address any error handling or checking to see if the
table exists or if the file path is valid, but it is the basic concept.

If you want to get adventurous, you can have a look at this site. It allows
you to use the Windows Open File dialog to allow the user to select the file
rather than having to type it in.

http://www.mvps.org/access/api/api0001.htm
 
Klatuu,

Thanks for pointing to TransferSpreadsheet method being used for
exporting as well.
If you want to get adventurous, you can have a look at this site. It allows
you to use the Windows Open File dialog to allow the user to select the file
rather than having to type it in.

http://www.mvps.org/access/api/api0001.htm
Click to expand...

Im using the code in the above site for selecting a file which can be
exported to Access. I am not able to understand as to how I can use the
above code for IMPORTING an access table in to excel. Ideally, Wouldnt
I need a set of code which could specify the folder where I want to
save the access table. Then I may offer the user with an inputbox in
which s/he may specify the filename under which data may be saved.
Please note, presently I have zilched code from Chip Pearson Excel MVP
for specifying a folder, but I would like to know as to how the
"api0001.htm" link will allow me to specify path.

Please guide me.

Regards,
HP
India
 
It doesn't matter whether you are importing or exporting. The code in the
API presents the Windows Common Open/Save dialog box that allows the user to
navigate to a file or enter a file name. It does not do anything other than
return the selected path and file.
You can use the InitialDir argument to pass a default directory and the
FileName argument to pass a default file name to the dialog box. That is
where the the dialog box will first display. If the user changes the
selection, then whatever the user entered will be returned. It returns the
full path and file name. You use the returned value as the file name
argument for the TransferSpreadsheet.

Note that if the use clicks Cancel, a zero length string is returned.
 
Back
Top