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

Replacing and Converting in a Macro

When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially ...

Discover More

Entered Values are Divided by 100

Enter a value into a cell and "poof!" it is automatically divided by 100. What's going on, and how can you fix it so that ...

Discover More

Understanding Aero Peek

Windows implements a lot of visual effects that can make your screen and desktop look gorgeous. Those effects have a ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Creating a Function Inventory for a Workbook

Your worksheets are very often made up of formulas and these formulas are made up of functions. If you ever want to ...

Discover More

Showing RGB Colors in a Cell

Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see ...

Discover More

Saving Information in a Text File

The VBA programming language provided with Excel allows you to create and modify text files quite easily. Here's how to ...

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 6 - 0?

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.