Transfer table to excel and assign name

[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

 

[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

 

[tbmarlie]

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)

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- Hide quoted text -

- Show quoted text -

Thank you, Jeanette! That worked.