Solved

Get Command line parameters into Excel

Posted on 2011-02-24
12
1,956 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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

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

Dave
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 200 total points
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now