Help !! Output

[Guest]

I have a VBA template (Little Less than 1 MB) . Template contains a worsheet (1) which contains data and i have couple of forms with listboxes etc which pick the data from the worksheet (1) and depending upon the selections made on forms output is created on worksheet (2) . Now my problem is that when output is created the size of my xls is same (little less that 1 mb) where as now i dont need that data in worksheet (1) and all the forms and macros/code etc. Could someone suggest a way to cut down the size of my worksheet with output.

Suggestions will be appreciate

 

[Bob Phillips]

Try creating your template as an addin, and create a new workbook from this
which will have the data you require, but not the code of forms.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a VBA template (Little Less than 1 MB) . Template contains a

worsheet (1) which contains data and i have couple of forms with listboxes
etc which pick the data from the worksheet (1) and depending upon the
selections made on forms output is created on worksheet (2) . Now my problem
is that when output is created the size of my xls is same (little less that
1 mb) where as now i dont need that data in worksheet (1) and all the forms
and macros/code etc. Could someone suggest a way to cut down the size of my
worksheet with output.

 

[Kalle]

Make a copy with SaveAs TempName
Switch to TempName
Delete Sheets whatever You need to delete
Delete modules as needed.
Save Sheet(1)

TempName still got your code and other data.
Sheet( 1 ) containes only wanted sheets.

 

[Guest]

Thanks for your reply Bob
I am a little new to this...would you suggest a link where i could get some details of doing so. Or if you could write few lines on that...it will be appreciated

 

[Bob Phillips]

An addin is just an Excel workbook, but saved as type xla not xls. So put
your macros in a new workbook, and just save it as an xla (bottom option on
file types in the save dialog).

Then go and install it in Tools>Addins by clicking Browse, selecting the
file, and then checking the new entry.

The main thing to take care of is to ensure that you always specifically
refer to a workbook/worksheet, as normally non-referenced code works on the
active workbook/activesheet, but they will not be visible to addin code (an
addin has an activeworkbook and an activesheet, you just cannot see them).

Post back if you need more help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks for your reply Bob,
I am a little new to this...would you suggest a link where i could get

some details of doing so. Or if you could write few lines on that...it will
be appreciated.

 

[Guest]

Bob
Thanks for lending help. I did some research on add-in's and it sounded as a perfect solution for my problem. Bob, i had a question. Let's say if teh template is located on a server and client tries to open it. Should the xla file be available on the client side.

Thanks once again.

 

[Bob Phillips]

AS,

An addin is always loaded when Excel starts up, if installed properly, so it
is in memory, but it can be located anywhere on the network.

I must admit that I envisaged the template file being in the addin. As an
addin is just another workbook, albeit one that you do not see, it can have
worksheets that can be used by the macros just like any other workbook. But
again of course it doesn't have to be that way, and it can be a separate
file, again located anywhere on the network if that is what your design
determines.

Good luck, and keep posting if you need further help/advice.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
Thanks for lending help. I did some research on add-in's and it sounded

as a perfect solution for my problem. Bob, i had a question. Let's say if
teh template is located on a server and client tries to open it. Should the
xla file be available on the client side.

 

[Guest]

Thanks alot Bob...I hope it will be help out. I am working on it. Shall keep you posted about the progress