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


5

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"
    Else
        Do While sFileName > ""
            Workbooks.Open Filename:=sFileName
            sFileName = Dir
        Loop
    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), _
      TrailingMinusNumbers:=True
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. ...

MORE FROM ALLEN

How Word Handles Abbreviations

Abbreviations appear all over the place in our society. If you want to understand how Word recognizes them (which it has ...

Discover More

Disabling a Function Key

Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather ...

Discover More

Determining the Current Directory

When you use a macro to do file operations, it works (by default) within the current directory. If you want to know which ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Preserving the Undo List

The undo list can be a lifesaver when working in a macro. Unfortunately, the undo list is not preserved when you run a ...

Discover More

Disabled Macros

Do your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's ...

Discover More

Changing Directories in a Macro

Need to specify which directory on your hard drive should be used by a macro? It's easy to do using the ChDir command.

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 2 + 8?

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: https://excelribbon.tips.net/T007992_Adding_a_Custom_Format_to_those_Offered_by_Excel.html


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:
[color1][val1]form1;[color2][val2]form2;[color3][val3]form3;pre@
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
Subscribe

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.