Activesheet.Querytables question.

[odonnks]

Below is a macro that does exactly what I need for the specified file.

I've tried for most of the day now and cannot find how to make the macro prompt the user for a file. The files i use won't always be named the same.

Any feedback will be appreciated.
Thank you!

Sub Macro1()
'
' Macro1 Macro
' Read text file
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\kevin70\Desktop\FInal Cal 2.7.19.txt", Destination:=Range( _
"$N$2"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(15, 10, 13, 12, 1, 6, 7, 7, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:K").Select
Range("K1").Activate
Selection.Delete Shift:=xlToLeft
End Sub

 

[Domenic]

Try...

Sub Macro1()

    Dim vFilename As Variant
    
    vFilename = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Select Text File", _
        ButtonText:="Select", _
        MultiSelect:=False)
    
    If vFilename = False Then Exit Sub 'user cancelled
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & vFilename, Destination:=Range("$N$2"))
        'etc
        '
        '
    End With


End Sub

Hope this helps!

 

[odonnks]

Hi and thank you for getting back to me.

I tried what you posted. It did prompt me for the .txt file but after I selected it nothing populated in the spreadsheet.

Try...

Sub Macro1()

    Dim vFilename As Variant
    
    vFilename = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Select Text File", _
        ButtonText:="Select", _
        MultiSelect:=False)
    
    If vFilename = False Then Exit Sub 'user cancelled
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & vFilename, Destination:=Range("$N$2"))
        'etc
        '
        '
    End With


End Sub

Hope this helps!

 

[odonnks]

OK, got it, Thank you !!

I miss pasted your code.

Thank you again !!