automating daily import of csv file

[vicky22298]

Hi everyone,

I wonder if you could help me. We receive a daily csv file from another database and we are trying to automate this process as much as possible.

At the moment, the other system will extract any new records, save a file as importddmmyyyy.csv in a specified location e.g. c:import\pending\importddmmyyyy.csv and then make a call to open access giving access the file path as follows

masaccess.exe ”C:\mydatabase.mdb” /nostartup / Excl/ cmd Filepath=“c:import\pending\import09082013.csv”

I have configured access to automatically open a form, "ImportForm" when it opens, and I have configured this form to automatically run an event procedure on loading "importdailyfile".

My problem lies in writing this procedure. Basically I need it to run the Transfer Text command (I have already create an import specification)

DoCmd.TransferText acImportDelim, “myimportspec”, ”table to append the data to”, filepath, true

The problem I am facing is that as the filepath changes on a daily basis given the naming conventions (which i cannot change) and my knowledge of VBA s pretty basic, I don't know how to make the procedure pick up the filepath that the other system is providing in the call to open access.

Any suggestions will be very very very much appreciated

Thanks

Vicky

 

[pbaldy]

As long as the naming remains consistent, you can build the path:

"C:\blah\blah" & Format(Date(), "ddmmyyyy") & ".csv"

 

[vicky22298]

Thanks Paul!
Oh God it was so obvious!! :banghead::banghead::banghead:
I guess I just got too focus on how to use the filepath that I was given, when I could simply just have done this from the very beginning and make the call only to open access...

many thanks again and have a great weekend

 

[pbaldy]

Happy to help and welcome to the site by the way!