Output to excel 1

[Walshie1987]

Hi, i've searched the forum and can't seem to find what i am looking for (sorry if i have misssed it).

I have a form which obviously stores info into a table. All I want to do is insert a button, when i click the button a box comes up asking me where i would like to save the Excel spreadsheet and what to call it (Just the standard box you would get when saving most).

When i select the location and the name, click save and then the excel spreadsheet will be created.

I think i may have to use the "docmd.outputTo" but not too sure.

Cheers
Chris

 

[Walshie1987]

Sorry forgot to put that the spreadsheet will be created using all the data in the table.

 

[scottian]

I think you need to look for 'docmd.transferspreadsheet'

"My God! It's full of stars...

 

[Walshie1987]

lol that would make sense

Cheers

 

[Walshie1987]

Can anyone tell me how i would write the VBA code to export all of my data in "tblUsers" to a file called "User Details"

Cheers

 

[Remou]

Did you look at docmd.transferspreadsheet?

 

[Walshie1987]

I had being putting signs in the wrong place but i now have the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblUsers", "Users Table"

but this just exports the tblUsers straight to and excel file in "My Documents", not ask me where to save the file to.

Is there anything i can add to the appove code to do this?

 

[SuicidED]

Hi
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblUsers", "In this bit put the pat name of where you want to save it i.e. C:\temp\name of fife.xls

 

[Walshie1987]

Thanks for that! One more thing, i'll probably not use it but while i using this bit of code i might aswel learn the lot!

How do you specify the range you want to export, say i wanted to export the last three columns of my table to an excel spreadsheet?

Cheers

 

[SuicidED]

You would eport using a query rather than the table. I.e.
make a query to select the 3 columns from your table and change the name form "tblUsers" to the name of your query.

 

[Walshie1987]

Thanks alot