Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating an xls in Access 2010 VBA not working

Posted on 2014-11-30
6
Medium Priority
?
205 Views
Last Modified: 2014-12-01
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!
0
Comment
Question by:alevin16
6 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40473011
Do you have MS Excel installed on the machine you run the code? The code will only work if you have Excel installed.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40473359
> 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
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 800 total points
ID: 40473574
Hi,

Maybe try to change the suffix of your file to xls

Regards
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 1200 total points
ID: 40473785
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
 

Author Comment

by:alevin16
ID: 40473897
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40473945
It just depends on what type of workbook you want to create -- both should work.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question