VBA Excel Importing File

For VBA Excel importing file the following code can be used to import a comma separated text file...

Sub vba_excel_importing_file()
    Dim strFileName As String
    
    strFileName = InputBox("Enter the full path to the comma " & _
               "separated file to import")
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
               strFileName, Destination:=Range("A1"))
        .Name = "vba excel importing file"
        .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 = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Values for the line...

.TextFileColumnDataTypes = Array(1, 1, 1, 1)

can be...

1 = xlGeneralFormat General 
2 = xlTextFormat    Text 
3 = xlMDYFormat     MDY date
4 = xlDMYFormat     DMY date 
9 = xlSkipColumn    Skip column 

You can use either the number or the constant.

This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more and on how to build a success-guaranteed site with no technical skills.

Custom Search

Return from VBA Excel Importing File to VBA Code Samples

Return to our Homepage