Hi all,

I'm looking for the code necessary to drag current, up to date stock
prices from the Australian share market into an Excel spreadsheet.

I was pointed to one free file download but it had a password on the
code so I couldn't alter it to my needs.

I tried using a web query feeding off of MSN Money central but the data
from this feed seems to have frozen as of last Friday.

Can anyone help me out?

Any help would be very appreciated.

Thanks guys,

Kim

Kim, Since no one with more smarts than I have about this has jumped in, I'll
offer up something that you can perhaps use or alter to suit your needs.
This is from a setup used to update some team stats via a web query. I truly
don't understand all I know about it, but it works.

The setup: initially I created the web query and saved it (the .iqy file) in
the same folder with the workbook that uses it. You'll see in the code where
when things go wrong, it looks in the same folder to try to find it. The
name of the .iqy file as I named it is used, you'll need to change that.
Code goes to the page used to retrieve the data, executes the query and
failing that, sets up to use the query. The name associated with the query
area on the sheet is removed to keep from creating zillions of entries in the
workbook's Names collection.

After the data is retrieved, the code goes on (not posted here) to grab what
it needs from the worksheet and use it elsewhere. If you think I can help
more, feel free to contact me at HelpFrom at jlathamsite.com - but I don't
use Web Queries much at all, and know very little more than what is shown
here in the code. The variables used in the code, such as
ImportedStatsSheet, PathToQuery (both are type String) and anyWebQuery (type
Object) were defined earlier - ahead of this code.

Sheets(ImportedStatsSheet).Select

Cells.Select
Selection.ClearContents
Range("A1").Select
On Error Resume Next
Selection.QueryTable.Refresh BackgroundQuery:=False
If Err < 0 Then
'must rebuild query and then requery
Err.Clear
On Error GoTo 0
PathToQuery = ThisWorkbook.FullName
For LC = Len(PathToQuery) To 1 Step -1
If Mid$(PathToQuery, LC, 1) = "\" Then
PathToQuery = Left(PathToQuery, LC) & "StarFireStats.iqy"
Exit For
End If
Next
'define this as to the path to file StarFireStats.iqy
QueryConnectString = "FINDER;" & PathToQuery
For Each anyWebQuery In ActiveSheet.QueryTables
anyWebQuery.Delete
Next
With ActiveSheet.QueryTables.Add(Connection:=QueryConne ctString,
Destination:=Range("A1"))
.Name = "StarFireStats"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If
On Error GoTo 0
'we should have data from the website now
'redefine the lookup table based on imported data


"Kim" wrote:

Hi all,

I'm looking for the code necessary to drag current, up to date stock
prices from the Australian share market into an Excel spreadsheet.

I was pointed to one free file download but it had a password on the
code so I couldn't alter it to my needs.

I tried using a web query feeding off of MSN Money central but the data
from this feed seems to have frozen as of last Friday.

Can anyone help me out?

Any help would be very appreciated.

Thanks guys,

Kim