alevin16
asked on
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\CLIENT S\3CTRPS\E xport 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!
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\CLIENT
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!
Do you have MS Excel installed on the machine you run the code? The code will only work if you have Excel installed.
> 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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all, I tried the xls and it seemed to work for now but I think I will try the last suggestion also.
It just depends on what type of workbook you want to create -- both should work.