Access VBA to Import a File with Variable Name

Need to import a file into Access, but the file name keeps changing? If so, I’m going to show you how to create an Access VBA to import a file with a variable name.

Here are the topics that I’m going to cover:

The Example to be Reviewed

Let’s say that you import a daily file into Access, but the file name has a date suffix that changes every day.

For example, suppose that you have the file name of: “client_list_yyyymmdd,” where the “yyyymmdd” represents the date when the file was created.

Here are some examples of daily files:

  • client_list_20171127
  • client_list_20171128
  • client_list_20171129

If the file that you import into Access is a CSV/Text/Excel file, then you can write a VBA to import that file using a click of a button. Let’s see how you can accomplish this task.

Access VBA to Import a CSV File with Variable Name

Here you can see the VBA to import a CSV file, where the file name has a date suffix that changes every day. For the code to work, you’ll need to create a specification name in Access when dealing with CSV/TXT files.

 

Dim DATE1 As String
Dim PATH As String

DATE1 = Format(Date, "yyyymmdd")
PATH = "C:\Users\Doron E\Desktop\Access VBA to Import a File\prefix name" & DATE1 & ".csv"

DoCmd.TransferText acImportDelim, "Import Specification", "Table", PATH, True

 

Let’s explain the components of the code:

  • DATE1 = Format(Date, “yyyymmdd”) represents the system date, formatted to yyyymmdd
  • PATH is the variable that represents the location where your CSV file (to be imported) is placed.  The “prefix name” represents the file name portion that never changes (for example, the “client_list_” portion remains constant across all the files to be imported)
    While the variable DATE1, and the extension of “.csv”, are concatenated at the end of the PATH string. They represent the suffix of the file name to be imported  
  • DoCmd.TransferText acImportDelim is used when importing delimited CSV files
    • “Import Specification” represents the specification name you created. If, for example, you created a specification name that is saved as client_list_specification, then you’ll need to put that name within quotations: “client_list_specification”
    • “Table” is the name of the Access table where the imported information, from the CSV file, will be stored. If, for instance, the name of your table is client_list, then you’ll need to type that name within quotations: “client_list”
    • PATH is the variable that contains the CSV file location and name. Quotations should not be used for variables
    • True should be chosen when the data to be imported contains headings

Here is how your VBA might look like in Access (you’ll need to tailor the PATH variable to your needs):

 

Access VBA to Import a File with Variable Name

 

Note that I added a button to run the above VBA on click. You can add buttons to Access Forms under the Design View.

Enhancements to the VBA code

If you want to delete all the records in the Access table, before you import the new data from the CSV file, you can insert the following portion into the VBA code (where “Table” represents the name of your Access table):

 

DoCmd.RunSQL "Delete * from Table"

 

You can also place an error message using a message box in Access. The message box can be used to inform users about issues when importing the data into Access. Potential issues that may occur when importing data into Access are:

  • The file to be imported is not present in the location defined; and/or
  • The file name is not following the structure specified in the VBA; and/or
  • The file is currently opened by another user

Here is the VBA code with the enchantments:

 

Dim DATE1 As String
Dim PATH As String

DATE1 = Format(Date, "yyyymmdd")
PATH = "C:\Users\Doron E\Desktop\Access VBA to Import a File\prefix name" & DATE1 & ".csv"

DoCmd.RunSQL "Delete * from Table"

On Error GoTo Bad
DoCmd.TransferText acImportDelim, "Import Specification", "Table", PATH, True

Exit Sub

Bad:
MsgBox "The File for the date " & DATE1 & " cannot be found in the directory " & PATH & " OR the file is currently opened by another user"

 

And here is how your VBA code might look like in Access:

 

Access VBA to Import a CSV File with Variable Name

Access VBA to Import an Excel File with Variable Name

In this case, the good news is that you don’t need to create a specification name when importing an Excel file into Access.

We will use the same example as before, where the file name has a date suffix that changes every day.

Please note that you may initially need to execute the Import Spreadsheet Wizard only once (to set the Excel import into your defined table), before this code will start working on an ongoing basis.

Here is the code:

 

Dim DATE1 As String
Dim PATH As String

DATE1 = Format(Date, "yyyymmdd")
PATH = "C:\Users\Doron E\Desktop\Access VBA to Import a File\prefix name" & DATE1 & ".xlsx"

DoCmd.RunSQL "Delete * from Table"

On Error GoTo Bad
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table", PATH, True

Exit Sub

Bad:
MsgBox "The File for the date " & DATE1 & " cannot be found in the directory " & PATH & " OR the file is currently opened by another user"

 

You’ll notice that in this case, we used the suffix of “.xlsx”  (for the PATH variable) to represent the Excel file.

We also used the command “TransferSpreadsheet” to deal with Excel files.

Your VBA code might look like this:

 

VBA to Import a File with Variable Name

Use a Combo Box to get User’s Input

You may also use a Combo Box in Access, where the user can input data that will be used to construct the file name. For example, the user can type a date in the combobox, and that date will be used as the suffix-date in the file name.

To start, you’ll need to find the name of the combo box that you created, in order to use it in the VBA code below, within the variable of:

DATA1 = Format(Me.combo name, “yyyymmdd”)

To accomplish this task:

  • Select the combo box while in the Design View
  • If the Property Sheet is not already opened, then press F4. You’ll now see the comb box name at the top of the property sheet (here it is “Combo3”)
  • Also make sure that the format is set to Short Date in the Property Sheet.  The user will have to type the short date in the combo box (the short date has the structure of dd-mmm-yyyy, such as 02-Dec-2017). Then the variable DATE1 in the VBA will transform the short date into “yyyymmdd” which represents the suffix of the file name.

 

Combo box - MS Access

 

In general, you can use combo boxes to retrieve any information from the user, and then use it in the VBA. Just make sure that the format is set properly to the type of data to be inserted by the user.

Here is the code with the combo box:

 

Dim DATE1 As String
Dim PATH As String

DATE1 = Format(Me.combo name, "yyyymmdd")
PATH = "C:\Users\Doron E\Desktop\Access VBA to Import a File\prefix name" & DATE1 & ".csv"

DoCmd.RunSQL "Delete * from Table"

On Error GoTo Bad
DoCmd.TransferText acImportDelim, "Import Specification", "Table", PATH, True

Exit Sub

Bad:
MsgBox "The File for the date " & DATE1 & " cannot be found in the directory " & PATH & " OR the file is currently opened by another user"

 

Please note that you may need to initially execute the Import Text Wizard only once, and re-save your specification name (in order to set the CSV import into your defined table), so that the code will start working on an ongoing basis.

And this is how your VBA code could look like in Access:

 

Use a combo box to get user’s input