Start up macro

[Kjetil]

I struggle with a workflow that is supposed to run unattended. It includes the following steps:

1) Creation of sdv-file from Oracle
2) Importing the sdv-file in a ready made Excel file. Updating some pivots.
3) Sending the Excel file to a colleague.

Task #1 and #3 is programmed and running unattended already, but i need help with the Excel prosessing. I guess it can be solved this way:

a) I call the "Master.xls" which has a start up macro.
Question: It's possible to dedicate a start-up macro to a single excel-file, right? I would appreciate any reference to have this can be done.

b) The macro is being executed (i should manage this by my own...)

c) After dataprocessing, i want to save the file as "Clone.xls". This version of the file should not hold the start-up macro.
Question: Is it possible? How?

Thanks in advance!
Kjetil

 

[Norman Jones]

Hi Kjetil,

The WorkBook_Open event may be used to import
data and update tthe pivot tables,

For an overview of event procedures, see Chip Pearson
at:

Event Procedures
http://www.cpearson.com/excel/events.htm

To save a copy of the resultant file without theWorkBook_Open
code, save the required sheets in a new workbook, for example

Sheets(Array("Sheet1", "Sheet2")).Copy

Then, save the new worbook and close rthe original without
saving changes.

Alternatively, Chip Pearson demonstrates how code can be
removed progammatically at:

Programming To The Visual Basic Editor
http://www.cpearson.com/excel/vbe.htm


---
Regards,
Norman



I struggle with a workflow that is supposed to run unattended. It includes
the following steps:

1) Creation of sdv-file from Oracle
2) Importing the sdv-file in a ready made Excel file. Updating some pivots.
3) Sending the Excel file to a colleague.

Task #1 and #3 is programmed and running unattended already, but i need help
with the Excel prosessing. I guess it can be solved this way:

a) I call the "Master.xls" which has a start up macro.
Question: It's possible to dedicate a start-up macro to a single excel-file,
right? I would appreciate any reference to have this can be done.

b) The macro is being executed (i should manage this by my own...)

c) After dataprocessing, i want to save the file as "Clone.xls". This
version of the file should not hold the start-up macro.
Question: Is it possible? How?

Thanks in advance!
Kjetil

 

[Jan Karel Pieterse]

Hi Kjetil,

a) I call the "Master.xls" which has a start up macro.
Question:
It's possible to dedicate a start-up macro to a single excel-file,
right? I would appreciate any reference to have this can be done.

Open the ThisWorkbook module, click on the lefthand dropdown at the
topand choose Workbook. The Workbook_Open event is inserted for you
automatically. Other events are available trough the righthand dropdown
list.

In the Workbook_Open stub, put a call to your startup macro (Advice:
put as much as your code in subs in a normal module as opposed to in
Thisworkbook).

b) The macro is being executed (i should manage this by my
own...)

You'll have a problem with macro enabling though, unless you either set
macro security to low, or digitally sign your VBA code.

c) After dataprocessing, i want to save the file as
"Clone.xls". This version of the file should not hold the start-up
macro.
Question: Is it possible? How?

Yes:

Dim oNewBk as workbook
Thisworkbook.Worksheets.Copy
Set Set oNewbook=ActiveWorkbook
oNewBook.SaveAs "Clone.xls"
oNewbook.Close
Set oNewBook=Noting

Note that code behind the worksheets will travel with your copy, but
normal modules, class modules, thisworkbook module are all left as is:
empty.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Kjetil]

Thanks a lot, Jan!

Kjetil

Hi Kjetil,


Open the ThisWorkbook module, click on the lefthand dropdown at the
topand choose Workbook. The Workbook_Open event is inserted for you
automatically. Other events are available trough the righthand dropdown
list.

In the Workbook_Open stub, put a call to your startup macro (Advice:
put as much as your code in subs in a normal module as opposed to in
Thisworkbook).


You'll have a problem with macro enabling though, unless you either set
macro security to low, or digitally sign your VBA code.


Yes:

Dim oNewBk as workbook
Thisworkbook.Worksheets.Copy
Set Set oNewbook=ActiveWorkbook
oNewBook.SaveAs "Clone.xls"
oNewbook.Close
Set oNewBook=Noting

Note that code behind the worksheets will travel with your copy, but
normal modules, class modules, thisworkbook module are all left as is:
empty.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Kjetil]

Thanks a lot, Norman!

Kjetil