how to create dynamic web query through vb scrip?

[Hemant Oswal]

i want to execute such a web query for which i am setting parameters in that
query.
I want to change parameters through visual basic script progrram and refresh
data.
how to do that programming.

 

[Joel]

Run the macro recorder on one query and explain which parameter you need to
change. The Command text portion of the query is the SQL statements which
are the parameters you are fetching from your data base. The SQL is a string
and you can make substitions like my example below

I have the original recorded macro and the new macro with the part number as
a varialble. I also modified the orginal recordeed macro so each parameter
is on its own line. the macro recorder makes loooooooong strings and it is
simple to break these string into multiple parts.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/26/2009
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), _
Array("=5;")), _
Destination:=Range("A1"))

.CommandText = Array( _
"SELECT `0123 parts received`.ID," & _
"`0123 parts received`.Program," & _
"`0123 parts received`.`Part Number`," & _
Chr(13) & "" & Chr(10) & _
"FROM `C:\TEMP\Part Log`.", _
"`0123 parts received` `0123 parts received`")

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Application.DefaultSheetDirection = xlLTR
ActiveSheet.DisplayRightToLeft = False
End Sub


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/26/2009
'
GetPartNumber = "9876"
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), _
Array("=5;")), _
Destination:=Range("A1"))

.CommandText = Array( _
"SELECT `" & GetPartNumber & " parts received`.ID," & _
"`" & GetPartNumber & " parts received`.Program," & _
"`" & GetPartNumber & " parts received`.`Part Number`," & _
Chr(13) & "" & Chr(10) & _
"FROM `C:\TEMP\Part Log`.", _
"`" & GetPartNumber & " parts received` `" & GetPartNumber & "parts
received`")

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Application.DefaultSheetDirection = xlLTR
ActiveSheet.DisplayRightToLeft = False
End Sub



"Comm

 

[Don Guillett]

Establish your query>record a refresh>introduce your parameters into the
macro. If you can't figure it out send your wb to my address with complete
details and before/after examples.