Excel Web Query

[emboyd]

Ok, I'm trying to access data stored on a web page that's in a correct format. I want to be able to open an Excel Spreadsheet and have it automatically access that web page.

Any tips?

Thanks

Eric M. Boyd
Call Center Supv. III
Washington Mutual Bank

 

[Digga]

Hi Eric,

the easiest way to get the result you're after is to record a macro in the workbook and call it Autpen(). Run your web query, you'll have to repeat it for the second table. This macro will then run everytime you open the workbook.

Press Alt+F11 to open the VBA editor, the code should be sitting in module1. Edit the code so it all fits in the one sub routine.

I've pasted the code generated by the record macro below:

Sub Autpen()
'
' Autpen Macro
' Macro recorded 02/05/2003 by Digga
'

'
With ActiveSheet.QueryTables.Add(Connection:="URL;

http://myServerName",

_
Destination:=Range("A1&quot)
.Name = "myServerName"
.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
' I think this is the table reference, so if I changed it to 4 I'd get the results from a different table
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

End Sub

Hope this helps

Digga

Sharing Knowledge Saves Valuable Time!