Each month I download the statements for each of my credit cards and import the transactions into a table in my FM8A database. In the Import Records script step I have specified the names (and paths) to the various possible downloaded files, and the script will import the first valid file and path that it finds. Only one valid downloaded file will be present at any time, and I will have already named it according to the credit card which it represents: e.g., citi.qif, mbna.qif, etc.

Here's my question: is there any way to determine the name of the file that the import script locates and processes? If it opens mbna.qif, for example, I'd like to be able to identify the imported records in the table as having come from the mbna credit card. Ideally, I'd like to name the file with both credit card name and statement date (mbna-02052006.qif) and strip out the name and date components into variables.

There may be a better way to look at this, and I'd be open to suggestions.

Thanks!

James McAlister

www.BulletinInserts.org

www.james-mc.com

FM8 allows for the import file path to be a variable, so you could do this:

set up a loop where the variable is set in turn to each of the paths. Do the import in the loop and catch for erros. If there are no errors it means the import succeeded and you'll know the variable holds the full path and the name of the file that was imported ...

Thanks, Wim! Yes, I can do this, and it makes a lot of sense. I'll have a look.

James

Wim, another thought.

In the VB Script Repository you once provided, I find a script called Locate A File Using A File Open Dialog Box. Here is the code:

Set objDialog = CreateObject("UserAccounts.CommonDialog")



objDialog.Filter = "VBScript Scripts|*.vbs|All Files|*.*"

objDialog.FilterIndex = 1

objDialog.InitialDir = "C:Scripts"

intResult = objDialog.ShowOpen

 

If intResult = 0 Then

    Wscript.Quit

Else

    Wscript.Echo objDialog.FileName

End If

Instead of echoing the file name to a message box (as is done here for illustration), is it possible to return the file name in some other form so that it could be put into a field or somehow assigned to a FM script variable? I don't know enough about VB script to know the possibilities.

Thanks!

James

Hi James,

Yes it is. Check the solutions/open source forum from the main index. I've uploaded how to do it when using the OS to browse for the contents of a folder and returning the result to FM. If you use the VBscript you posted and the rest of the technique in my demo file, you'll get it to work.

If not, post back and I'll make a second demo with the above VBscript.

Thanks, Wim. I found the demo last night after posting here and spent a bit of time studying what you had done. I'll try to apply your excellent technique to get the file selection dialog to work.

If you ever update the demo, though, having the choice to do either the file or the folder would be pretty slick.

I'll post back if I have problems.

Thanks!

James