Prompt to locate Import data file into Excel

[LGMan]

Hi,
I have a routine that imports data into my excel worksheet. I would like to be prompted to locate the file but can't figure out how to incorporate a prompt within the following

Sub ImportData()

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\costs02.data", _
Destination:=Range("D1"))
.Name = "costs02"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 9)
.TextFileFixedColumnWidths = Array(1, 6)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks in advance

 

[rmikesmith]

Try the following; uses the GetOpenFilename method to retrieve path & filename from user.

Sub ImportData()
Dim FPath As Variant
Dim FFilter As String

    FFilter = "Data Files (*.data), *.data"
    FPath = Application.GetOpenFilename(FFilter, , "Select Data File")
    If FPath = False Then Exit Sub
 
   With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;[b]FPath[/b], _
        Destination:=Range("D1"))
        .Name = "costs02"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 9)
        .TextFileFixedColumnWidths = Array(1, 6)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Regards,
Mike

 

[PHV]

I'd replace this:
"TEXT;FPath, _
with this:
"TEXT;" & FPath, _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LGMan]

Thanks Mike
however I get a syntax error on this block

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;FPath, -
Destination:=Range("D1"))

 

[LGMan]

Whoops,
have just seen PH's reply which sorts the problem.

Thanks

 

[rmikesmith]

Thanks, PH. In my haste...


Mike