passing parameter to a query 1

[anuktac]

HI,
I am really VB challenged. I am hoping some of you can help me.
I am writing a Macro from Excel which simply executes a query with one parameter dynamic:


With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=mark;PWD=newpass;SERVER=test_mark;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT SAC_CODE_LOAD.SAC_CODE, SAC_CODE_LOAD.EIN, SAC_CODE_LOAD.SECTOR" & Chr(13) & "" & Chr(10) & "FROM ""MARK"".SAC_CODE_LOAD SAC_CODE_LOAD" & Chr(13) & "" & Chr(10) & "WHERE (SAC_CODE_LOAD.EIN=?)" _
)
.Name = "Query from mark"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


I am invoking the Excel file from commandline, and the macro is written in Auto_open function.

I want to pass the value of EIN as a parameter to the excel file invocation. Is there a way to do that?

I am invoking the application in java...


Process p = runCommand("\"C:\\PROGRAM FILES\\MICROSOFT OFFICE\\OFFICE\\excel\" \"" + filename + "\"");


-Thanks
Anukta

 

[SkipVought]

Hi,

Assuming that EIN is NUMERIC

Sub AddQT(YourParameterValue As String)
    With ActiveSheet.QueryTables.Add _
        ( _
            Connection:="ODBC;DRIVER={Microsoft ODBC for Oracle};UID=mark;PWD=newpass;SERVER=test_mark;" _
            , Destination:=Range("A1") _
        )
        .CommandText = "SELECT A.SAC_CODE, A.EIN, A.SECTOR " & _
            "FROM MARK.SAC_CODE_LOAD A " & _
            "WHERE (A.EIN=" & YourParameterValue & ")"
        .Name = "Query from mark"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

If not string then change this line...

"WHERE (A.EIN='" & YourParameterValue & "')"

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[rarogersonkf]

Could you not use a parm table to select the value of EIN?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=mark;PWD=newpass;SERVER=test_mark;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT SAC_CODE_LOAD.SAC_CODE, SAC_CODE_LOAD.EIN, SAC_CODE_LOAD.SECTOR" & Chr(13) & "" & Chr(10) & "FROM ""MARK"".SAC_CODE_LOAD SAC_CODE_LOAD" & Chr(13) & "" & Chr(10) & "WHERE (SAC_CODE_LOAD.EIN=(SELECT EIN_PARM.EIN FROM EIN_PARM_TABLE EIN_PARM))" _
)
.Name = "Query from mark"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Rob

 

[anuktac]

Thanks Rob
This gives me an idea. My problem was really how to pass the parameter from commandline and make excel understand that this value was a parameter and how to evaluate it.

I can update the value of the ein from the wrapper java code into a table, and as you suggested, pick up the latest value from this table.
Thanks
Anukta