TransferSpreadsheet --acSpreadsheetType question

[exRP12Nuke]

Good Afternoon,

I have a bit of code that exports a table in my database to an Excel Spreadsheet, but I cannot get it to save in .xlsx format, only in .xls format. What am I doing wrong? I assume that it is the Excel type that is the issue, but I cannot figure out which one I need to use.

Here is the bit of code.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "TblLastRI", _
        "G:\Data\EMS\Product Support\Contracts\Contract Reports\R&I Table Backup\ _
LastR&I Backup_" & Format(Date, "yyyymmdd") & ".xls", True

When I try to make the ".xls" part ".xlsx" the code does not run, I am at a loss.

Thanks!

 

[dhookom]

Try change the export type to some value like:
[tt]acSpreadsheetTypeExcel12 [/tt]

Duane
Hook'D on Access
MS Access MVP

 

[exRP12Nuke]

dhookom,

acSpreadSheetTypeExcel12 does not work either, looking at the help file the highest that will go is acSpreadSheetTypeExcel9 (Excel 2000 format). Even with that I cannot get the .xlsx

Any other ideas?

Thanks!

 

[PHV]

What version of msaccess are you playing with ?
Should be at least 2007 for .xslx export ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

looking at the help file the highest that will go is acSpreadSheetTypeExcel9 (Excel 2000 format). Even with that I cannot get the .xlsx

THEREFORE, with your version of MS Access, you cannot use .xlsx as that is a 2007+ feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

I have MS Office 2010.

 

[PHV]

MS ACCESS 2010 too ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[exRP12Nuke]

Yes, I have Microsoft Office Professional Plus which includes MS Access 2010.

 

[SkipVought]

and the version of Access that you have open is the 2010 version, as it is possible to have multiple versions of Access on your PC?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

Nope, I only have one version of Access, I wish that I could put a screen print in here, but I have not figured out how to do that.

Is there another command besides TransferSpreadsheet that I would be able to use?

Thanks!

 

[SkipVought]

I rarely export to a spreadsheet, although I regularly query MS Access databases directly from a worksheet, in order to return data to my workbook.

Once a Query Table is added to a worksheet, it can be REFRESHED either automatically or on demand in Excel to get new data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

I will try that approach, I have never queried out of Excel into Access before, but it never hurts to learn something new.

Overall, if it does not work, it is not that big of a deal, I just like to have everything as .xlsx, .docx, .pptx, etc...

Thanks again!

 

[SkipVought]

IMNSHO, a .doc* or .ppt* for extensive tabluar data is relatively useless, whereas an .xls* can be employed to slice 'n' dice the data to glean valuable views and summarizations where non-database types can manipulate the data in an extremely functional & popular tool.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[exRP12Nuke]

Oh I agree, I was just trying to make the point that I like having the most recent versions of office documents. I rarely, if ever, put Access data into a .docx or .pptx unless I am giving a static presentation or generating instructions on how to utilze some of the databases that we manage.

Thanks!

 

[Hoaokapohaku]

exRP12Nuke - I don't know if this can be done, but if I had your problem, I'd try to find a way to code so that you the data in a *.zip file format, then within the same code to change the file name extension to *.xlsx. Since the xlsx file format is actually a zip file, I have found it useful in some cases to change an xlsx file extension to zip and then using WinZip to open and manipulate the components of the file independently of the rest of the components, then change it back to xlsx when I'm done. This suggestion would just be doing that in reverse, using the zip format as a stepping stone to the current excel format.

Another idea I'm not sure if possible but might work - Export to *.txt, then from within the access procedure, fire up excel, have excel run a procedure that opens and properly parses the txt file into columns and rows and do anything else you want to do with the data, saves as xlsx, then deletes the txt file.

Others - Can either of the above be done?