Passing UserForm Text from One Excel Application to Another

[jochryem]

Hello,

I'm trying to find the VBA code used to pass the value in a UserForm textbox to a separate excel application. I need to do this as I have a master workbook that I use to enter in a User ID and Password for an external database then I call multiple workbooks in separate excel applications that run macros asynchronously. When these workbooks open, I need to update the DNS entry so that it doesn't call for the User ID and Password pair again.

I'm opening the separate workbooks as follows:


Private Sub LaunchReport1()

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Open Filename:= _
"C:\TEST FOLDER\MasterRefreshTest1.xlsm"
xlApp.Visible = True

xlApp.Run "Launcher"
Set xlApp = Nothing
End Sub

Private Sub LaunchReport2()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Open Filename:= _
"C:\TEST FOLDER\MasterRefreshTest2.xlsm"
xlApp.Visible = True

xlApp.Run "Launcher"
Set xlApp = Nothing
End Sub

[Jan Karel Pieterse]

You can pass (string) arguments to the called routine when you use Application.run:

Application.Run "'" & TheOtherWorkBook.Name & "'!MacroNameHere", "parm1", "parm2"

[jochryem]

Hi Jan,

I added the following code to the target workbook but it still gives me a debug error. I believe it's because the source and target workbooks are in separate excel application instances?

Application.Run "'Master Report Submission Screen.xlsm'!Update_Credentials"

Is this possible?

[Jan Karel Pieterse]

You must use application.run in the main workbook to pass the credentials from main to target

[jochryem]

I'm not sure I follow. In the main workbook, I call a Userform (let's call it Credentials). It has credentials.textbox1 (UserID) and credentials.textbox2 (Password). What macro would I use in the target workbook that I would call from the main to grab these values?

[Jan Karel Pieterse]

I understood you already call a macro in your target book from your your main workbook. If not, now is the time :-)

With Credentials
Application.Run "'Otherbook.xlsm'!SomeMacro", .Textbox1, .TextBox2
End With
And in the other workbook you have:

Sub SomeMacro(sUserName As String, sPassword as String)
'Now you can use the credentials
End Sub

[jochryem]

Still can't get this to function properly. I'm attaching a sample of what I want to happen. When you run the code on workbook A, it should open workbook B as a separate Excel application (you'll need to change the source path to your own first save location first). When the modules in workbook B are called, the UserId and Password should individually appear in the message boxes that pop up based on the credentials entered in workbook A WITHOUT saving them to cells in workbook B (need these to be passed virtually).

[Jan Karel Pieterse]

See attached...

[snb]

Or

[jochryem]

This worked great! Thank you :)