Importing to the same table daily

[mrwistles]

Hi there, great forum you have here. Quick question in regards to auto importing.

I import from an excel file every day where the columns and headers stay the same. What I want to do is import into the same table but just had a date for when the item was added. e.g I have fields 1-8 and everyday I want the import to feed the same table, keep the existing information but have the date it was imported as another column in field 9.

How do I do this?

Thanks for your time.

[ajetrumpet]

I think it might be easier if you came up with a method of using the TODAY() function in excel in the last column and simply imported everything into access using code or the wizard in one fell swoop. That way, the date is already there and you can avoid any code that is necessary to do this with.

[mrwistles]

Thanks.

I am importing from about 1,000 different spreadsheets. So to use the today formula I would have to change multiple sheets going forward.

That is obviously a mammoth task and something which I would like to avoid.

[ajetrumpet]

That is obviously a mammoth task and something which I would like to avoid.

actually it's not that 'mammoth' for code to run on it but it might take a few minutes.

but maybe there's an easier way in access:

*add a date field to your table
*import your 1,000 ss data as appended data to the table
*when you get done with that, run an UPDATE QUERY on your table and replace all NULL values in the date field with the function DATE()

[weekend00]

Just add one DataTime field in you table, set it's default to now() if you don't mind the time. if you don't want the time, use int(now) as default.

every time you add new record, the default value will be insert to the field. you don't need any code for it.

[ajetrumpet]

Just add one DataTime field in you table, set it's default to now() if you don't mind the time. if you don't want the time, use int(now) as default.

every time you add new record, the default value will be insert to the field. you don't need any code for it.

That's so funny. Very good solution weekend. I absolutely love it!

[pkstormy]

Here's a nice import form I designed to import 1 or multiple xls files in a designated folder the user selects on the form (using the Browse button).

Once the browse button is clicked and the import folder is supplied, and then the import button is clicked, it can cycle through the entire folder importing all and any xls files.

Hope it helps.

[mrwistles]

Thanks all. Will check these out later. Much appreciated.

[weekend00]

That's so funny. Very good solution weekend. I absolutely love it!

Thanks Aje.

[pkstormy]

Just add one DataTime field in you table, set it's default to now() if you don't mind the time. if you don't want the time, use int(now) as default.

every time you add new record, the default value will be insert to the field. you don't need any code for it.

Or you can simply set the defaultvalue to =Date() and you won't get the time.

[pkstormy]

Here's a nice import form I designed to import 1 or multiple xls files in a designated folder the user selects on the form (using the Browse button).

Once the browse button is clicked and the import folder is supplied, and then the import button is clicked, it can cycle through the entire folder importing all and any xls files.

Hope it helps.

I forgot to mention, this also keeps a log history of the excel files imported and when they were imported.

[fatalmusic]

Here's a nice import form I designed to import 1 or multiple xls files in a designated folder the user selects on the form (using the Browse button).

Once the browse button is clicked and the import folder is supplied, and then the import button is clicked, it can cycle through the entire folder importing all and any xls files.

Hope it helps.

I tried using your test form, but i get an error after confirming the import from the folder i browsed to:

"You entered an expression that has an invalid reference to the property FileSearch"

Then this gets highlighted in the Importing Module after clicking 'debug':

Dim NumFound As Long
        With Application.FileSearch
        .NewSearch
        .LookIn = Directory
        .FileName = FileSpec
        If .Execute() > 0 Then

any idea how to solve this? I'm using Access 2007.

Thanks a lot