Thread: VBA saveas + shell

Long story short: I was asked to fix a broken macro, but I don't program w/ VB let alone use excel more than I'm forced to. I can read and understand whats broken with the macro, but I'd rather improve it to prevent the next poor intern from having to fix it.

I wrote up some pseudo code to get my idea about what I'm looking to do across.

## when workbook opens run the following code

save($thisworkbook) as ('C:/path1/path2/' . $thisworkbook['name'] . '.tmp.xls')

#I think the command is actually shell for VBA
#open a shell run python, pass the workbook name as an argument
shell('C:/python25/python.exe $thisworkboo_name.tmp.xls')

#I don't want it to ask if I'm going to save it, that's what the first line is for

close(thisworkbook);
make_excel_go_away();

I'm just looking to save a copy of the workbook when it opens. The copy should have the same name as the workbook that opens with the extension .tmp.xls. I actually want it to be in .xls (97-2003).

Open a shell, run python, and pass the workbook name as an argument.

I've tried searching for subs written by other people to do these tasks, but I just can't seem to get it to work. I hit the beginner tuts and still made no progress.

Edit: there are no other macros in the workbook.
Edit2: code tags

Something like...

Sub Test()
ThisWorkbook.SaveCopyAs ("C:/path1/path2/" & Right(ThisWorkbook.Name, Len(ThisWorkbook.Name - 3)) & ".tmp.xls")
Shell ("C:/python25/python.exe, C:/path1/path2/" & Right(ThisWorkbook.Name, Len(ThisWorkbook.Name - 3)) & ".tmp.xls")
ThisWorkbook.Saved = True
ThisWorkbook.Close
Application.Quit
End Sub

ThisWorkbook.Close
Application.Quit

is equivalent to:

close(thisworkbook);
make_excel_go_away();

(i.e. exactly as requested)
But this will not work beautifully if the user has other workbooks open.
maybe:

If application.workbooks.count < 3 and not application.workbooks("PERSONAL.XLS") is nothing then
application.quit
else
thisworkbook.close
end if

(replacing both lines quoted)

HTH