Link to an excel spreadsheet

[ragtopcaddy via AccessMonster.com]

Every week I get a spreadsheet deposited in a directory. It has a "Name" +
"mmddyy.xls" format. I have a function that returns the name of the latest
file in the directory. I have queries in my db on a table, "xlData" that is
linked to the latest spreadsheet. I would like to further automate the
process that returns the latest filename so that I can create a link to the
spreadsheet, whose name changes weekly but predictably, so that all queries
based on "xlData" will return data from the latest spreadsheet.
How do I use VBA to link to the sheet? I don't want to transferspreadsheet or
create a dao.recordset object. I just want to link to the spreadsheet so I
can query it and append and update records.

Thanks,

Bill R.

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com

 

[Guest]

If you want to link to it as a table, you do use TransferSpreadsheet, but you
use the transfer type of acLink.

DoCmd.TransferSpreadsheet, acLink, , "xlData", strFileName, True

 

[ragtopcaddy via AccessMonster.com]

Damn! That was easy! Boy is my face red.

Barada Nikto

If you want to link to it as a table, you do use TransferSpreadsheet, but you
use the transfer type of acLink.

DoCmd.TransferSpreadsheet, acLink, , "xlData", strFileName, True

Every week I get a spreadsheet deposited in a directory. It has a "Name" +
"mmddyy.xls" format. I have a function that returns the name of the latest

[quoted text clipped - 10 lines]

Bill R.

 

[ragtopcaddy via AccessMonster.com]

Do I have to delete the previous link or anything? The Access table name
always remains the same, but the Excel workbook name changes weekly.

If you want to link to it as a table, you do use TransferSpreadsheet, but you
use the transfer type of acLink.

DoCmd.TransferSpreadsheet, acLink, , "xlData", strFileName, True

Every week I get a spreadsheet deposited in a directory. It has a "Name" +
"mmddyy.xls" format. I have a function that returns the name of the latest

[quoted text clipped - 10 lines]

Bill R.

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com

 

[Guest]

Glad I could help. To help others, please rate the post so others with a
similar question will see it when they search.

Damn! That was easy! Boy is my face red.

Barada Nikto

If you want to link to it as a table, you do use TransferSpreadsheet, but you
use the transfer type of acLink.

DoCmd.TransferSpreadsheet, acLink, , "xlData", strFileName, True

Every week I get a spreadsheet deposited in a directory. It has a "Name" +
"mmddyy.xls" format. I have a function that returns the name of the latest

[quoted text clipped - 10 lines]

Bill R.

 

[ragtopcaddy via AccessMonster.com]

I am getting runtime error '3274': "External table is not in the expected
format."

Glad I could help. To help others, please rate the post so others with a
similar question will see it when they search.

Damn! That was easy! Boy is my face red.

[quoted text clipped - 10 lines]

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com

 

[ragtopcaddy via AccessMonster.com]

It turns out I had left out an argument and put the acLink argument in the
wrong spot. Here's the correct format:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "xlFile",
strLinkXL, True

Thanks again,

Bill R

Glad I could help. To help others, please rate the post so others with a
similar question will see it when they search.

Damn! That was easy! Boy is my face red.

[quoted text clipped - 10 lines]

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com

 

[Guest]

Leave the table name the same.
When you have completed processing the linked table, you do want to delete
it. It does not delete the spreadsheet file, it only removes the link to it:

DoCmd.DeleteObject acTable, xlData

Do I have to delete the previous link or anything? The Access table name
always remains the same, but the Excel workbook name changes weekly.

If you want to link to it as a table, you do use TransferSpreadsheet, but you
use the transfer type of acLink.

DoCmd.TransferSpreadsheet, acLink, , "xlData", strFileName, True

Every week I get a spreadsheet deposited in a directory. It has a "Name" +
"mmddyy.xls" format. I have a function that returns the name of the latest

[quoted text clipped - 10 lines]

Bill R.

--
Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via AccessMonster.com