How to run a macro when file opens???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shane R. Pouch - 21 Jun 2006 01:49 GMT

I am trying to figure out the Visual Basic language to include in an Excel
file so when it opens the macro will automatically run each time the file is
opened.

Specifically, I want the macro to open the File, Save As... command so that
I have to save the file as a new file before I start doing anything else.

Any help will be highly appreciated!
Thanks!

Reply to this Message

Homer J Simpson - 21 Jun 2006 03:01 GMT

> I am trying to figure out the Visual Basic language to include in an Excel
> file so when it opens the macro will automatically run each time the file
[quoted text clipped - 3 lines]
> that I have to save the file as a new file before I start doing anything
> else.

Record yourself saving the file then edit that macro.

Reply to this Message

Shane R. Pouch - 21 Jun 2006 03:44 GMT

>> I am trying to figure out the Visual Basic language to include in an
>> Excel file so when it opens the macro will automatically run each time
[quoted text clipped - 5 lines]
>
> Record yourself saving the file then edit that macro.

I understand that. But, I can't figure out how to "assign" this macro to
the file so that it will run when the file opens. I am not experienced
enough with VB to figure this part out.

Reply to this Message

Homer J Simpson - 21 Jun 2006 05:32 GMT

> I understand that. But, I can't figure out how to "assign" this macro to
> the file so that it will run when the file opens. I am not experienced
> enough with VB to figure this part out.

You MUST use the name Auto_Open

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 20/06/2006 by Me
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\<My Name>\My Documents\Test01.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Reply to this Message

Shane R. Pouch - 21 Jun 2006 20:00 GMT

>> I understand that. But, I can't figure out how to "assign" this macro to
>> the file so that it will run when the file opens. I am not experienced
[quoted text clipped - 12 lines]
> ReadOnlyRecommended:=False, CreateBackup:=False
> End Sub

First off, thanks for your continued efforts!

I tried to insert your code into the "ThisWorkbook" folder under the
VBAProject (Report with Graph). It did not work. The file opens and
nothing happens.

I have a file named "Report with Graph". When I open the file, I want the
File Save As... dialogue box to simply popup so that I have to save the file
as a new name, like "06K025" or "06M034", etc. This file name is not
necessarily in numeric order, so I must name it each time.
I just want the "master" file to immediately prompt me to save as a new
filename whenever it is opened.

Another catch... I don't want the new file like "06K025" to have this
feature. Once it is created, it needs to be void of this code. That way,
when I open it I won't have to save it as something else, etc., etc.

Here is the code that I was getting close with, but keep getting errors.

Private Sub Workbook_Open()

Do

fName = Application.GetSaveAsFilename ([FileFormat:="Microsoft Excel
Workbook (*.xls), *.xls])

Loop Until fName <> False

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal'

End Sub

Reply to this Message

Henning - 22 Jun 2006 02:45 GMT

> >> I understand that. But, I can't figure out how to "assign" this macro to
> >> the file so that it will run when the file opens. I am not experienced
[quoted text clipped - 44 lines]
>
> End Sub

Only thing I can see missing is:
Dim fName as String

I tested this one
Private Sub Workbook_Open()
Dim FileSaveName As String

FileSaveName = ThisWorkbook.Application.GetSaveAsFilename
ThisWorkbook.SaveAs Filename:=FileSaveName
End Sub

/Henning

Reply to this Message

Shane R. Pouch - 22 Jun 2006 16:28 GMT

>> >> I understand that. But, I can't figure out how to "assign" this macro
> to
[quoted text clipped - 62 lines]
>
> /Henning

Okay, now we're getting somewhere. But, I still have some functionality
that I would like to have that I can't figure out.

1) I would like the file that is created by this process to NOT have this
code embedded in it. Because when I open the new file, I don't want to have
it prompt me to automatically save it as a new file. (FYI - if you cancel
the Save As dialogue box in the newly created file, you get yet another file
named "False.xls". Maybe there's a way to have the code ask first whether I
want to work on the "master" file or create a new file (Save As process).
That way in the created files, I can simply choose to work on the file
without creating a new 3rd file.

2) I would like the "Save As" dialogue box to come up with the "Save in:"
field preset to my save location (which never changes) so I don't have to
navigate to it each time.

3) I would like the "Save As" dialogue box to come up with the "Save as
type:" field preset to "Microsoft Excel Workbook (*.xls)" so all I have to
do is type in the "File name:".

Thanks again! And I hope you can help once again!

Reply to this Message

Henning - 30 Jun 2006 13:08 GMT

Shane R. Pouch skrev:

> >> >> I understand that. But, I can't figure out how to "assign" this macro
> > to
[quoted text clipped - 84 lines]
>
> Thanks again! And I hope you can help once again!

Hi,sorry about the long delay. I'm having problems with my ISP, close
to none connection :(

If as a guess your 'new' files always begin with a digit, following
should work without popping up the FileSave::

If Not IsNumeric(Left$(ThisWorkbook.Name, 1)) Then
FileSaveName = ThisWorkbook.Application.GetSaveAsFilename
ThisWorkbook.SaveAs Filename:=FileSaveName
End If

or if you choose to Cancel the filesave:

FileSaveName = ThisWorkbook.Application.GetSaveAsFilename
If FileSaveName <> "False" then
ThisWorkbook.SaveAs Filename:=FileSaveName
End If

/Henning

Reply to this Message

Bob Phillips - 21 Jun 2006 08:38 GMT

Private Sub Workbook_Open()
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsodt Excel File (*.xls), *.xls")
If fileSaveName <> False Then
ThisWorkbook.SaveAs ileSaveName
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I am trying to figure out the Visual Basic language to include in an Excel
> file so when it opens the macro will automatically run each time the file is
[quoted text clipped - 5 lines]
> Any help will be highly appreciated!
> Thanks!

Reply to this Message