OutputTo Excel

[arthurman]

I'm a novice trying to get an access table exported to excel and have the excel file named based on the "Job Number" value in the Table "Job Info". I want this to happen by running a Macro that generates several reports, and at the same time exports the data. The reports part of the macro works fine, I just can't get the Output to excel to work. I've researched it on the forum and tried to write the coding based ideas in the forum. Here's what I've got, but it doesn't work. I get a compile error.

Option Compare Database

Dim stDocName As String
Dim stOrderNum As String
Dim stSubject As String

stDocName = "Filemaker Data - Excel" 'table to export
stOrderNum = [Tables].[Job Info].[Job Number] 'location of "job number"

stSubject = "FM" & stOrderNum & ".xls" 'named of exported file

DoCmd.OutputTo acReport,stDocName, acFormatxls, \\clo01\Drv F\FILEMAKER DATA\stSubject,True

Thanks for any help.

 

[Fizzio]

DoCmd.OutputTo acReport,stDocName, acFormatxls, "\\clo01\Drv F\FILEMAKER DATA\" & stSubject, True

 

[arthurman]

Fizzio -

Thanks, but when I try to run Debug - Compile Report Testing, I get a message "Compile error: Invalid Outside Proceedure" and the "Filemaker Data - Excel" part of the StDocName line highlights.

 

[Fizzio]

You have to run the code in some sort of procedure so that Access knows how to run it! I have included a stand alone version but I suspect that you want to run it on the press of a button.

Public Sub MergeToXL()

Dim stDocName As String
Dim stOrderNum As String
Dim stSubject As String

stDocName = "Filemaker Data - Excel" 'table to export
stOrderNum = [Tables].[Job Info].[Job Number] 'location of "job number"

stSubject = "FM" & stOrderNum & ".xls" 'named of exported file

DoCmd.OutputTo acReport,stDocName, acFormatxls, "\\clo01\Drv F\FILEMAKER DATA\" & stSubject, True

End Sub

On another point, Do not use spaces in table names as Access has a paddy when you do. A better name for your table would be tblFileMakerData_XL

Also this line
stOrderNum = [Tables].[Job Info].[Job Number] 'location of "job number"

Will not work as you
a) cannot directly reference the tables
b) have not told access which record you want to export!

 

[arthurman]

Fizzio -
Your comment that the line that looks for the "Job Number" in the table will not work kinda puzzles me. That is the crux of the who question, getting the "job Number" to be a part of the new filename. What needs to happen.
I appreciate your help.

 

[Fizzio]

Can you explain exactly what process you are trying to accomplish.

 

[arthurman]

I have created a database the links to tables from another database. I have created several queries and reports to get the data I need into the format I need. Now, I have to export to an excel file the data from one of the tables I created through a Make Table Query. I have a form that the user can select various reports, etc. from. I want one of the selection buttons on the form to execute a macro that produces a set of reports and at the same time institutes the output to the excel file. The data I want to export is in a table named "Filemaker Data - Excel". It is unique to a certain job with a unique "Job Number". This "Job Number" is a field in a Table called "Job Info", one of the tables I link to. The "Job Number" is also in the table to export. I want the excel file name to be "FM" Job Number .xls.

I'm a novice, as I said. I have everything else in the database working fine, except this. I have created the macro that produces the reports and will export the data through using the OutputTo action, but I cannot get the naming done "automatically". If I leave the "Output file" blank, it requests the filename to save to and the location. My intent is to have the file saved to a specific folder on the server using the desired filename, without the user inputing anything.

Thanks for your patience and help

 

[ghudson]

If the job number is not visble on the form [as a field value] then you could use a DLookup to find the job number to which you can add to your TransferSpreadsheet function.