Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Web query again
Hello,
I looked on MSDN about web queries with VBA. But the code I took from there doesn't work. Dim BaseURL As String BaseURL = "some URL" Const StartDate As Date = #11/2/2004#, EndDate As Date = #4/20/2005# Dim d As Date For d = StartDate To EndDate Dim newSheet As New Excel.Worksheet Set newSheet = Worksheets.Add With newSheet .Name = Replace(CStr(d), "/", ".") Call .Activate End With 'MsgBox ("URL;" & BaseURL) 'Next line gives an error 'Run-time error '5': 'Invalid procedure call or argument With ActiveSheet.QueryTables.Add("URL;" & BaseURL, Range("A1")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With Worksheets(Replace(CStr(d), "/", ".")).Move After:=Worksheets(Worksheets.Count) Next d The line that I get the error is noted above. The line looks correct to me. Can someone help? Thanks, Andrew |
#2
|
|||
|
|||
maybe this?
With ActiveSheet .QueryTables.Add "URL;" & BaseURL, Range("A1") .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With schreef in bericht oups.com... Hello, I looked on MSDN about web queries with VBA. But the code I took from there doesn't work. Dim BaseURL As String BaseURL = "some URL" Const StartDate As Date = #11/2/2004#, EndDate As Date = #4/20/2005# Dim d As Date For d = StartDate To EndDate Dim newSheet As New Excel.Worksheet Set newSheet = Worksheets.Add With newSheet .Name = Replace(CStr(d), "/", ".") Call .Activate End With 'MsgBox ("URL;" & BaseURL) 'Next line gives an error 'Run-time error '5': 'Invalid procedure call or argument With ActiveSheet.QueryTables.Add("URL;" & BaseURL, Range("A1")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With Worksheets(Replace(CStr(d), "/", ".")).Move After:=Worksheets(Worksheets.Count) Next d The line that I get the error is noted above. The line looks correct to me. Can someone help? Thanks, Andrew |
#3
|
|||
|
|||
"moi" wrote in news:d4dftl$7jf$1
@reader13.wxs.nl: maybe this? With ActiveSheet .QueryTables.Add "URL;" & BaseURL, Range("A1") .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With I figured it out. My code needed to be in 'ThisWorkbook'. Once I moved it there, everything worked A-OK. Previously, I had it in 'Sheet1'. Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database Query -- Data Type Mismatch | Excel Discussion (Misc queries) | |||
Excel2000 ODBC query oddity | Excel Discussion (Misc queries) | |||
Query of External Data | Excel Discussion (Misc queries) | |||
Microsoft Query Help | Excel Worksheet Functions | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |