TransferSpreadsheet construction

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone. Using A02 on XP.

I have some code provided by an old post but need help with the
TransferSpreadsheet line. Here is my code so far:

Function ExportFileForEachGP()

Dim strSQL As String
Dim GRPID As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst
Do Until rs.EOF
GRPID = rs("GRPID")
strSQL = "Select * from tVtgAssetAcctBals WHERE GRPID = " & GRPID
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tVtgAssetAcctBals, "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

I get Run time error 3495: The action or method requires a Table Name argument

This line is highlighted:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tVtgAssetAcctBals, "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"

I have the table name in there and it seems to be in the right place...I was
pretty much guessing on the argument items after each comma.

If you can assist me with this final line, I think I can get this baby
running. It is going to save hundred of hours every quarter!!!

Thanks in advance for any assistance you may be able to provide!

Bonnie
 
Hi Bonnie

Put your table name in quotation marks as in.
"tVtgAssetAcctBals"

Regards

Kevin
 
Hi Bonnie A,
you must put the name of the table between quotation marks (if is not a
variable) so your statement must look like as follow

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"

HTH Paolo
 
Bonnie -

The name of your table should be in double-quotes, as Access is expecting a
string.
 
Hi Jim! Thanks very much for your reply. I no longer get emailed when a
response is posted and I've not had time to check in. You are correct, I am
looping. I have a query qListGPs that uses the aggregate grouping to show
only a list of contract numbers (no duplicates, just one record per
contract). There may be 50 records for 2259 and 850 records for 3487, etc.
The field [GRPID] is a number field that will only contain numbers from 1
through 9999. I need to export a file for each GRPID. Do I need another
query? Doesn't my code say to transfer all records

strSQL = "Select * from ""tVtgAssetAcctBals"" WHERE GRPID = " & GRPID

right before my transfer spreadsheet line

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tVtgAssetAcctBals", "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"

As you know, I'm sure, I am not a programmer but love Access and saw a post
about exporting multiple files. A coworker has to take a file with over
200,000 records and parse it by hand into 872 separate Excel files so it can
be 'loaded' to a system rather than keyed in by hand. This would save tons
of hours each quarter.

I really appreciate your assistance and I will try to log back in and check
to see if you have replied again. Thanks!
--
Bonnie W. Anderson
Cincinnati, OH


JimBurke via AccessMonster.com said:
Bonnie,

One thing I noticed is that you have the command in a loop and are assigning
a group ID in the loop. Are you trying to send a separate spreadsheet for
each group ID? If so then you don't just want the table name in the command,
you want a query that is selecting the records for that group ID. I don't
know if TransferSpreadsheet will accept an SQL statement. If it does, then
what you want in the command is strSQL rather than the table name. If if
doesn't then you need to define a query that uses a parameter and then use
that query with the appropriate assignment for the parameter in the loop.

Bonnie said:
Hi everyone. Using A02 on XP.

I have some code provided by an old post but need help with the
TransferSpreadsheet line. Here is my code so far:

Function ExportFileForEachGP()

Dim strSQL As String
Dim GRPID As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("qListGPs")
rs.MoveFirst
Do Until rs.EOF
GRPID = rs("GRPID")
strSQL = "Select * from tVtgAssetAcctBals WHERE GRPID = " & GRPID
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tVtgAssetAcctBals, "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"
rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

I get Run time error 3495: The action or method requires a Table Name argument

This line is highlighted:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
tVtgAssetAcctBals, "S:\RPS\PTS\VtgAssetAcctBalExtract\" & [GRPID] & "_" &
Format(Date, "MM-DD-YY") & ".XLS"

I have the table name in there and it seems to be in the right place...I was
pretty much guessing on the argument items after each comma.

If you can assist me with this final line, I think I can get this baby
running. It is going to save hundred of hours every quarter!!!

Thanks in advance for any assistance you may be able to provide!

Bonnie
Click to expand...

--
Jim Burke

Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top