PDA

View Full Version : Help with web adresses and vba



vexel77
01-27-2013, 04:13 PM
Provide for the coders:
Excel 2010
I want to get vba to look to specific cell in a worksheet to get a webaddress. I would like to paste different web addresses in a specific cell in excel which will then automatically update in my piece of vba code.
Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;(web address entered here)" _
, Destination:=Range("$A$1"))
.Name = "flow"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """tab"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub
The above is the piece of code im currently using, I paste in the web address after the URL; and then run it. If the code could be changed to pull the web address from cell A1 in a sheet called Websites it would save me a lot of hassle. I'm not currently allowed to post web addresses so I have had to delete the url that should come after URL;.
If anyone out there could help me it would be greatly appreciated, I tried to use the recorder to record me copying and pasting the web address from cell A1 in the websites sheet but it did not produce the result I was looking for.
If you require the excel file I can attach that no probs, many thanks for any help in advance.Regards, gerard