Open, save and then close a webpage automatically

[MrMajik]

I am trying to open this website and then save it to a file on a daily basis:

http://moneycentral.msn.com/investo...2Z04L35Z&Name=StrongStocks In Dip&Tickers=100

The link is too long for the Excel query. I think VB6 is cabable of doing it but I am out of ideas on things to try to get it to work.

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.

 

[taupirho]

Hi, a web query is one way of doing what you want.

Create a text file containing the following data and save it as c:\test.iqy

Note the url should all be on one line sandwiched between the text WEB and the digit 1 (which are on lines of their own)

WEB

http://moneycentral.msn.com/investo...2Z04L35Z&Name=StrongStocks In Dip&Tickers=100

1


Now create the following macro below. Running it should paste the contents of the url starting at cell A1



Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:="FINDER;C:\test.iqy", _
Destination:=Range("A1"))
.Name = "test"
.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
.Refresh BackgroundQuery:=False
End With
End Sub

 

[taupirho]

Sorry, should have said that my posting is an EXCEL based solution to the problem - NOT VB6

 

[MrMajik]

taupirho,

Thank you for the reply.

I did not know you could call a macro this way. Interesting.

I did exactly what you described and got this

Run-time error 1004:
Microsoft Excel coul not open or read this query file. Either the file has been damaged or the file format is not valid.

The file name is test.iqy and the path to get to it is c:\test.iqy It is not read-only or is it opened while I run the query in Excel. Also, when I click on the test.iqy file it opens Excel but generates the same error. I also verified my macro security level in Excel is set to Low.

If this will work with that long url in Excel this will solve this problem. Any ideas what is causing the error?

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.

 

[taupirho]

Make sure the .iqy file is just a plain text file - created with notepad, NOT with word or excel?

 

[taupirho]

Also when you open the file in notepad ensure that there are no spaces at the end of each of the 3 lines

 

[MrMajik]

taupirho,

I got your example to work by changing one thing:
Your example led me to look at a query that comes with Excel. You had the 1 at the end of the query whereas it needs to be on the second line (see below). Now the query runs but it pulls everything into Excel except the stock data I need.

WEB
1

http://moneycentral.msn.com/investo...stor/finder/customstocks.asp&&DI=7290&PI=7288

Can Excel pull in the stock data?

This leads me back to my original question...How do I get VB6 to open this website and then save it?

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.

 

[taupirho]

When I click on your link I dont get any stocks, just a screen headed Create Your Own Stock Search Screen. I then have to fill in some criteria and hit the Search button at the bottom of the screen to get any stocks up.

What is the exact URL of the screen that you're using to display your stocks? This is what needs to go in the web query file

 

[MrMajik]

Oops...the original link was not the right one...This one works.

http://moneycentral.msn.com/investo...stor/finder/customstocks.asp&&DI=7290&PI=7288

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.

 

[taupirho]

Ditto, same answer as my last post to the link you just posted.

 

[macleod1021]

If you watch closely, you'll notice that the web page forwards back to the starting query page. The reason is because the web page is using some type of session ID or cookie that keeps up with the query. It's not valid when you click it, so it takes you back to the starting page.

The best solution for this would be to find out if the website offers some type of downloadable information in xml format. Then you can parse the XML file into whatever format you need.

 

[taupirho]

Ok try this , navigate to the page where you see the list of stocks you're interested in. Now click on IE's history button and get to the same link that you're looking at. Right click and take Properties. This should result in a pop-up appearing with the interner address of the the URL highlighted. This should be the real URL of the stock list page you're looking at. This is the URL you need in your web query file.

 

[MrMajik]

You are right. The web query is different! This is what I get:

http://moneycentral.msn.com/investo...F181ZF3Z05L0.9F231MZF136Z04L10ZF102Z04L35Z&Na

However, it will not load the stock symbols in the large empty window. Are you seeing these stock symbols? For today there are three of them.

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.

 

[taupirho]

No,

when I click on that link I get a screen headed
Find stocks and funds like a pro

 

[MrMajik]

I just remembered...you have to install some software from the website. When it asks you, "Do you see the chart" you are at the install page. I don't see that page any longer as I installed the software.

Thank you,

MrMajik

There are 10 types of people in the world:
Those that understand binary and those that don't.