Solved

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

Posted on 2009-04-13
24
1,099 Views
Last Modified: 2016-02-11
Hello Experts, Ive found many related questions, but none seems to fit exactly my need and as I am completely new to coding and VB its hard for me to think of how to adapt the answers. So please bear with me&

Each day a large number of stocks are selected (different stocks get selected each day), and (using a software called Stata) I have managed to automate the creation of a corresponding number of .iqy files, named stockticker.iqy. For each stock, I need to run the relevant web query in Excel 2003 and save the resulting Excel file. How can I automate this?

Thanks a lot in advance for any help and direction!
Barbara
0
Comment
Question by:Lilibet
  • 12
  • 10
  • +1
24 Comments
 
LVL 6

Accepted Solution

by:
kalkul earned 500 total points
ID: 24135768
Hi,

You can use this two procedures. Copy them to new module in VBA editor. Run macro with Alt F8.

OpenIQY opens specific IQY filename, rune query and save .xls file in same path with same name as .iqy file.

OpenAllIQY opens all iqy files in path (in this case C:\Documents and Settings\kalkul\My Documents\). Please change this appropriate to your needs.

I hope this will help you.

Regards, Kalkul
Sub OpenAllIQY()

Dim FName As String, Path As String

  Path = "C:\Documents and Settings\kalkul\My Documents\"

  FName = Dir(Path & "*.iqy")

  While Len(FName) > 0

    OpenIQY Path & FName

    FName = Dir

  Wend

End Sub
 

Sub OpenIQY(IQYName)

Dim WBN As Workbook
 

  Set WBN = Application.Workbooks.Add

  With WBN.Worksheets(1).QueryTables.Add(Connection:= _

    "FINDER;" & IQYName, Destination:=WBN.Worksheets(1).Range("A1"))

    .Name = "IQY"

    .FieldNames = True

    .RowNumbers = False

    .FillAdjacentFormulas = False

    .PreserveFormatting = False

    .RefreshOnFileOpen = False

    .BackgroundQuery = True

    .RefreshStyle = xlInsertDeleteCells

    .SavePassword = False

    .SaveData = True

    .AdjustColumnWidth = True

    .RefreshPeriod = 0

    .WebSelectionType = xlAllTables

    .WebFormatting = xlWebFormattingAll

    .WebPreFormattedTextToColumns = False

    .WebConsecutiveDelimitersAsOne = True

    .WebSingleBlockTextImport = False

    .WebDisableDateRecognition = False

    .WebDisableRedirections = True

    .Refresh BackgroundQuery:=False

  End With

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

End Sub

Open in new window

1
 

Author Comment

by:Lilibet
ID: 24136441
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
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24136527
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
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24136543
Hi,

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

Path = thisworkbook.Path & "\"

Regards, Kalkul
0
 

Author Comment

by:Lilibet
ID: 24136575
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
0
 

Author Comment

by:Lilibet
ID: 24136642
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
0
 

Author Comment

by:Lilibet
ID: 24136811
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
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24136816
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
0
 

Author Comment

by:Lilibet
ID: 24136837
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

0
 
LVL 6

Expert Comment

by:kalkul
ID: 24136976
Hi,

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

instead of
        .WebFormatting = xlWebFormattingAll

regards, Kalkul
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24137073
Hi

This is answer::

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

Regards, Kalkul
0
 

Author Comment

by:Lilibet
ID: 24137380
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Expert Comment

by:kalkul
ID: 24137573
Hi

What do you want to know - Excel programing?

Regards, Kalkul
0
 

Author Comment

by:Lilibet
ID: 24137589
The code you created -- is it Excel programming or Visual Basic or are the two the same thing? :)
All the best,
Barbara
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24137701
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

0
 

Author Comment

by:Lilibet
ID: 24137824
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
0
 
LVL 6

Expert Comment

by:kalkul
ID: 24138048
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
0
 

Author Comment

by:Lilibet
ID: 24138175
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


0
 
LVL 6

Expert Comment

by:kalkul
ID: 24141130
Hi,

Very strange,  don't have answer for you.

Regards, Kalkul
0
 

Author Comment

by:Lilibet
ID: 24141712
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?
0
 

Author Comment

by:Lilibet
ID: 24143239
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
0
 

Author Comment

by:Lilibet
ID: 24174719
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
0
 

Expert Comment

by:gippslandwater
ID: 25724774
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
0
 

Expert Comment

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

Any suggestions?
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now