Run procedure in Personal.xls which needs name of calling workbook

[Guest]

I currently have a spreadsheet set up with coding to automatically configure
the individual sheets based on which farms and how many loads at each farm
are scheduled.

After sheet is set up, coding is NO LONGER NEEDED, and currently saves the
workbook with a new name, then removes ALL the VBA coding from the workbook.

I want to be able to have the VBA coding removed from the workbook THEN have
the workbook save itself.

I tried it with the save procedure AFTER the code removal procedure, but
once the code is removed, the save procedure never executes (as expected) So
I'm thinking I probably need the code remove/workbook save procedure in
another workbook. But THAT would mean I'd have to be able to call that
procedure FROM the workbook being worked on, and get that external procedure
to know which workbook was previously active and had called the procedure.

I tried copying the save/code remove from the workbook to the Personal.xls,
but now I need the way to tell the procedure which workbook it was called
from.

Help on the RUN method says the ONLY type of arguments that RUN will allow
to be sent to the external procedure are NUMERIC, and will NOT accept
variables or strings to be passed.

Is this ONE area where the Help system is wrong? If not, how can I tell the
procedure in my Personal.xls the name of the active workbook that had called
the procedure? When I execute the RUN method, the ActiveWorkbook changes to
Personal.xls, which seems to mean I need some way to set the
ActiveWorkbook.Name to a variable, then have some way to send the contents of
that variable TO the procedure in the Personal.xls workbook so it can know
which workbook it is supposed to be removing the code from and saving.

 

[Bob Phillips]

Application.Run("MYCUSTOM.XLS!MyMacro", "workbook_name")

 

[Tom Ogilvy]

You wouldn't want to use the run method anyway if you will save and close
the workbook. You would want to use application.Ontime.

Just make your workbook to be saved (and closed) to be the activeworkbook
before you call your code using Ontime. You should have that code also
contain the code for removing code from the workbook to be saved.

If you try to save in the same code that does the clearing of the code. you
will find that the code is not cleared. Using Ontime breaks this code
chain and allow the code to be actually removed and the workbook saved
without the code

 

[Guest]

The workbook having the code removed from and then saved will not NECESSARILY
be CLOSED after being saved.

Normally the workbook gets set up just before night shift starts and needs
to use the workbook that was set up, so I just need it to be able to know
which workbook called it (would prefer to use ActiveWorkbook.Name assigned to
variable so when I update features/functions in the workbook setup file, I
can use the SAME code to accomplish the SAME goal WITHOUT having to change
the workbook name within the code inside the workbook every time the workbook
setup file name gets changed)

Most times, the setup code is run to configure the spreadsheet workbook for
the coming night and day shift workday, and the only reason for the self-save
code is so the resulting files will ALWAYS have a filename easy to track so
if the computer crashes and needs re-booting between shifts, the person
coming in for the next shift knows what filename to look for instead of
wondering "What name was todays spreadsheet saved under this time???"

Once in a while, the workbook will be closed as soon as it is set up, but
MOST of the times, it will be saved several times during each shift so the
new data in the sheets is saved, but only gets closed at the end of Day
Shift, which could occur anywhere from 10 - 20 hours AFTER the workbook was
initially set up and the code removed.


With application.Ontime can I pass variables (ie, new filename, plant
rundate) to the procedure being called??? The first part of the filename is
fixed, but it has the plant run-date Month & Day added to it to make the
filename unique. The Plant run-date is stored in one of the cells in
worksheet(5) in the workbook to be saved. The Plant run-date is USUALLY the
day AFTER the workbook is set up, but sometimes on weekends, the workbook for
Sunday Night/Monday will be set up already Friday afternoon, so it is NOT
possible to simply add XX days to today's date to arrive at the run-date to
append to the filename, however the correct run-date is stored in the one
cell of worksheet(5) in the workbook.

 

[Guest]

Calling the save with Application.Ontime, what would be the command format I
need (if it's possible) to use Application.Ontime to Call the save routine,
AND send the save routine the name of the workbook that it is to save under
the new name the save routine generates with a text string and the value of
the cell on worksheets(6) of the workbook the save routine is supposed to be
saving?

I got the external routines to work.. sort-of, but without using
Application.Ontime to force the rpocedure to wait 5 - 10 seconds before
calling the save routine causes the saved workbook to have all the actual VBA
coding removed, but it still has the empty forms and Modules names in it so
when the sheet is opened, Excel still thinks the sheet contains macros.
Looks like if I can delay the save for probably another 5 - 10 seconds, that
would probably be long enough to allow the empty modules to be removed before
the save routine actually saves the workbook