Creating an xls in Access 2010 VBA not working

Hey Everyone,  

I have an Access 2010 program that at one point allows the user to create an excel export file from the data (well originally I had the program creating a csv file).  Someone is helping me update this program so it works cleaner and in conjunction with the cloud so they changed the code slightly.  Now I get an error box that says "The format in which you are attempting to output the current object is not available"

Here is the original code that worked:

  DoCmd.TransferText acExportDelim, "", "Export Plan Selection and Deduction To Send Sorted", "\\HOST33\ShareFile\CLIENTS\3CTRPS\Export for " & UCase(txtClientID.Value) & " Date-" & Replace(CStr(Date), "/", "-") & " Time-" & Replace(CStr(Time), ":", "-") & ".csv", True, ""

Here is the code that he created:

DoCmd.OutputTo acOutputReport, stdocname, acFormatXLSX, strFilename

(the variables produce the same name for the file as the first one does).

I tried changing it to read acFormatXLS also and it still did not work.

Any help is really appreciated!
alevin16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Do you have MS Excel installed on the machine you run the code? The code will only work if you have Excel installed.
0
Gustav BrockCIOCommented:
> Here is the code that he created:

Then why not address "he" your issue?

Anyway, run this:

    DoCmd.OutputTo acOutputReport, stdocname

and you'll be listed/prompted for the possible output formats.

/gustav
0
Rgonzo1971Commented:
Hi,

Maybe try to change the suffix of your file to xls

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Helen FeddemaCommented:
For the TransferSpreadsheet method, when creating an .xls workbook, use the acSpreadsheetTypeExcel9 named constant for the spreadsheettype argument:

   strQuery = "qryDataForExport"
   strDBPath = Application.CurrentProject.Path
   strFileName = "Exported Data.xls"
   strFilePath = strDBPath & "\" & strFileName
   Debug.Print "File with path: " & strFilePath
   DoCmd.TransferSpreadsheet transfertype:=acExport, _
      spreadsheettype:=acSpreadsheetTypeExcel9, _
      tablename:=strQuery, _
      FileName:=strFilePath, _
      hasfieldnames:=True

Open in new window


For Office 2010, use the acSpreadsheetTypeExcel12Xml argument and the .xlsx extension for the filename

DoCmd.TransferSpreadsheet transfertype:=acExport, _
   spreadsheettype:=acSpreadsheetTypeExcel12Xml , _
   tablename:="qryCustomers", _
   FileName:=strWorkbook, _
   hasfieldnames:=True

Open in new window

0
alevin16Author Commented:
Thanks to all, I tried the xls and it seemed to work for now but I think I will try the last suggestion also.
0
Helen FeddemaCommented:
It just depends on what type of workbook you want to create -- both should work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.