TransferText with variable file names? 1

[stormtrooperofdeath]

I'm new to this site and new to VB and I need some help!

I need to automate a process using VB.

A .csv file is generated about every 15 minutes and is automatically emailed as an attachment. The contents of this file needs to be imported into an access database. The filename includes the current date and time, for example: 20031216100001512.csv. I have a program off the web which automatically detaches the file and stores it in a directory. It also gives me the option of running a script - this is what I need to create.

What I need to do is create a VBA that imports data from this .csv via the TransferText command, but I want to pass the name of the current file (the one that just got detached from the email) as a parameter for the TransferText command (not using a static, hardcoded one like it seems to want). This VB script needs to be run from a command prompt.

I dont know how to program in VB and I dont know how difficult something like this is. I created the TransferText macro within VB using a static filename but I dont know how to call this macro from a command prompt (I tried calling it at startup but it didnt seem to run the macro - and i dont want to use an AutoExec macro).

Does this require a full blown VB program? Can anyone help me please?

Thanks!

 

[MinusM]

This is the string to be used at the command prompt

"C:\Program Files \Microsoft Office\Office10\MSACCESS.EXE" "c:\MyFolder\Mymdb.mdb" /cmd 20031216100001512.csv

Within Access, if you can create at start up use the function Command() to retrieve the Filename passed.

How familiar are you at access development? It may be better to have a form called STARTUP and put the transfertext code in the FormLoad event of this form

Then you have to assign in the database menu
tools->Startup->Display Form/Page. In the Dropdown below select STARTUP

Best of luck

 

[cwarner]

I did the same thing and created a variable on my form that contained the actual filename; I had users select a file to be read in, so it also changed all the time. so my transfertext statement looks like this:

If Me!fileformat = "Comma-delimited (.csv)" Then DoCmd.TransferText acImportDelim, , tbl_out,
Me!Fullfilename, True

where me!fullfilename is a field on my form where the user selects a filename.