Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2932
  • Last Modified:

Get Command line parameters into Excel

I have a VBA project (Excel 2007) which is called from another program.
When the Parent Program calls Excel it sends login details (Username and Password).
How can I get these into VBA?

The Parent Program sends
Excel.exe /embedded /r "C:\xxx\Programs\abcd.xlsm" username password

I can make the Parent Program call a batch file to open the Excel file on one line but how do I get it to insert the usernam and password?
0
p-plater
Asked:
p-plater
  • 8
  • 4
1 Solution
 
dlmilleCommented:
I believe this is what you're looking for, which can be adapted to your VBA Project.  It shows how to write code on the _Open of the VBA Project to then pull the command line parameters.

http://www.vbforums.com/showthread.php?p=2234990

Dave

0
 
dlmilleCommented:
Here it is implemented and tested...

First the commandline batch file:
 
"C:\Program Files\Microsoft Office\Office12\excel.exe" "c:\users\%USERNAME%\documents\excelcommandliner1.xlsm" /eUSERNAME/PASSWORD"

Open in new window

Note, you'll need to specify your own path to excel, and where your VBA projet is - for this test, I put it in my documents folder

Also the e/USERNAME/PASSWORD is replaced with /eYOURUSER/YOURPASSWD

The macro assumes only two parameters, so picks off username and password using the SPLIT command (as suggested in the tip link I posted).
Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim CmdLine As String
Dim userName As String
Dim userPass As String
Dim v() As String

    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    MsgBox CmdLine
    v = Split(CmdLine, "/")

    userName = Right(v(1), Len(v(1)) - 1)
    userPass = Left(v(2), Len(v(2)) - 1)
    MsgBox "Username: " & userName & ", Password: " & userPass
End Sub

Open in new window

Note - I included no error checking (re: you'll get errors if you open the xlsm file without parameters - just need to check for that on the _OPEN command, or put an On Error Resume next in - when you're debugging, you might want some code like this).

And the function which parses from the command line (I placed in Module 1):
Option Base 0
Option Explicit

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)

Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
   
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

Open in new window

Enjoy!

Dave  
ExcelCommandLiner1.xlsm
0
 
p-platerAuthor Commented:
Thanks

That works except for two things.
1. If the Computer has Excel 2010 it won't start.
2. How can I stop the Command Prompt window from appearing?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
Have you tried running this from your PARENT PROGRAM?  This was the requirement per your original question.  The VBA code is independent of Excel version.  I'll look in to the command prompt from batch file, but was only putting that out there as an example.

Dave
0
 
p-platerAuthor Commented:
Yes I am running it from the Parent Program.

The Problem is when the workstation has excel 2010 and the batch file says to run excel from the Office12 (Office07) folder when it is in the office14 folder

Ron
0
 
dlmilleCommented:
Ok - can you change the batch file to run Excel from the correct folder?

Dave
0
 
dlmilleCommented:
So - for different versions of Excel, the starting point is in different locations...

You could just have it call each version, as follows:

@ECHO OFF
"C:\Program Files\Microsoft Office\Office14\excel.exe" "c:\users\%USERNAME%\documents\excelcommandliner1.xlsm" /eUSERNAME/PASSWORD"

"C:\Program Files\Microsoft Office\Office12\excel.exe" "c:\users\%USERNAME%\documents\excelcommandliner1.xlsm" /eUSERNAME/PASSWORD"

As long as the user doesn't have multiple versions, it should suffice.

Still checking on how to tell which version of Excel the user has...

Dave
0
 
dlmilleCommented:
Here's a much better solution - using VB Script.  You don't get the black CMD screen, and it finds the right instance of Excel on the machine running Excel.

Create a VBScript file like this:
Dim xlApp
Dim xlWkb
Const cEXIT = False

Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("c:\users\daddy\documents\ExcelFromVBS_UserPass.xlsm")
xlWkb.RunAutoMacros 1 'xlautoopen
xlApp.Run "ExcelFromVBS_UserPass.xlsm!UserPass", Cstr("Username"), Cstr("Password")

'now either make Excel visible or close it
If cEXIT Then
xlWkb.Close 0
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
Else
xlApp.Visible = True
End If

Open in new window


And in your Excel File, there's a module like this:

   
Public UserID As String
Public UserPasswd As String
Sub UserPass(U_ID As String, U_Pass As String)
    MsgBox "UserID: " & U_ID & ", UserPassword: " & U_Pass
    UserID = U_ID
    UserPasswd = U_Pass
End Sub

Open in new window


And you're good to go.

Cheers,

Dave
ExcelFromVBS-UserPass.xlsm
testAutoUserPassr1.vbs
0
 
dlmilleCommented:
How's it going - did my solution work for you?  My latest version was run in VB Script which should be availabe on your machine.

Please advise any issues or close the question.

Thanks!

Dave
0
 
p-platerAuthor Commented:
Just one Question.
When I close off the Userform the screen flashes. - Is this normal or can it be avoided?
(It looks like its the Excel window becoming visible for a fraction of a second)
0
 
dlmilleCommented:
I don't know.  What changes have you made?  I had a msgbox advising user id and password last time I submitted, with the Excel window begin visible on success.

I get no flickering screen.

Dave
0
 
p-platerAuthor Commented:
OK
I see what was happening now - After I closed the application the vbScript file was making Excel visible for a fraction of a second then closing it.

I just removed the xlApp.Visible = True and it stopped the flicker

Thanks for your help.
10/10
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now