Import Text and Bypass Wizard

[bpowers]

Hi,

I am trying to write a Macro that will alow me to choose a text file to important but bypass the Import Text Wizard when doing so. I used the record macro function to get this code:

Sub Macro3()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;Z:\Brian Powers\Brian\2009.02.09\2009.02.09.Hoa.CG.WholeHeart.AST-A.10-9.1st..txt" _
        , Destination:=Range("B55"))
        .Name = "2009.02.09.Hoa.CG.WholeHeart.AST-A.10-9.1st."
        .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 = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

The thing that I cannot figure out is what code to add (and where to add it/replace existing code) so that I am prompted to choose the file upon running the Macro (Now it is just finding the file I looked for when I specified it). I think that if I am able to do this the rest should run smoothly and import with the desired settings without the hassle of going through the Wizard. Any ideas?


Also, on a similar note. Im not sure if this is possible but I was wondering if there was a way to have the filename of the imported txt file (After it has been selected) inserted into a cell in the spreadsheet.

Thanks in advance,
Brian

[rylo]

Brian

Try something like

getfile = Application.GetOpenFilename
  MsgBox getfile
  
  If getfile <> False Then
    Range("B54").Value = getfile
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & getfile _
        , Destination:=Range("B55"))
        .Name = "2009.02.09.Hoa.CG.WholeHeart.AST-A.10-9.1st."
        .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 = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
  End If

rylo

[bpowers]

Ah, that works perfectly for importing the text files. Thanks so much! Do you have any idea if the other thing is possible. Pasting the name of the inserted text file into a cell somewhere in the sheet? I'm thinking it should be I just dont have the experience to know the code to do so. Thanks again

[bpowers]

Ah, I misread the code. That does work perfectly. Thanks!

[rylo]

Brian

If this has solved your problem, can you please edit your original post, and mark it as SOLVED.

rylo