Importing a single Excel cell to multiple rows in Access table

[wzg]

Hello,

I need to create a database that would automatically import about 10K deals from an excel worksheet (and manipulate them somehow afterwards obviously). The thing is, it also has to add some information not given in the records.
To be more precise:
The table in Access will consist of columns like:

Order Date | Shipping Date | Price | Discount

which have their equivalent in the .xls file. However I need to add a new column to the Access table that will contain information on the date of import of the specific deal. The report I'm importing is generated each day and has that date shown in the B4 cell. What I need to do is add this date to all the records I just imported, so that the user would know when the deal was imported to the database.

To break it into seperate task, I have to:
1. Import columns A-N from row 11 till the end and paste it into a table in Access
2. Import the date shown in cell B4 and paste it to the same table, into an extra column for EACH record I just imported.

I know the first part may be done with a simple
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "table_name", _
"C:\PATH\FILE_NAME"
code, but I have no idea how to get through the second task.

The second question (not so crucial) is:
is it possible to automatically import that .xls report when its path is constantly changing? By that I mean that the path is "C:\PATH\0721\report.xls" for the report of 21/07/2008 and "C:\PATH\0722\report.xls" for 22/07/2008.

I would be extremely grateful for any help.

P.s. Please have mercy with the tech-language, I'm a total newbie

 

[Rabbie]

The second question (not so crucial) is:
is it possible to automatically import that .xls report when its path is constantly changing? By that I mean that the path is "C:\PATH\0721\report.xls" for the report of 21/07/2008 and "C:\PATH\0722\report.xls" for 22/07/2008.

I would be extremely grateful for any help.

P.s. Please have mercy with the tech-language, I'm a total newbie

The simple answer to this is that it is pretty easy to do in VBA. You just need to extract the month and day part of the date you are interested in and concatenate that into your path string.

 

[wzg]

Damn it, I knew I wouldn't understand the answer If you could explain it as if you were explaining it to your Grandma, I would be more than grateful

I'm not sure if I made myself clear: On the click of a command button, the user should import all the reports that were not already imported. Without typing anything each time.

And one more thing - my Access version is Access 2000 and VB is Visual Basic 6.0

Thanks for your help