General discussion

Locked

vba macro works in excel97 winNT but not in excel2002 winXP

By mohammad.ali ·
I'm not sure if this is the right place to ask this BUT please bear with me.
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.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Whats the error ??

by JimHM In reply to vba macro works in excel9 ...

What error are you getting - what is it returning -

Need more info - not going to guess at the problem -

Thanks

Collapse -

vba macro works in excel97 winNT but not in excel2002 winXP

by mohammad.ali In reply to Whats the error ??

sorry.

the actual problem is that it starts excel and open the template and does not execute the macro.

in excel97 it starts excel, opens the template and then runs a macro using the data.xls file as the input file and plots a graph.

MA

Collapse -

vba macro works in excel97 winNT but not in excel2002 winXP

by mohammad.ali In reply to Whats the error ??

sorry.

the actual problem is that it starts excel and open the template and does not execute the macro.

in excel97 it starts excel, opens the template and then runs a macro using the data.xls file as the input file and plots a graph.

MA

Collapse -

Question?

by JimHM In reply to vba macro works in excel9 ...

Do other spreadsheets with Macros run from the command line? - Isn't there a setting in Excel to say if macros can be enable by default? -

Just wondering if its the macro - or an option that isn't set in Excel -

Hum - just thinking ...

Back to Desktop Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums