Text files can be used to store data in certain scenario. However, we need to use data from these text files in our excel template in specific format. So, use of text files data into excel can be a manual and lead to data accuracy miss. Sometimes, there will be data mismatch in excel and text file. To avoid such a data inaccuracy a simple excel macro can be used while will help to import data from text file to excel workbook. Let's see how data can be import from text file to excel using excel macro.
Here is the VBA code to import data from text file to excel:
Option Explicit
Sub Import_From_Text()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com
'Declare Variable
Dim TXT_FILE_PATH As String
Dim DESTINATION_RNG As String
Dim isTAB, isSEMICOLON, isCOMMA, isSPACE As Boolean
Dim Cn As Variant
'Variable used to assign data delimiter type in text file data
'Here Tab used for text file data delimiter
isTAB = True
isSEMICOLON = False
isCOMMA = False
isSPACE = False
'Variable used to provide text file path and range from activesheet
'to import data from text file
TXT_FILE_PATH = "C:\Users\Dreams\Desktop\Sample_Test_File.txt"
DESTINATION_RNG = "$A$1"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & TXT_FILE_PATH, Destination:=Range(DESTINATION_RNG))
.Name = "Test_Connection"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = isTAB
.TextFileSemicolonDelimiter = isSEMICOLON
.TextFileCommaDelimiter = isCOMMA
.TextFileSpaceDelimiter = isSPACE
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'For loop to delete connection created during text data import
For Each Cn In ActiveSheet.QueryTables
Cn.Delete
Next Cn
MsgBox "Text file data imported successfully.", vbInformation, "Text file data import"
End Sub
Option Explicit
Sub Import_From_Text()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com
'Declare Variable
Dim TXT_FILE_PATH As String
Dim DESTINATION_RNG As String
Dim isTAB, isSEMICOLON, isCOMMA, isSPACE As Boolean
Dim Cn As Variant
'Variable used to assign data delimiter type in text file data
'Here Tab used for text file data delimiter
isTAB = True
isSEMICOLON = False
isCOMMA = False
isSPACE = False
'Variable used to provide text file path and range from activesheet
'to import data from text file
TXT_FILE_PATH = "C:\Users\Dreams\Desktop\Sample_Test_File.txt"
DESTINATION_RNG = "$A$1"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & TXT_FILE_PATH, Destination:=Range(DESTINATION_RNG))
.Name = "Test_Connection"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = isTAB
.TextFileSemicolonDelimiter = isSEMICOLON
.TextFileCommaDelimiter = isCOMMA
.TextFileSpaceDelimiter = isSPACE
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'For loop to delete connection created during text data import
For Each Cn In ActiveSheet.QueryTables
Cn.Delete
Next Cn
MsgBox "Text file data imported successfully.", vbInformation, "Text file data import"
End Sub