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" _