Get External Data Web Query from local directory

  • I an trying to wirte a macro that will allow my users to import data from a HTML file locatedon their local hard drive. THe directory information will not change however I would like the opporutnity to "choose" the appropriate file prior to completing the macro.


    Do I need to construct a form first? If so, a little help would be greatly appreciated. Here's what I have:


    Sub QuestionGetExtData()
    '
    ' QuestionGetExtData Macro
    ' Macro recorded 6/18/2006 by Robert A. Grone
    '


    '
    Range("H11").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;C:\foo\foo.html" _
    , Destination:=Range("A11"))
    .Name = "foo.html"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub


    I would like to change the "foo" statements so that the user is prompted for this information:


    How do I change:


    "URL;C:\foo\foo.html" _
    , Destination:=Range("A11"))
    .Name = "foo.html"


    to allow for a file selection from directory c:\foo


    Thanks....


    R Grone
    :confused:

  • Re: Get External Data Web Query from local directory


    Quote from norie

    Use GetOpenFilename.


    I do not understand...


    With ActiveSheet.QueryTables.Add(Connection:= _
    GetFileOpen."C:\foo"
    , Destination:=Range("A1"))


    Is this what you are indicating?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!