Transfer sheet method on files with date extension

Hello everyone. I would like to transfer an Excel spreadsheet into an Access table using the TransferSheet Method. The caveat is that the name of the file needs to involve a variable (a date) based on an entry from a TextBox.

The name of the table is tblMTMReportData. It already has column headings. The name of the Excel spreadsheets is as follows: mtm_report_asof_yyyymmdd.xls. Now the date extension "yyyymmdd" varies.

Now I have created a userform and on that userform is a TextBox and a Command Button. The textbox is called txtDate. The command button is called CommandReturnMTMPortfolio.

What I would like to happen is for the user to enter a date say 20110419. After that the user presses the button and the macro(or code) retrieves the file based on the date extension. The path to the files and folders are contained in another table.

I am getting an error on the variable I call Dim asofDATE As String, it says that it is undefined? Any suggestions. If there is a neater cleaner way to get this done let me know.

The code I have so far is below:

Please Login or Register  to view this content.

Try changing this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alan

Thanks for the reply. That is not working either. Note that txtDate is being highlighted in yellowas Undefined.

I'm guessing here, but maybe have to identify the form that the text box is located on, ie. Forms!YourFormName.txtDate.Text

It is possible that Access does not know where to go to find txtDate Control and needs some help.

Alan