Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
I am designing a master template. This will be set to read only. Once this file is saved to another name the macros setup in the master file no longer work. The macro i have a problem with is opening a data file from the server and copying it to a spreadsheet. the macro can see the data file but i am having troubles with it going back to the new file as it is no longer called the master files name. I hope this is all u need to help Ty -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
Not too sure from your description but perhaps this will help. Thisworkbook
is the book that is running the code. So if your master workbook is the one running the code then you can get back to it with Thisworkbook.select Otherwise as you create the spreadsheets and such use workbook objects to keep track of the different books. dim wbkMaster as workbook set wbkMaster = workbooks("Master.xls") With the above code even if the name of the master changes you can still refer to it like this wbkMaster.Select -- HTH... Jim Thomlinson "XCESIV" wrote: I am designing a master template. This will be set to read only. Once this file is saved to another name the macros setup in the master file no longer work. The macro i have a problem with is opening a data file from the server and copying it to a spreadsheet. the macro can see the data file but i am having troubles with it going back to the new file as it is no longer called the master files name. I hope this is all u need to help Ty -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
The data is being picked up from a different workbook. to do this it needs to open the 2nd workbook. In the master file it i have it working, but not once i change the file name Here is the line that is the problem Windows("AFNE MASTER.xls").Activate Once the file name is changed from this the macro can no longer go back to this file. I need this line to recognise the change in file name -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
If you want to activate the workbook with the code
Thisworkbook.Activate -- Regards, Tom Ogilvy "XCESIV" wrote in message ... The data is being picked up from a different workbook. to do this it needs to open the 2nd workbook. In the master file it i have it working, but not once i change the file name Here is the line that is the problem Windows("AFNE MASTER.xls").Activate Once the file name is changed from this the macro can no longer go back to this file. I need this line to recognise the change in file name -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
But when i go to the other workbook to gather the data that becomes the active workbook and i want it to go back to the other workbook. here is wat it is doing * open report (where data i want to import is) * select line 3 to line 100 * goto main workbook * paste in line 5 of main workbook * close report Its getting back to the main workbook that i am having a problem with. I know i could setup a linked cells to the report file, but this report file is used by multiple ppl and can change often and want it via an upload button. -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
Dim bk as Workbook
set bk = Workbooks.Open("C:\Somefolder\somfile.xls") now you have a reffernce to the workbook Thisworkbook.Activate bk.activate but if you know the names of the workbooks, you can also do Assume book1.xls and book2.xls Workbooks("Book1.xls").Activate Workbooks("Book2.xls").Activate of course you usually don't have to activate anything to work with it, but you can look into that later. -- Regards, Tom Ogilvy "XCESIV" wrote in message ... But when i go to the other workbook to gather the data that becomes the active workbook and i want it to go back to the other workbook. here is wat it is doing * open report (where data i want to import is) * select line 3 to line 100 * goto main workbook * paste in line 5 of main workbook * close report Its getting back to the main workbook that i am having a problem with. I know i could setup a linked cells to the report file, but this report file is used by multiple ppl and can change often and want it via an upload button. -- XCESIV ------------------------------------------------------------------------ XCESIV's Profile: http://www.excelforum.com/member.php...o&userid=24271 View this thread: http://www.excelforum.com/showthread...hreadid=538258 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro not working once filename changes
thanks heaps for that. It worked. I just had to change 1 section little set bk = Workbooks.Open to Set bk = ThisWorkboo -- XCESI ----------------------------------------------------------------------- XCESIV's Profile: http://www.excelforum.com/member.php...fo&userid=2427 View this thread: http://www.excelforum.com/showthread.php?threadid=53825 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add a input box to ask for a filename to macro | Excel Discussion (Misc queries) | |||
Change Filename in Macro | Excel Discussion (Misc queries) | |||
macro reference changes with filename | Excel Discussion (Misc queries) | |||
filename in macro | Excel Discussion (Misc queries) | |||
Dynamic Filename From Macro | Excel Programming |