+ Reply to Thread
Results 1 to 7 of 7

web query

Hybrid View

  1. #1
    Registered User
    Join Date
    11-01-2014
    Location
    rome
    MS-Off Ver
    excel 2007
    Posts
    5

    web query

    Hi everybody,
    I never used vba excel, I need a web query and the URL is written in a cell. In my example, URL is written in N283 and I would like to print the table in AG1. May anybody paste the vba code for this? thank you

  2. #2
    Registered User
    Join Date
    11-01-2014
    Location
    rome
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: web query

    I found what you should do if you know URL (for example www.google.com):
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.google.com", Destination:=Range("$g$1"))
            .Name = "www.google.com"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    in my case, i think i just need to change the google address with what is written in N283 cell. unfortunately I've never used vba before and I can't do that...
    Last edited by Leith Ross; 11-02-2014 at 03:33 AM. Reason: Added Code Tags

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: web query

    Hello romilton,

    Welcome to the Forum!

    Here is your macro with the needed changes.
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;" & Range("N283"), Destination:=Range("$AG$1"))
            .Name = "www.google.com"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    11-01-2014
    Location
    rome
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: web query

    hi Leith,
    thank you, it works and that problem is solved! thank you!! but i have one more little problem...the n283 sometimes shows the URL and sometimes is empty, so I would like "shut up" the macro when that cell is empy. I apologize for the new question

  5. #5
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: web query

    Maybe:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
    
    If Not IsEmpty(Range("N283").Value) Then
    
    
        With ActiveSheet.QueryTables.Add(Connection:="URL;" & Range("N283"), Destination:=Range("$AG$1"))
            .Name = "www.google.com"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Else
        End If
    End Sub

  6. #6
    Registered User
    Join Date
    11-01-2014
    Location
    rome
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: web query

    wrong code...the rigth one is in JRidge's post

  7. #7
    Registered User
    Join Date
    11-01-2014
    Location
    rome
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: web query

    Thank you very much! Solved!! I'm ashamed but I can't solve one more problem
    now this is my code:
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & Range("N283"), Destination:=Range("$AG$1"))
    .Name = "www.google.com"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With
    Else
    End If
    End Sub

    I need to know what happens during the day, even when I'm not at home and my computer is off. So, how can my file run this macro at the time (shown as hh:mm) indicated in a cell (F283 in my case)? thank you so much for this and for what you've already done for me!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-01-2013, 04:21 PM
  2. [SOLVED] Loop through list, perform web query and save each query on its own page
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 07:50 AM
  3. Web Query Help? - Query Picture Title or Alt Text on a webpage?
    By teamtrav in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2012, 06:33 PM
  4. Problem with selecting range with in query table after query refresh
    By shooter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2012, 11:55 AM
  5. Web Query - Change a word in query to form a new query
    By scottymelloty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 04:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1