Microsoft Excel - How to associate cell value with web address when pulling content from the web (EXCEL VBA)

Asked By Shawn Murty on 26-Aug-14 02:15 PM
Looking to pull data from online database. I was hoping I could use a cell's value during this process that changes the URL page.

I highlighted the area that would be part of my current excel sheet (location $A$5 if needed) that needs to go into the URL in yellow



example:
Sub Online_data_formula()
'
' Online_data_formula Macro
'
 
'
  ActiveWindow.SmallScroll Down:=-33
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.hpsapps.com/pcore/testReports.asp?fSearch=Now&fSerialNumber=14-157265&Submit=Search" _
    , Destination:=Range("$A$9"))
    .Name = "testReports.asp?fSearch=Now&fSerialNumber=14-157265&Submit=Search"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "4"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Harry Boughen replied to Shawn Murty on 27-Aug-14 08:15 AM
You could try adding this to the front of your macro (adjusting the sheetname as necessary):

Dim strAdr As String
Dim strBit1 As String, strBit2 As String
Dim rngSrc As Range

Set rngSrc = Sheets("Sheet1").Range("A5")
strBit1 = "testReports.asp?fSearch=Now&fSerialNumber="
strBit2 = "&Submit=Search"

strAdr = strBit1 & rngSrc.Value & strBit2

Then change the relevant line to:

.Name = strAdr

Shawn Murty replied to Harry Boughen on 02-Sep-14 08:49 AM
Harry, I wish I saw this earlier. Your code would of definitely worked.  

I used a more primitive version :

"URL;http://www.hpsapps.com/pcore/testReports.asp?fSearch=Now&fSerialNumber=" & Range("B10").Value & "&Submit=Search" _