Need help with unique Excel file name output

[choli0090]

Hello everyone,

I am trying to figure out what I am missing to give the excel output the name
based upon the criteria at the time during the loop. This is what I have.

qryGroup - The list of Id's that are used for the criteria for the main query.
I have ftnRepID as the criteria under the RepId field (one of the fields part
of the query)

qryMain - The Main query

tblData - Table containing the data.

I also have 2 modules:

modFunctions
Function ftnRepID
On Error Resume Next
ftnRepId = vRepId
End Function

The other module..

Sub RepOutput

set db = Current Db
set recset = db.openRecordSet ("qryMain")
DoCmd.SetWarnings False
recset.MoveFirst
Do Until recset.EOF
vRepId = recset!RepCode
DoCmd.transferSpreadsheet acExport, acSpreadSheetTypeExcel9, "C:\Folder" &
vRepId & ".xls"
recset.MoveNext
Loop
Recset.close
DoCmd.SetWarnings = True
Debug.print "Done"

However when I run this, I get a run time 2522 error on the
transferSpreadsheet part. Overall I am trying to have the file name be the
repId that its processing at the time.

Any help would much be appreciated..

Thanks

 

[OssieMac]

without testing you should have a back slash at the end of the path.
"C:\Folder" Should be "C:\Folder\" & vRepId & ".xls"

 

[choli0090 via AccessMonster.com]

I added the \ after folder & it gave me the same message. Do I need to have
it append into a temp table, export based upon that table, delete & start the
process over again?

Thanks

 

[Stefan Hoffmann]

hi,

DoCmd.transferSpreadsheet acExport, acSpreadSheetTypeExcel9, "C:\Folder" &
vRepId & ".xls"

You need to specify a table or query to export...


mfG
--> stefan <--

 

[choli0090 via AccessMonster.com]

Hi Stefan,

I changed the line to the following, which now is producing the files by the
separate id's but they have no data in them.

DoCmd.transferSpreadsheet acExport, acSpreadSheetTypeExcel9, "qryMain","C:\
Folder\" &
vRepId & ".xls"

When I change it to open the query to display & use a small amount of records,
with a couple of ids, I can display the records with no problems.. Do I
have my transferSpreadsheet on the wrong line, or am i missing a line of code?


Thanks

 

[OssieMac]

Hi Chris,

Now that I have had a close look at your code I realize that the code you
have is to transfer an entire table or query not individual records from the
query. I think that you will need code to create an Excel workbook and copy
the required fields from the query for the specific records to the excel
workbook.

To do the above, in the Access VBA editor, youi will need to add the Excel
library. To do this:-
Select Tools -> References -> and scroll down until you find Microsoft Excel
n.0 Object Library and check the box (Ensure you check the box don't just
select the line)
Where n.0 stands for the version of xl.
9.0 = xl2000
10.0 = xl2002
11.0 = xl2003
12.0 = xl2007

CreateObject Function in Access VBA Help should get you started.

 

[choli0090 via AccessMonster.com]

Ok I figured out what was wrong. I had to remove the Dim line, which solved
the problem.

However I did have another question, is there a quick way of running another
"Loop" query to export to the same spreadsheet, but to another sheet?

Thanks

 

[Ken Snell \(MVP\)]

Perhaps this article will be helpful:

Create a query and export multiple "filtered" versions of the query (based
on data in another table) to separate worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile