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
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
I found what you should do if you know URL (for example www.google.com):
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...![]()
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
Last edited by Leith Ross; 11-02-2014 at 03:33 AM. Reason: Added Code Tags
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 Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
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
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
wrong code...the rigth one is in JRidge's post
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks