General discussion
Thread display: Collapse - |
All Comments
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
vba macro works in excel97 winNT but not in excel2002 winXP
I have the following macro code that works fine in excel97 winNT but not in excel2002 winXP:
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
Loop
Call mppt_macro(file_name)
Call close_book1
End Sub
This macro should read the commandline and return the file name to be used for plotting a graph.
The command line is as follows:
excel /r c:\temp\template.xlt /e/c:\temp\data.xls
the filename returned should be data.xls
please help and thanx in advance for your cooperation and patience.