Problems importing HTML from defined string

Question

I have a piece of code that loads a website and clicks a link that opens a popup. The contents of this popup is what I need to be imported into Excel (VBA) so I can manipulate that data. The issue is that this link's web address always changes, but the link is always in the same place.

The following code defines the currently active IE instance's URL as "IEURL". I would like to use the code to import the table but I get an error "Run-time error '1004': The address of this site is not valid. Check the address and try again".

Sub Button1_Click()

Dim objIE As SHDocVw.InternetExplorer
Dim IEURL As String
LastRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Set objIE = New InternetExplorerMedium
'apiShowWindow objIE.hwnd, SW_MAXIMIZE
objIE.navigate "http://www.youtube.com"
objIE.Visible = True
Do While objIE.READYSTATE <> 4 And objIE.Busy
DoEvents
Loop

'Call Sleep
Application.Wait (Now + TimeValue("0:00:5"))

IEURL = objIE.LocationURL


ThisWorkbook.Sheets("Sheet1").Activate
Rows("6:250").Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;IEURL", _
Destination:=Range("a6"))

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub

Can anyone help me out here? P.S. I just used YouTube as an example here, as it demonstrates the same problem as the actual website I am trying to import

ChipsLetten · Accepted Answer · 2015-06-05 14:44:24Z

0

The objIE.LocationURL property returns a string which you are storing in a string variable. When you later try to use that variable you should append it to a string rather than just putting the name of the variable inside the string. So change

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;IEURL", _

to

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & IEURL, _

btw, in Excel 2013, your sample code fails at IEURL = objIE.LocationURL

answered Jun 5, 2015 at 14:44

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JGoldz75 Over a year ago

Thanks for the response! I'm actually using Excel 2010 which does not return an error on that line. (I got that piece of code from the following post: stackoverflow.com/questions/11158341/… Making these changes in my code no longer produces the error, but it does not import anything when it runs. Any ideas on this? Perhaps this is not the right approach to importing the page?

ChipsLetten Over a year ago

I think that might be a separate question and probably depends on how the website constructs the pages.

JGoldz75 Over a year ago

Use of this code in a new spreadsheet worked fine. Thanks for helping!

Collectives™ on Stack Overflow

Problems importing HTML from defined string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related