Transfer table to excel and assign name

  • Thread starter Thread starter tbmarlie
  • Start date Start date
T

tbmarlie

I'm trying to do 2 things:
1) Transfer a table (tbl A70 Final Remit Detail) to an excel file
2) Assign a name to the file using a field (FileName) from another
table (tbl A02 Report Year Temp tbl)

I'm using the code below (which I used from some other code that I
found in another application), but getting compile errors so I'm not
heading in right direction. I'm hoping someone can suggest some
better code to do this. Thanks.

Function RemitTransfer()

Dim FileName As String
MyReportTable As Recordset

Set MyReportTable = OpenRecordset("tbl A02 Report Year Temp tbl",
DB_OPEN_DYNASET)

Set FileName = MyReportTable.Fields("FileName")


'copies to excel file that contains remit detail
DoCmd.TransferSpreadsheet A_EXPORT, 5, "tbl A70 Final Remit Detail",
"y:\data\remit\Remit Spreasheets\" & FileName & ".xls"


End Function
 
Hi,
to get the FileName use DLookup
You don't need to open a recordset

Dim strFileName as String

strFileName = DLookup("[FileName]", "MyReportTable")

'copies to excel file that contains remit detail
DoCmd.TransferSpreadsheet acExport, 5, "tbl A70 Final Remit Detail",
"y:\data\remit\Remit Spreasheets\" & strFileName & ".xls"



Jeanette Cunningham
 
Hi,
to get the FileName use DLookup
You don't need to open a recordset

Dim strFileName as String

strFileName = DLookup("[FileName]", "MyReportTable")

 'copies to excel file that contains remit detail
 DoCmd.TransferSpreadsheet acExport, 5, "tbl A70 Final Remit Detail",
 "y:\data\remit\Remit Spreasheets\"  & strFileName & ".xls"

Jeanette Cunningham




I'm trying to do 2 things:
1) Transfer a table (tbl A70 Final Remit Detail) to an excel file
2) Assign a name to the file using a field (FileName) from another
table (tbl A02 Report Year Temp tbl)
Click to expand...
I'm using the code below (which I used from some other code that I
found in another application),  but getting compile errors so I'm not
heading in right direction.  I'm hoping someone can suggest some
better code to do this. Thanks.
Click to expand...
Function RemitTransfer()
Click to expand...
Dim FileName As String
MyReportTable As Recordset
Click to expand...
Set MyReportTable = OpenRecordset("tbl A02 Report Year Temp tbl",
DB_OPEN_DYNASET)
Click to expand...
Set FileName = MyReportTable.Fields("FileName")
Click to expand...
'copies to excel file that contains remit detail
DoCmd.TransferSpreadsheet A_EXPORT, 5, "tbl A70 Final Remit Detail",
"y:\data\remit\Remit Spreasheets\" & FileName & ".xls"
Click to expand...
End Function- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you, Jeanette! That worked.
 
Back
Top