General discussion


excel97 macro not working in excel2002

By mohammad.ali ·
I have the following in excel97 winNT & it works fine BUT in excel2002 winXP does not. Any help will be much appreciated.

Private Declare Function GetCommandLine Lib "kernel32" _
Alias "GetCommandLineA" () As Long

Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenA" _
(ByVal lpString As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)
Global file_name As String

Public Function CommandEx() As String
Dim lpCmdLine As Long
Dim lLen As Long
'Get pointer to command line ansi string:
lpCmdLine = GetCommandLine()
If lpCmdLine Then
'Get length of ansi string:
lLen = lstrlen(lpCmdLine)
'Allocate space for copy:
CommandEx = String$(lLen, vbNullChar)
'Copy the string into our local String:
CopyMemory ByVal StrPtr(CommandEx), ByVal lpCmdLine, lLen
'Convert to Unicode and trim:
CommandEx = Left$(StrConv(CommandEx, vbUnicode), lLen)
End If
End Function

Sub auto_open()
Dim CmdLine As String 'command-line string
Dim Args() As String 'array for storing the parameters
Dim ArgCount As Integer 'number of parameters
Dim Pos1 As Integer, Pos2 As Integer
CmdLine = CommandEx() 'get the cmd-line string
On Error Resume Next 'for the wksht-function "Search"
Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1 'search "/e"
Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st param
Do While Err = 0
Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)
ArgCount = ArgCount + 1
ReDim Preserve Args(ArgCount)
Args(ArgCount) = Mid(CmdLine, Pos1)
file_name = Args(ArgCount)
Pos1 = Pos2 + 1
Call mppt_macro(file_name)
Call close_book1
End Sub

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by mohammad.ali In reply to excel97 macro not working ...

I've been debugging the above and subsequently found that commandline is not read properly and the switches /r and /e are not interpreted correctly under excel2002.
The foolowing syntax is used to run this:

"C:\Program Files\Microsoft Office\Office\excel.exe" /r c:\ma\clearcase\mppt_graph.xlt /e/ c:\ma\clearcase\mppt_data.xls

the lLen variable only reads in upto 80 odd characters but the command line is much longer.
Please help asap as I can't see why this works in winNT but not in winXP.

Related Discussions

Related Forums