Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to create dynamic web query through vb scrip?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to create dynamic web query through vb scrip?
Establish your queryrecord a refreshintroduce 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Hemant Oswal" <Hemant wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To creat scrip ledger | Excel Discussion (Misc queries) | |||
in excel , arrange scrip names alphabatically | Excel Discussion (Misc queries) | |||
VB and Excel Scrip | Excel Programming | |||
Dynamic Web Query from VBA | Excel Programming | |||
Dynamic Web Query | Excel Programming |