Importing Based on a Partial File Name

Written by Allen Wyatt (last updated April 1, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


Ira receives a weekly text-delimited file that he imports into Excel by using a macro. The file name was always the same each week, so Ira was able to embed it into the macro; this made importing very easy. Recently the vendor's new system started changing the file name each week, but the first 12 characters always remain the same. Ira wonders how he can code the macro to import this text file without needing to modify the file name each week.

There are a few ways you can approach this problem, and all of them may mean some extensive recoding of your macro. The method you choose should depend on how you want to do your work each week. For instance, let's say that you know the directory in which the incoming file is stored, what the base 12 characters are for the filename, and the filename extension. With that information, you could simply prompt the user to provide the suffix for the filename, in this manner:

Sub OpenImportFile()
    Dim sFileName As String
    Dim sBase As String
    Dim sSuffix As String
    Dim sExt As String

    sBase = "c:\MyDirectory\First12Chars"
    sExt = ".csv"
    sSuffix = InputBox("Enter suffix for filename")

    sFileName = sBase & sSuffix & sExt
    Workbooks.Open Filename:=sFileName
End Sub

You can, obviously, change the base and extension as you desire. This example assumes that you are importing a CSV file, which should open in Excel just fine.

If you are simply placing a group of files into a folder and you want to open all of them, then the code becomes a bit quicker to run because you don't need to get user input.

Sub OpenImportFiles()
    Dim sFileName As String
    Dim sBase As String
    Dim sExt As String

    sBase = "c:\MyDirectory\First12Chars"
    sExt = ".csv"

    sFileName = Dir(sBase & "*" & sExt)
    If sFileName = "" Then
        MsgBox "No Files Found"
        Do While sFileName > ""
            Workbooks.Open Filename:=sFileName
            sFileName = Dir
    End If
End Sub

This approach opens all the CSV files in the folder; to open a different type of file, just change what is stored in the sExt variable. One thing to remember is that since all the CSV files in the folder are opened, that means it may open import files from previous weeks. (This may or may not be what you want.)

Everything presented in this tip so far assumes that when you "import" a file, all you want to do is open the file so you can work with it. That may not be the case; you may want your macro to process the import file in some way and do something with it. That functionality may already be in your older macro, so all you need to do is change how the import file is identified. If this is the case, the code you use can be adapted from the code already presented in this tip.

Another way to grab the data out of a text file and stuff it into a workbook is to use the OpenText method, as shown in this example code.:

Sub OpenImportFile()
    Dim sFileName As String
    Dim sBase As String
    Dim sExt As String

    sBase = "c:\MyDirectory\First12Chars"
    sExt = ".csv"
    sFileName = sBase & "*" & ".txt"

    Workbooks.OpenText Filename:=sFileName, Origin:= _
      xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:= False, Space:=False, _
      Other:=False, FieldInfo:=Array(1, 1), _
End Sub

A full exploration of what the OpenText method does is beyond the scope of this tip, but the result is that all of the delimited text files that begin with the base 12 characters should be opened in your workbooks. You can modify how the method works by changing the parameters. You'll want to pay particular attention to the parameters used to specify what delimiter character should be used.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13472) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Ensuring Proper Page Numbers for a Table of Authorities

Automatically create a Table of Authorities entry in your document, and Word might place the necessary field at the wrong ...

Discover More

Selecting a Field

Do you need to select a field? It is as simple as selecting a single character, as this tip explains.

Discover More

Disappearing Toolbar Buttons for Macros

One of the important configuration files for Excel is known as the XLB file. You should periodically make backups of this ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Editing Macros

Even if you do nothing but record macros, sooner or later you will have a need to edit what you record. Here's how to get ...

Discover More

Counting All Characters

Need to know how many characters there are in a workbook? You can find out easily with the handy macro introduced in this ...

Discover More

Converting Numbers to Strings

When creating macros, it is often necessary to change from one type of data to another. Here's how you can change from a ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 3?

2023-05-01 13:18:55

R. A. Williams

To J. Woolley -- Thanks! Comment re-submitted to the page you gave me.

2023-04-26 10:11:26

J. Woolley

@R. A. Williams
Perhaps you intended to post your comment here:

2023-04-25 17:29:18

R. A. Williams

Your article on custom number formats reminded me that one of the oft-referenced self-help tips I generated for myself might be useful to others as well. It works for me (retired Ph.D. nuclear scientist) -- I like to see the complete spec, then have notes on it. So here it is. Feel free to make it available to others. [I would have supplied a spreadsheet fragment if I could figure out how to do that. I would thin that this site, if any, would have that facility. Or, what am I missing?]
3 Custom Number Formats
> The custom format for displaying values has sections that can display positive, negative, zero numbers and text in different forms. The complete spec is:
The defaults are: val1 = ">0", val2 = "<0" and val3 = "=0", but they can be anything.
The example is below uses the defaults for the "val1", etc. specs.
[Green][>0]0.0;[Red][<0]#,##0" (arrghhh!)";[Blue][=0]0;"Blah... "@"NOT"
In the above, values >0 are in green with one digit to the right of the decimal point; values <0 are in red as integers and include a thousands separator and are followed by "(arrghhh!)"; zeros are in blue as an integer; any text is shown preceeded by "Blah... " and followed by "NOT".
> The commonly-used format codes are: "#" (place holder), "0" (displays a digit), and "," is the thousands separator(which can get tricky). "?" (space for insignificant zeros) is useful for fixed-pitch fonts and fractions.
> The only colors available are Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow.
> A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.
> A single character can be included by preceding it with a slash instead of putting it in quotes.
"The following characters are displayed without the need for quotation marks:
$, +, -, (, ), :, ^, ', {, }, <, >, /, !, &, ~, and space."
> Note that a format of ";;;" prevents anything from being displayed in the cell, regardless of value.
> Also note that "conditional formatting" can be applied to a cell. This function overlaps with the instructions inherent in the format codes above.
> The macro recorder gives color codes as negative numbers. These can be converted to standard values by adding 16777216 (= &H01000000&).


And thanks for the Excel Tips!

Bob Williams

2023-04-03 08:29:40

Tom W Van Dam

Excellent tip
I can see that an improvement would be to have a batch file (or something similar) to run when the task is complete and move the files to a finish folder so they don't get imported again the next time.

2023-04-01 11:53:07

J. Woolley

Here is a macro that uses Application.GetOpenFilename. It offers to open any CSV file (but is not based on First12Chars):

Sub OpenImportFile()
    Dim sFilter As String, sBase As String, vFileName As Variant
    sFilter = "CSV Files (*.csv),*.csv"
    sBase = ActiveWorkbook.Path 'or "C:\MyDirectory"
    ChDir sBase
    vFileName = Application.GetOpenFilename(sFilter)
    If vFileName <> False Then Workbooks.Open FileName:=vFileName
End Sub

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.