Spreadsheet autorun macro, parameter?

[Phil Hibbs]

Is it possible to launch an Excel spreadsheet with a parameter that
can be picked up by a Workbook_Open macro? What I want is for the
macro to open and process a text file specified, then SaveAs and
close. If it is opened without a parameter, I don't want it to process
a file. I could make this conditional behaviour dependent on the
presence of data in the spreadsheet that has been read from the file,
but I need to get the file name somehow, and I need a way of opening
and updating the macros without running them. As a last resort I could
write the file name into another text file and pick it up from there.

Are there any other gotchas with this kind of arrangement that I
should watch out for?

Phil Hibbs.

 

[Don Guillett]

Without looking at this very carefully, explore the idea of using the older
autoopen() and tie to the workbook_open if desired.

 

[Patrick Molloy]

are you able to use VBS files? VBScript

here's an example that passes the file name directly to a sub
- in this case Mysub is defined as
Sub Mysub(sname As String)
MsgBox sname
End Sub

This is tehVBScript code:

sExcelPath = "c:\temp\test.xls"
Set XLapp = CreateObject("Excel.Application") ' New Excel.Application
XLapp.visible = true
' open the workbook
Set wbMain = XLApp.Workbooks.Open(sExcelPath)
with XLApp
if wscript.arguments.count =1 Then
.Run "MySub", wscript.arguments(0)
end if
end with

set wbMain = Nothing

Set XLApp = Nothing


you could drop the textfile onto the vbscript file and then the script could
load the file name in to a specified range and call whatever

 

[Phil Hibbs]

I've got it working now, using a file to drive the process:

Private Sub Workbook_Open()
AutoProcess
End Sub

' ... and in Module1 ...
Dim FileName As String

Public Function AutoProcess()
Dim FileNum As Integer
Dim FSO As FileSystemObject
On Error Resume Next
Set FSO = New FileSystemObject
FileNum = FreeFile
FileName = ""
Open "autoprocess.txt" For Input As #FileNum
Line Input #FileNum, FileName
Close #FileNum
If FileName <> "" Then
Analyse
SaveAs
Call FSO.DeleteFile("autoprocess.txt")
Application.Quit
End If
End Function

Analyse and SaveAs are my pre-existing functions, I just amended
Analyse to check for an existing value in the global variable FileName
and open that instead of presenting the user with a FileOpen dialog. I
had to set macro security to 1 in the registry first, I do this with a
Perl script and then reset it back to 2 after again.

Phil Hibbs.