Conditional Saved Import in Access/VBA

Question

I'm fairly new to this stuff, so hopefully someone our there can help me out.

The setup: I've got an Access 2007 database that imports data, then performs a set of queries and exports the results on a monthly basis.

The monthly data is saved as either Excel spreadsheets or in text files in a "Current" folder. I saved the rather tedious import steps for each file to facilitate the process of adding them as tables in Access.

There are a lot of files to import, so I wrote a simple VBA code to run all of the saved imports at once.

Public Sub DatabaseImp()
DoCmd.RunSavedImportExport "Import-Excel1"
DoCmd.RunSavedImportExport "Import-Excel2"
DoCmd.RunSavedImportExport "Import-Txt1"
DoCmd.RunSavedImportExport "Import-Txt2"
DoCmd.RunSavedImportExport "Etc....."
End Sub

Also, I created a macro to run this code and everything works fine.

BUT,

The Problem: Depending on the month, certain files will be included in the 'Current' folder and others won't be.

For example, suppose that this month there is no 'Excel2' file to import into the database.

Is there anyway to modify the code above so that it only tries to perform the import IF there is something there for it to import?

I understand that I could simply do it manually, ignoring the 'Excel2' import. However I would like to keep the process automated.

I'm looking for some kind of conditional IF statement that I could add on to the end of each line, e.g.:

DoCmd.RunSavedImportExport "Import -Excel1" 
 (ONLY IF there is an excel1 file in the 'current' folder to import)

Ideas, anyone?

Thanks,

Tom Collins Tom Collins · Accepted Answer · 2013-06-10T20:31:42

You can use the Dir command. If the result is is an empty string, then it doesn't exist.

So, on a Windows machine, Dir("C:\Windows\explorer.exe") will return explorer.exe, but Dir("C:\Windows\bumblebee.exe") will return "".

So set up an If statement.

If Len(Dir(YourFile)) > 0 then
  DoCmd.RunSavedImportExport "Import -Excel1"
End if

Conditional Saved Import in Access/VBA

1 Answers