Looking for Approach on Excel Project

S

S Himmelrich

Basically, I'm provided with a worksheet with several sheets with a
lot of cleanup and some deletion of sheets. I must do the clean up in
Excel, no export to Access or another product then re-import. What
has been done so far is that macro's are build to perform this
funtionality. However that means you must use a template where the
macro resides.

Here are my several questions?

Is there a way to reference code or external macros to run against an
excel file? If so, where is there information on this process?

What is the best approach on this type of cleanup script or macro?

your thoughts would be appriciated on this...thx.
 
G

Guest

You keep your macros in your own workbook. You open your workbook. You open
the workbook submitted to you to clean & process (say its called
Submiited.xls)


At this point you have two books opened. The code in your workbook starts
out with something like:

Sub gothere()
Workbooks("Submitted.xls").Activate
End Sub

Then you have the rest of the cleanup code. In the end, Submitted.xls will
be saved and closed. This way you don't need to actually put any macros in
the submitted workbooks.
 
P

Pete_UK

You could have a macro in a Clean_up.xls file, for example, which asks
you to point to the folder and file that you want it to work on, and
it then opens that file and does what is needed and finally saves the
file with a new name. The original Clean_up file is not affected, and
so can be used next time, and there are no macros in the saved file.

You could put the macros in your Personal.xls, so they will always be
available.

You might even think about turning them into an add-in - see here for
further advice on this:

http://www.cpearson.com/excel/CreateAddIn.aspx

Hope this helps.

Pete
 
S

S Himmelrich

You could have a macro in a Clean_up.xls file, for example, which asks
you to point to the folder and file that you want it to work on, and
it then opens that file and does what is needed and finally saves the
file with a new name. The original Clean_up file is not affected, and
so can be used next time, and there are no macros in the saved file.

You could put the macros in your Personal.xls, so they will always be
available.

You might even think about turning them into an add-in - see here for
further advice on this:

http://www.cpearson.com/excel/CreateAddIn.aspx

Hope this helps.

Pete

Thank you for your response....this will be somewhat of a temporary
situation I'm creating, but your suggestion is something to consider;
otherwise I'm going to go with one of the other responses as I'm not
really a programmer in Excel.

Workbooks("Submitted.xls").Activate

code that intiates and works the sheet I receive. Thanks again for
the response.
 
P

Pete_UK

Thanks for the feedback.

With you mentioning that you had macros to do the cleaning up, I
assumed that you had some VBA experience. There are many people here
who will help you with the coding if you get stuck.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top