Written by Allen Wyatt (last updated August 12, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003
Ole ran into a problem importing information into an Excel workbook. It seems that the files he needs to import typically have thousands and thousands of records in them. Ole doesn't need most of the input rows, however, and normally gets rid of them once the records are imported into Excel. Ole is looking for a way to get rid of the unwanted records during the import process, so that he has less work to do when the data is in his workbook.
There are a couple of different ways that a solution to this problem can be approached. One solution is to use Access as your first importing step. Access will easily handle the thousands of records you want to import—even if there are more records than what you can import into Excel. You could import the file into Access, filter out the unwanted records, and then export the resulting table as an Excel workbook.
The best solution, however, may be to bypass Excel's import filters entirely. You can easily write an import routine in VBA, and allow it to process the import file. For instance, consider the following macro:
Sub Import() Dim sFile As String Dim sUnwanted As String Dim sDelim As String Dim iRow As Integer Dim iCol As Integer Dim bBadRecord As Boolean Dim iTemp As Integer sFile = "d:\data.txt" sUnwanted = "bad text" sDelim = "," Open sFile For Input As #1 iRow = 1 While Not EOF(1) 'Scan file line by line iCol = 1 Line Input #1, sBuffer ' Check to see if should ignore record bBadRecord = Instr(sBuffer, sUnwanted) If Not bBadRecord Then iTemp = Instr(sBuffer, sDelim) While iTemp > 0 With Application.Cells(iRow, iCol) .NumberFormat = "@" 'Text formatting .Value = Left(sBuffer, iTemp-1) End With iCol = iCol + 1 sBuffer = Mid(sBuffer, iTemp+1, Len(sBuffer)) iTemp = Instr(sBuffer, sDelim) Wend If Len(sBuffer) > 0 Then With Application.Cells(iRow, iCol) .NumberFormat = "@" 'Text formatting .Value = sBuffer End With End If iRow = iRow + 1 End If Wend Close #1 End Sub
This macro opens a data file and reads each record in the file. It checks the record to make sure it is OK to import, and then pulls the record apart, based on a delimiter, and stuffs the information into the current worksheet. You can change the name of the data file (the sFile variable), the text that indicates a bad record (sUnwanted variable) and the delimiter (sDelim variable).
As an example, let's assume that you have a data file named Customers.txt. This file contains all your customer records, but you don't want to import the records for customers with addresses inside the United States. Further, the records in the data file use a tab character between each field. In this case, you would only need to make the following changes to the variables at the beginning of the macro:
sFile = "d:\Customers.txt" sUnwanted = "United States" sDelim = Chr(9)
Once you run the macro, the current worksheet contains just the desired data.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2239) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Selectively Importing Records.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If you use Excel's Open dialog box to search for files and you notice that doing so ends up crashing your system, you may ...
Discover MoreIf you have links in your workbook to data in other workbooks, you may want to control whether Excel updates those links ...
Discover MoreNeed to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments