import excel different file everyday

[elfranzen]

OK I have a Excel file that is created every day and is saved at 2am with "file name + date" example "myfile (90809).xls" this will change everyday but at 2:05am I want this data to be imported into access. I have thought about importing all the data wiping the sheet and starting with the same name again but if for some reason the import doesn't work I want a back up. I am getting this data off the web and I have to capture it in real time. any help would be wonderful. Thanks

 

[DCrake]

Is this a 7 day occurance? what happens at weekends?

You could create an import table that holds the status of current imports. When the file is imported it appends that information to the import log file. You can use the Date() function to derive the current dates file name and test for existance using the Dir() function. Then test the log to say if exists on machine but not in log file import else ignore. Once the data is in the table and has been verified then you can kill the old file or move it to a secure location.

David

 

[elfranzen]

This is a 7 day everyday it takes snapshots every 15 mins and logs them in a sheet then at the end of the day it takes all 24 sheets (1 for each hour) and builds a summary I would like this to be imported in to access every day just after 2 am. I think it might be easier if I tell excel to save as an import file (name always stays the same) then after import it saves the sheet with the date added and then wipes the sheet clean and starts over.

 

[gcgc2000]

Hi David,
I am also looking to accomplish the same what you described below. I am very new to VBA/Access. Could you please provide a sample code if possible.
Regards
GCGC

Is this a 7 day occurance? what happens at weekends?

You could create an import table that holds the status of current imports. When the file is imported it appends that information to the import log file. You can use the Date() function to derive the current dates file name and test for existance using the Dir() function. Then test the log to say if exists on machine but not in log file import else ignore. Once the data is in the table and has been verified then you can kill the old file or move it to a secure location.

David