Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making VBA code from one WB work on another WB
I'm working on basically a complex order taking/proposal/scope of work
spreadsheet and am doing pretty well with the coding for tricks I need. However, I'm starting to generate a rather large file due to all the background code. In order to keep the file size down, I want to offload a lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls) that is always kept running, and call the code from there. The problem I'm having is how to get the subroutines to look back and work on the spreadsheet that's calling them. The names change dynamically, so this is part of the problem. For example, the spreadsheet starts off as NEWORDER.xls. One of the subs I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look at some cells on NEWORDER.xls to name the file in a very specific format, concatenate the filename from that, then rename NEWORDER.xls to (for this example) CUSTORDER1.xls. How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it when it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say, to create a second version of the file as CUSTORDER1a.xls) and have it look at whichever worksheet called it? I assume that there's some simple identifier that I could append to each range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make this work easily, but I don't know what it is and haven't been able to locate it in my reference books. Apparently I don't speak 'crossreferencese' very well. Thanks in advance for any help. - Forest Ramsey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making VBA code from one WB work on another WB
Hi ForestRamsey,
Try ActiveWorkbook. --- Regards, Norman "ForestRamsey" wrote in message ... I'm working on basically a complex order taking/proposal/scope of work spreadsheet and am doing pretty well with the coding for tricks I need. However, I'm starting to generate a rather large file due to all the background code. In order to keep the file size down, I want to offload a lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls) that is always kept running, and call the code from there. The problem I'm having is how to get the subroutines to look back and work on the spreadsheet that's calling them. The names change dynamically, so this is part of the problem. For example, the spreadsheet starts off as NEWORDER.xls. One of the subs I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look at some cells on NEWORDER.xls to name the file in a very specific format, concatenate the filename from that, then rename NEWORDER.xls to (for this example) CUSTORDER1.xls. How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it when it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say, to create a second version of the file as CUSTORDER1a.xls) and have it look at whichever worksheet called it? I assume that there's some simple identifier that I could append to each range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make this work easily, but I don't know what it is and haven't been able to locate it in my reference books. Apparently I don't speak 'crossreferencese' very well. Thanks in advance for any help. - Forest Ramsey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making VBA code from one WB work on another WB
DOH! I knew it would be something simple. Thanks. Much appreciated.
"Norman Jones" wrote: Hi ForestRamsey, Try ActiveWorkbook. --- Regards, Norman "ForestRamsey" wrote in message ... I'm working on basically a complex order taking/proposal/scope of work spreadsheet and am doing pretty well with the coding for tricks I need. However, I'm starting to generate a rather large file due to all the background code. In order to keep the file size down, I want to offload a lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls) that is always kept running, and call the code from there. The problem I'm having is how to get the subroutines to look back and work on the spreadsheet that's calling them. The names change dynamically, so this is part of the problem. For example, the spreadsheet starts off as NEWORDER.xls. One of the subs I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look at some cells on NEWORDER.xls to name the file in a very specific format, concatenate the filename from that, then rename NEWORDER.xls to (for this example) CUSTORDER1.xls. How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it when it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say, to create a second version of the file as CUSTORDER1a.xls) and have it look at whichever worksheet called it? I assume that there's some simple identifier that I could append to each range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make this work easily, but I don't know what it is and haven't been able to locate it in my reference books. Apparently I don't speak 'crossreferencese' very well. Thanks in advance for any help. - Forest Ramsey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making changes in all pages in a work book | Excel Discussion (Misc queries) | |||
Making this formula work | Excel Worksheet Functions | |||
Making a Reuters function work in VBA | Excel Discussion (Misc queries) | |||
making a userform work ,oops | Excel Programming | |||
making a userform work | Excel Programming |