Macro to refresh only sometimes working?

[roxnoxsox]

I'm using VBA to download data from a website - but this website first needs to be refreshed.
The macro I'm using to do this is: (website used is only one example of many)


Dim ie As Object
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
ie.NAVIGATE "http://www.mangold.se/MangoldOnline/produktinformation/SE0004926145.htm/"
ie.Visible = True

While ie.busy
DoEvents
Wend

SendKeys ("{F5}")
ie.Quit


This works for the first couple of websites I've tried, but then stops refreshing after that. So I have a long list of web queries where VBA: opens the internet explorer link, refreshes the webpage, closes the webpage, downloads the webpage into excel, picks out the data I need, clears the query table and then goes onto the next link. But this code is only working for the first 3 webpages, then stops refreshing. The webpages are all from the same site so I don't think the error lies there..

Any suggestions?

Many thanks

[snb]

Use querytables instead of this VBA-code.

[roxnoxsox]

Hi snb - thanks for the attached spreadsheet. I don't think I've used querytables before - how does this work?

[snb]

Ribbon /data /from web

and you will be guided to the result.

[roxnoxsox]

Hi snb- I think this is what I'm already doing. Sorry, my fault for lack of clarification, it's difficult to explain:

I have a long list of websites, like the one used above, which I download into excel (using the method you suggested), pick out the data I need, then clear it and move onto the next website. This part works fine, I just macro recorded it and the VBA runs okay. My problem is that the date on the website & the data needed is stale for some reason. Unless I actually OPEN the website, manually click refresh, and THEN run the macro - it won't show today's date. So I'm trying to write a bit of VBA code which would do this for me (go into the website, refresh it, and then continue to run the web query). But this doesn't seem to be working.

Does this make any more sense? Sorry!

[snb]

Do you use ?:


private sub Workbooks_open()
thisworkbook.refreshall
End Sub


I just macro recorded it and the VBA runs okay.

Recorded macro code is only useful to make a start to create your own code, not to reuse.

I don't believe you that is runs 'OK". (you told it didn't)
The code you showed is 'horrible' (the sendkeys e.g.) and doesn't match your statement that you use querytables.

Post a sample workbook & or your whole code.

[roxnoxsox]

Hi snb, Many thanks for your help with this. Here is a sample of 2 of the pages.


Dim ie As Object

Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
ie.NAVIGATE "http://www.mangold.se/MangoldOnline/produktinformation/SE0007439450.htm"
ie.Visible = True
While ie.busy
DoEvents
Wend
SendKeys ("{F5}")
ie.Quit
Range("P25").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.mangold.se/MangoldOnline/produktinformation/SE0007439450.htm", _
Destination:=Range("$P$25"))
.Name = "SE0007439450"
.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
[formulas to pick out data required & clears web query]
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
ie.NAVIGATE "http://www.mangold.se/MangoldOnline/produktinformation/SE0004926137.htm"
ie.Visible = True
While ie.busy
DoEvents
Wend
SendKeys ("{F5}")
ie.Quit
Range("P25").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.mangold.se/MangoldOnline/produktinformation/SE0004926137.htm", _
Destination:=Range("$P$25"))
.Name = "SE0004926137"
.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
[formulas to pick out data required & clears web query]

[snb]

Well I assumed you would add new querytables every time: that is much too slow.
The internetexplorer is redundant (and above all creating umpths instances of it is very clumsy coding ;) ).

You can do this to replace your code (and some that you didn't post):


Sub M_snb()
With Sheet1.QueryTables(1)
For j = 1 To 2
.Connection = "URL;http://www.mangold.se/MangoldOnline/produktinformation/" & Choose(j, "SE0007439450", "SE0004926137") & ".htm"
.Refresh False
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(Sheet1.UsedRange.Rows.Count, Sheet1.UsedRange.Columns.Count) = Sheet1.UsedRange.Value
Next
End With
End Sub

[roxnoxsox]

Would you possibly be able to show this in an example excel file? Sorry for being so useless! :(

I tried putting this into excel but it's returning a debug error: 'Compile error: Invalid or unqualified reference'
The section highlighted is: '.Connection ='

[snb]

There should be at least 1 Querytable in sheet1.

Otherwise use the file I posted in #2

[roxnoxsox]

I have tried using this code with your file from #2 but I'm getting the below error message :( :

Run-time error '424':
Object required

The line highlighted when I click debug is:

Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(Sheet1.UsedRange.Rows.Count, Sheet1.UsedRange.Columns.Count) = Sheet1.UsedRange.Value

[snb]

Apparently you didn't notice the lacking of sheet2.
I think your level of expertise is far too restricted for successful/efficient helping.
Maybe someone else is prepared to create this whole project for you.