Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Pass a command line parameter to Excel (Read 9082 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Pass a command line parameter to Excel
18.12.07 at 21:57:39
Pass a command line parameter to Excel.
The following code can be used to pass a parameter to an Excel worksheet. The implementation of the code below can be found in the attached commandline.xls file.
The purpose for passing a parameter to a workbook is to initialise the workbook using the parameter. I need to pass the database name to the workbook to make it connect to a specific database. The database name can be defined in the /_DB: parameter as shown in the command line below. Note that I named the parameter /_DB because /D already seems to be a parameter to Excel. On my PC Excel hangs when the parameter starts with /D...
Command line:

"C:\Program Files\Microsoft Office\OFFICE11\excel.exe"  /_DB:MYDB myfile.xls  

Option Explicit

Private Sub Workbook_Open()
  Set g_App = New clsApplication
  MsgBox g_App.DatabaseName
End Sub

Option Explicit

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As Long
Declare Function lstrlenA 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)

Global g_App As clsApplication

Option Explicit

Dim m_strDatabaseName As String

Public Property Get DatabaseName() As String
  DatabaseName = m_strDatabaseName
End Property

Private Sub Class_Initialize()
  Dim strCommandLine As String
  strCommandLine = m_getCommandLine
  m_strDatabaseName = m_ExtractDatabaseName(strCommandLine)
End Sub

Private Function m_getCommandLine() As String
  Dim bytBuffer() As Byte
  Dim lngCommandLine As Long
  Dim strCommandLine As String
  Dim lngLen As Long
  Dim lngPos As Long
  lngCommandLine = GetCommandLine()
  If lngCommandLine > 0 Then
    'Command line could be found
    'Now copy it into the byte buffer
    lngLen = lstrlenA(lngCommandLine)
    If lngLen > 0 Then
	ReDim bytBuffer(0 To lngLen - 1)
	CopyMemory bytBuffer(0), ByVal lngCommandLine, lngLen
	strCommandLine = StrConv(bytBuffer, vbUnicode)
	'lngPos = InStr(strCommandLine, Chr(0))
	'If lngPos > 0 Then
	'  strCommandLine = Left(strCommandLine, lngPos - 1)
	'End If
    End If
  End If
  m_getCommandLine = strCommandLine
End Function

Private Function m_ExtractDatabaseName(CommandLine As String) As String
  Dim strRetVal As String
  Dim lngPos As Long
  Dim lngPosSpace As Long
  lngPos = InStr(1, CommandLine, "/_DB:", vbTextCompare)
  lngPosSpace = InStr(lngPos, CommandLine, " ", vbTextCompare)
  strRetVal = Mid(CommandLine, lngPos + 5, lngPosSpace - (lngPos + 5))
  m_ExtractDatabaseName = strRetVal
End Function

Back to top
« Last Edit: 18.12.07 at 21:58:14 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1