macro for importing text file

[darchi]

I'm new to macros and need to create one that will prompt for a file that will be imported for parsing. My major issue is for the macro to stop and ask the user for file location/name before it continues to perform the parsing functions which are straight foward. Thks!

 

[Joe4]

Welcome to the Board!

Take a look here: http://excelvbamacro.com/how-to-open-excel-file-using-macro.html

 

[darchi]

Thanks for the welcome and the link. I had created a macro and tried to include the Application.GetOpenFilename but didn't know where or how to insert it; following is the macro: I understand that the red text should be in some way replaced...

Sub Macro5()
'
' Macro5 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\darchi\Desktop\EMI-EDI-Files\INBOX\20110809163036-070f8b51.edi.txt" _
, Destination:=Range("$A$1"))
.Name = "20110809163036-070f8b51.edi"
.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 = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "~"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("A1").Select
End Sub
Sub Macro1()
End Sub

 

[Joe4]

Combining the file browser with the first part of your code that opens the file would look something like this:

    Dim vFile As Variant
 
    vFile = Application.GetOpenFilename("Text Files (*.txt)," & "*.txt*", 1, "Select Text File", "Open", False)
    If TypeName(vFile) = "Boolean" Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & vFile _
        , Destination:=Range("$A$1"))
''        .Name = "20110809163036-070f8b51.edi"
        .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 = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "~"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

 

[darchi]

you guys are the greatest! I tested the modifications and they worked excellently well! got a question though; what functionality or purpose does the red text serve within the command string?

vFile = Application.GetOpenFilename("Text Files (*.txt)," & "*.txt*", 1, "Select Text File", "Open", False)
If TypeName(vFile) = "Boolean" Then Exit Sub

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & vFile _
, Destination:=Range("$A$1"))
'' .Name = "20110809163036-070f8b51.edi"

 

[Joe4]

I believe that it just names/renames the worksheet tab name the data is being imported to. I commented it out, as it is not necessary.

 

[darchi]

Thanks!