[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get Command line parameters into Excel

Posted on 2011-02-24
12
Medium Priority
?
2,662 Views
Last Modified: 2012-05-11
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
Comment
Question by:p-plater
  • 8
  • 4
12 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34974409
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34974656
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
 

Author Comment

by:p-plater
ID: 34975632
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 42

Expert Comment

by:dlmille
ID: 34976037
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
 

Author Comment

by:p-plater
ID: 34976094
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34976147
Ok - can you change the batch file to run Excel from the correct folder?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34976384
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
 
LVL 42

Accepted Solution

by:
dlmille earned 800 total points
ID: 34976509
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35065780
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
 

Author Comment

by:p-plater
ID: 35091328
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35096956
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
 

Author Comment

by:p-plater
ID: 35153734
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question