Export to excel

[nileshtx]

I there a way to exporl an results of an sql into excel spreadsheet and let the user pick the file name and location?

 

[jimbrooking]

Check out the TransferText Method in Access's Visual Basic online help.

 

[boblarson]

CORRECTION -

NOT TransferText
TransferSpreadsheet method to export to an Excel Spreadsheet format.

BL
hth

 

[nileshtx]

when i tried to use TransferSpreadsheet it only worked to transfer data to an existing spreadsheet, but able to create new spreadsheet? please tell me how to create new spreadsheet

 

[boblarson]

The TransferSpreadsheet method exports to a new file or will go into an existing file (if the file name you specify already exists). You can export more than one table or query to a single filename and there will be a worksheet created inside the spreadsheet for each differently named table or query you export to that particular file name. As to how to use the method, see my instructions below:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblMyTableOrQueryName", "C:\TheFileNameIWantAsAnExcelFile", True

Okay, let's break that down.

First up you tell it to use the TransferSpreadsheet Method by the Docmd.TransferSpreadsheet.

Next, what are we doing? Exporting, so it gets the acExport. Next, you pick the version of Excel you want it exported to. Excel9 is Excel 2000, Excel8 is Excel 97, etc.

Next up comes the table or query name that you want to export.

Then, pick the name that you want for your excel file (you can use variables to make up the name based on whatever you want too). Be sure to include the whole path.

The next part that says "TRUE" is used when you want the column headers to export. Use false if you don't want the column headers to show up when you export to Excel. For exports, it's best to ignore the RANGE part of the method.

Hopefully that will get you going.

BL
hth

 

[babs]

Another Related Question...

While exporting, how can we export in a certain format like Headings to be in Bold Format or put the Report Title on the first three lines of the Sheet ?

 

[boblarson]

For manipulation like that, I believe you would need to do some manipulation after export using code in Excel. However, it may be possible to do so using code in Access, but I've never tried anything that "intense" before.

BL

 

[sharinglife]

This won't work in Windows XP because of the file path. It will only work on the computer it was created on. Can someone help?

Thanks