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" 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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Macros can make your use of Excel much more powerful. If you have a macro that is triggered by an on-screen button, you ...
Discover MoreMany times you need to select just the visible cells before taking some action. It is helpful to know how to make this ...
Discover MoreMacros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments