Problem in passingmultiple parameters in Excel Macro

[ashutosh152]

Problem in running dtexec command with multiple parameters in macro:-

------code in macro-----

Dim Rsvr As String
Dim Runm As String

Rsvr = Sheets("update_xml").Range("C6")
Runm = Sheets("update_xml").Range("C7")

' MsgBox Rsvr ----value= Hi
' MsgBox Runm ----value= Hello

Call Shell("DTEXEC /f C:\Macro\xml_Table_updated_xml.dtsx /set \package.variables[User::InputRsvr].Value;&Rsvr /set \package.variables[User::InputRunm].Value;&Runm", vbNormalFocus)

when i run this macro, &Rsvr and &Runm in dtexec command is not assigned the values, i wanted these variables to be replaced by the "Hi" and "Hello" respectively.
I cannot hard code the values of the variables as they dynamic.

Please guide me how can provide parameters in call shell.

 

[shg]

Welcome to the forum.

Maybe ...

    Call Shell("DTEXEC /f C:\Macro\xml_Table_updated_xml.dtsx /set \package.variables[User::Input" & _
               Rsvr & "].Value;&" & _
               Rsvr & " /set \package.variables[User::Input" & _
               Runm & "].Value;&" & _
               Runm, _
               vbNormalFocus)

 

[shg]

In practice, I would assign the built-up shell string to a string variable and then look at it. Then you can just shell the string variable.

Lots easier to debug.

 

[ashutosh152]

Thanks shg4421 for quick response.

I rather used the following code and it solved my purpose:-

Call Shell("DTEXEC /f C:\Macro\xml_Table_updated_xml.dtsx /set \package.variables[User::InputRsvr].Value;" & Rsvr & " /set \package.variables[User::InputRunm].Value;" & Runm, vbNormalFocus)

Anyways thanks for replying.

 

[S M C]

X posted here http://www.ozgrid.com/forum/showthread.php?t=149547