Solved

Get Command line parameters into Excel

Posted on 2011-02-24
12
2,170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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