Automating the running of several web queries (in separate .iqy files) that change each day and saving the corresponding Excel file

Dear Kalkul, this looks awesome, thank you!

I followed your instructions, have saved the workbook, exited the Editor, and tried to run the macro in the workbook using Alt F8. I get a Macro window, with Macroname OpenAllIQY, I click on the Run button, the window closes, but nothing much happens... ie I don't see the xls files it's meant to create.
What am I doing wrong?!?

Thanks so much for your patient help to this newbie!!
Barbara

Hi,

Did you change Path variable (Path = "C:\Documents and Settings\kalkul\My Documents\" )?

I don't know where your .iqy files are. You must write your path to files.

~Regards, Kalkul

Hi,

If you put excel file with this macro in same folder then you can use

Path = thisworkbook.Path & "\"

Regards, Kalkul

Yes I had but quickly realised I had left out the last "\" and was just going to write you!! Sorry :)
Now it works, that's fantastic, but just one thing left that puzzles me:
if I run an iqy file manually, I rightly get only the relevant table I want (see CECO_manually.xls), but the automation process seems to neglect the place on the webpage that I specified and gets me the whole page... ie it ignores that in my say CECO.iqy I have

http://finance.yahoo.com/q/os?s=CECO
Selection=12

Any clues?

Thanks again, you're a star!!
Deserve the max points :)
b
CECO.xls
CECO-manually.xls

One way round it would be to add code to OpenAllIQY that deletes the first 28 rows in the Excel spreadsheet? (It seems to always be the first 28).
Is this easy to do?
Maybe not super elegant but would do the trick for me...
Thanks!
Barbara

Hi Kalkul, looking up some VB commands and code on the web I found how to make this work!
(Not sure this is The best way, but it works):

I replaced your line 29:
 .WebSelectionType = xlAllTables

with these two lines:
.WebSelectionType = Excel.XlWebSelectionType.xlSpecifiedTables
.WebTables = "12"

Hurray!

One last thing: is there a way in the above code (I guess line 38) to force Excel to overwrite old xls files directly?

Thanks!
Barbara

Hi,
please send me iqy file and I'll check.

Anyway deleting first 28 rows are easy

before WBN.close put

Rows("1:27").Delete Shift:=xlUp

Regards, Kalkul

Yes I had tried to send you the iqy file too but was not a format that was allowed. I copy it here:


WEB
1
http://finance.yahoo.com/q/os?s=CECO

Selection=12
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


But you see my (random!) fix above works!
It looks like coding a specific web query directly in the routine...

b

Hi,

Put in
        .WebFormatting = xlWebFormattingNone
        .WebTables = "12"

instead of
        .WebFormatting = xlWebFormattingAll

regards, Kalkul

Hi

This is answer::

  Application.DisplayAlerts = False
  WBN.Close SaveChanges:=True, Filename:=Replace(IQYName, ".iqy", "", , , vbTextCompare)
  Application.DisplayAlerts = True

Regards, Kalkul

Kalkul, now this is PERFECT!!
Thank you so much for your help.
Amazing stuff... would you by the way have a suggestion on where I could get started to learn more on this (web, book you could recommend)?
Have a great day!
Barbara

Hi

What do you want to know - Excel programing?

Regards, Kalkul

The code you created -- is it Excel programming or Visual Basic or are the two the same thing? :)
All the best,
Barbara

Hi,

VBA - which is used in Excel is similar it's Visual Basic for Applications.

Search: recording macros in excel in google and follow links.

Regards, Kalkul

Thanks, looks a very useful topic to study.

In the meantime, the macro has crasched twice :-(

First, it said it could not access the relevant webpage (thought the iqy file would run manually), so i deleted that igy and continued.

after some more time, it stopped with this message:

Runtime error '1004':
the file could not be accessed. try one of the following

<4 things to make sure, but none looks like it applies here>

And if i click on Debug, it highlights in yellow this line:
 .Refresh BackgroundQuery:=False

If I launch that iqy manually, it works...

Should I try deleteing/changing that line? but it has worked for the other iqys...
Maybe 252 iqy's are too many to automate?

ooooh :-(
Barbara

Hi,

Sometimes I find this could be problem that you try to get a lot of data from webpages. maybe they track this. This line actualy get data from web page.

You can put some wait function in

Application.Wait TimeSerial( Hour(Now()), Minute(Now()), Second(Now()) + 1)

Before wend.

This will slow down (1 sec after each query) you but it might work.

regards, kalkul

The whole thing had just got so stuck that even Task manager would not allow me to close Excel and I had to restart the computer!
Thank you so much for the added line, but it doesn't seem to work.
It seems that it just does not like some stock, eg I left only 2 iqy files in the folder and this one:

WEB
1
http://finance.yahoo.com/q/os?s=BEC

Selection=12
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


gives rise to the error message i told you about.
This is so strange as the BEC.iqy works fine manually, and other similar files work fine in automation...

How come computers are not consistent?!?!

Best, Barbara

Hi,

Very strange,  don't have answer for you.

Regards, Kalkul

Thanks very much for all your help Kalkul, greatly appreciated.

This is indeed very strange -- i tried on the computer at work and instead of crashing on stock 26 (BEC) it crashed after 40 or so, on another one, which again is ok when launched manually...
Mysteries of computers...
Anyway, thanks to you at least i got it automated to a certain extent...

All the best,
Barbara

PS do you think it's worth asking a related question, and if so, under what fields would you suggest?

Hi Kakul,

To wrap this up: I think you are right in surmising that they might be tracking you when trying to get a lot of data from webpages. I tried again at home and it ran for 40 stocks rather than 20 before crashing, but then even the manual iqy (or even doing it within Excel) would no longer work!
After a rest of an hour or so, the manual ones work again.

This would explain the erratic behaviour of the code.

Thanks again,
Barbara

Hello Kalkul,

I have searched the web and found what the problem is!
It's because of a limit in terms of temp files that can be saved. I've also found an outline of the solution. But now I need help on how to implement it :)
I'll ask a separate question, so you (if you are interested of course!) or others can get fresh points.

All the best,
Barbara

Hi this is AWESOME and just what I need too... Is it possible to run this as an external VB Script. What do I need to do..  Thanks

When running the VB Script, I get the Error 400 for the Line FName = Dir
A blank Excel file then opens

Any suggestions?