macro/fileanme help
 

I have a spreadsheet that is saved on a network drive and contains macros.
It is saved under a "general" file name. When people use it, they are going
to open it and resave in on their personal c drive under a different name.
However, when they do this, it causes an error in the macro's since they seem
to contain the original Excell file name in the programing. I did not
purposefully put the file name into the programinng, but it's there non the
less.

How do I fix this?

macro/fileanme help
 

can you provide more information as to where you see the
workbook name or the pathname in the workbook.

Check --- Tools, Options, General (tab)
Default file location: (should be on user's drive)
and is used for saving new files.

"Daniel R" ...
I have a spreadsheet that is saved on a network drive and contains macros.
It is saved under a "general" file name. When people use it, they are going
to open it and resave in on their personal c drive under a different name.
However, when they do this, it causes an error in the macro's since they seem
to contain the original Excell file name in the programing. I did not
purposefully put the file name into the programinng, but it's there non the
less.

How do I fix this?

macro/fileanme help
 

David,

Below is the macro that is causing the problem. The basic function of this
macro is to perform a ceratin filter, print the results, and then put it back
as it was before. However, if you look in the second line from the bottom,
you see a file name. I did not put this file name in there while recording.

The macro works fine as long as the file is not resaved in another name.
However, this is a public document on a net work drive and people will be
re-saving it under different names on in different locations once they have
made changes to it.

Keyboard Shortcut: Ctrl+p
'
ActiveSheet.Unprotect
ActiveWindow.SmallScroll ToRight:=13
Range("AE1").Select
ActiveCell.FormulaR1C1 = "0"
Range("AE2").Select
ActiveWindow.LargeScroll ToRight:=-2
Cells.Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=2
Selection.AutoFilter Field:=28, Criteria1:="1"
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=5
Selection.AutoFilter
Range("AE1").Select
ActiveCell.FormulaR1C1 = "10000000"
Range("AE2").Select
ActiveWindow.LargeScroll ToRight:=-2
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.Run "'Job Worksheet - CCTV 04-20-07 WC.xls'!auto_open"
End Sub


"David McRitchie" wrote:

can you provide more information as to where you see the
workbook name or the pathname in the workbook.

Check --- Tools, Options, General (tab)
Default file location: (should be on user's drive)
and is used for saving new files.

"Daniel R" ...
I have a spreadsheet that is saved on a network drive and contains macros.
It is saved under a "general" file name. When people use it, they are going
to open it and resave in on their personal c drive under a different name.
However, when they do this, it causes an error in the macro's since they seem
to contain the original Excell file name in the programing. I did not
purposefully put the file name into the programinng, but it's there non the
less.

How do I fix this?

macro/fileanme help
 

Hi Daniel,

Application.Run "'Job Worksheet - CCTV 04-20-07 WC.xls'!auto_open"
End Sub


If that macro is in the same workbook them simply use the macroname
as the complete instruction line.

auto_open

BTW, would suggest when you include= dates as part of
the file name to do so as yyyy-mm-dd so they can be sorted or
ordered by filename.

dim dname as string
dname = "Job Worksheet - CCTV " & _
Format(Now(), "yyyy-mm-dd") & " WC"
ActiveWorkbook.SaveCopyAs dname

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Daniel R" wrote in message ...

David,

Below is the macro that is causing the problem. The basic function of this
macro is to perform a ceratin filter, print the results, and then put it back
as it was before. However, if you look in the second line from the bottom,
you see a file name. I did not put this file name in there while recording.

The macro works fine as long as the file is not resaved in another name.
However, this is a public document on a net work drive and people will be
re-saving it under different names on in different locations once they have
made changes to it.

Keyboard Shortcut: Ctrl+p
'
ActiveSheet.Unprotect
ActiveWindow.SmallScroll ToRight:=13
Range("AE1").Select
ActiveCell.FormulaR1C1 = "0"
Range("AE2").Select
ActiveWindow.LargeScroll ToRight:=-2
Cells.Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=2
Selection.AutoFilter Field:=28, Criteria1:="1"
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=5
Selection.AutoFilter
Range("AE1").Select
ActiveCell.FormulaR1C1 = "10000000"
Range("AE2").Select
ActiveWindow.LargeScroll ToRight:=-2
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.Run "'Job Worksheet - CCTV 04-20-07 WC.xls'!auto_open"
End Sub


"David McRitchie" wrote:

can you provide more information as to where you see the
workbook name or the pathname in the workbook.

Check --- Tools, Options, General (tab)
Default file location: (should be on user's drive)
and is used for saving new files.

"Daniel R" ...
I have a spreadsheet that is saved on a network drive and contains macros.
It is saved under a "general" file name. When people use it, they are going
to open it and resave in on their personal c drive under a different name.
However, when they do this, it causes an error in the macro's since they seem
to contain the original Excell file name in the programing. I did not
purposefully put the file name into the programinng, but it's there non the
less.

How do I fix this?