ExcelBanter - how to create dynamic web query through vb scrip?

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(Arra y( _
"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(Arra y( _
"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

"Hemant Oswal" wrote:

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.