Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > changing a macro each time the file name changes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11
Default changing a macro each time the file name changes
How can I program a macro so that it will be automatically updated each time
I change a file name?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default changing a macro each time the file name changes
There isn't a single answer to thsi question because it is specific to your
macro. Most time youi can use THISWORKBOOK top specify the file where the
macro is located.

If you open workbooks up during a macro then set a variable to the new
workbook like this

set newbk = workbooks.open(filename:="book1.xls")

Then use newbk to reference the workbook like this

Data = newbk.sheets("Sheet1").Range("A1").Value

"andrewc" wrote:

How can I program a macro so that it will be automatically updated each time
I change a file name?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default changing a macro each time the file name changes
Hi,

Why not write the filename to work on into a cell and have your macro pick
up that name from the worksheet cell. For example this returns the filename.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Mike

"andrewc" wrote:

How can I program a macro so that it will be automatically updated each time
I change a file name?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11
Default changing a macro each time the file name changes
Hello Joel,

Thanks for you reply. It was most helpful. However, to clarify, I will
include the code that I have in my macro.

The code that I want to automatically change is
"Copy 2 of Prototype SO & PO.xls", which is the name of the excel file

My situation is that the file "Copy 2 of Prototype SO & PO.xls" already
contains the macro below. When I use "File Save" to change the name of the
file to "My Car.xls", for example, the code of the macro unfortunately
remains "Copy 2 of Prototype SO & PO." in the new file called "My Car.xls" .
How do I change "Copy 2 of Prototype SO & PO.xls" to "My Car.xls"?

The macro is listed below for your perusal.



Thanks,
Andrew


'
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy 2 of Prototype SO & PO.xls").Activate
End Sub

"Joel" wrote:

There isn't a single answer to thsi question because it is specific to your
macro. Most time youi can use THISWORKBOOK top specify the file where the
macro is located.

If you open workbooks up during a macro then set a variable to the new
workbook like this

set newbk = workbooks.open(filename:="book1.xls")

Then use newbk to reference the workbook like this

Data = newbk.sheets("Sheet1").Range("A1").Value

"andrewc" wrote:

How can I program a macro so that it will be automatically updated each time
I change a file name?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11
Default changing a macro each time the file name changes
Hello Mike,

How would you incorporate the my macro code into the code that you sent me?
My macro is listed below.
By the way, "Copy 2 of Prototype SO & PO.xls" is the file name that needs to
change automatically.



Thanks!

Andrew
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy 2 of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy 2 of Prototype SO & PO.xls").Activate
End Sub




"Mike H" wrote:

Hi,

Why not write the filename to work on into a cell and have your macro pick
up that name from the worksheet cell. For example this returns the filename.

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Mike

"andrewc" wrote:

How can I program a macro so that it will be automatically updated each time
I change a file name?
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
delete info from source file w/out changing the destination file TB New Users to Excel 0 May 26th 08 06:26 PM
Changing decimal time into 24 hour time and reverse Bobzter100 Excel Discussion (Misc queries) 4 January 25th 08 11:38 AM
run macro then print the results as pdf file changing the name eac Richard Ward[_2_] Excel Discussion (Misc queries) 0 October 26th 07 10:01 PM
Unable to run excel macro after changing file name merf New Users to Excel 2 June 14th 05 03:16 PM
Give File Time to update Pivot Table when started bij Macro Jasper Excel Discussion (Misc queries) 0 April 27th 05 01:30 PM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement