How to run a macro when file opens???

[Shane R. Pouch]

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!

 

[Homer J Simpson]

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.

Record yourself saving the file then edit that macro.

 

[Shane R. Pouch]

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.

 

[Homer J Simpson]

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 Autpen

Sub Autpen()
'
' Autpen 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

 

[Bob Phillips]

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



--
HTH

Bob Phillips

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

 

[Shane R. Pouch]

You MUST use the name Autpen

Sub Autpen()
'
' Autpen 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

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

 

[Henning]

You MUST use the name Autpen

Sub Autpen()
'
' Autpen 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

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

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

 

[Shane R. Pouch]

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 Autpen

Sub Autpen()
'
' Autpen 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

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

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

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!

 

[Henning]

Shane R. Pouch skrev:

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 Autpen

Sub Autpen()
'
' Autpen 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



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

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

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!

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