insert vba code to new workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jive - 04 Mar 2008 16:44 GMT

Hi

I have a large 5ish meg spreadsheet, into which project info is entered.
Once complete there is a function which opens a new workbook and then saves
it with the raw data in it, ie as a save file. which is much smaller.

I would like to add some code to the "Private Sub Workbook_Open()" section
of the save file so that if it is opened directly, and not through the main
tool as it is supposed to be, exports itself to the main tool.

I havent worked out the exact coding, although i know roughly what i need.

However, I need help on how to open a blank workbook and add the code to the
"Private Sub Workbook_Open()" section. I dont want to use a template if that
is possible.

Many Thanks

Reply to this Message

FSt1 - 04 Mar 2008 17:36 GMT

hi
i not sure what you mean by "main tool" so i feel hesitant about suppling an
specific code. but roughly you need help crating a blank workbook and adding
code to it??
to create a blank workbook use workbook.add
to add code to it, see this site
http://cpearson.com/excel/vbe.aspx

regards
FSt1

> Hi
>
[quoted text clipped - 13 lines]
>
> Many Thanks

Reply to this Message

Jive - 05 Mar 2008 14:12 GMT

By main tool i ment the main 5MB spreadsheet.

A few months ago i replace an old estimating tool which we have used for
years and we always saved a copy of the filled in tool for every project.

This never used to be an issue but now the company has grown and we are
estimating circa 100 projects a day the space the file eats up on the server
is an issue.

The new estimating tool saves a simple 20KB speaeadsheed containg the raw
data and woorks realy well by selecting "import estimate" from within the
estimating tool.

However some of the staff are having problems remembering to open up the
estimating tool and then browse for the etimate save from inside it. So they
moan repeatedly about the amount of time they waste finding the project
folder on the server and opening up essentially useless data files. and then
rembering the correct way to do it and repeating the search.

Originally i assumed they would get used to the new way of working, but you
cant teach old dogs new tricks and all that.

So just to reassure you there is noting sinister planned, although i never
though about the possibility of a virus scanner considering the code a
threat. will have to test that when im done i guess.

Thanks for the directions all the same

> hi
> i not sure what you mean by "main tool" so i feel hesitant about suppling an
[quoted text clipped - 24 lines]
> >
> > Many Thanks

Reply to this Message