Embeded macro programatically exported to new workbook

[rinser]

Hi All,

I have a series of modules that generate a rather huge table which is programatically exported to a new workbook and saved there. I would like to have the table already filtered in the new workbook and the user when opening would see the filtered results and only if desired will reveal all entries.
Can this be done? Is it possible to export a worksheet with embeded macros?

Any ideas appreciated.

[100_Meridian]

There is probably several different ways to tackle this requirement. My choice utilizes the Reference Library Microsoft Visual Basic for Applications Extensibility 5.3. The library is not loaded by default, nor have I found (haven't looked that hard) a way to loaded is programatically.

In a nutshell, I copy over the "action" sheet, then export the macro line by line into a bas file. I can then write the code to a module in the target sheet.

So the user steps involved are

1. Load the Library
2. Save and Exit
3. Open and Run the macro

It's a little awkward, but the users are proficient enough to handle these steps.

This is the code verbatim.

Please Login or Register  to view this content.

John

[wotadude]

see my previous post on the subject of setting references http://www.excelforum.com/excel-prog...ml#post2057363

This my be of intesrest.

[rinser]

Thank you for answers so far.
The problem is this tool that I am building will be used by people that don't know much about excel. Later edit <The other issue is I don't know on which computers it will be run, so there should not be any additional packages installed.> This is why I wanted something that would export the file with filters already embedded. I am not sure this is possible, if anyone have ever seen anything like this please let me know.
Thank you.

[Gabor]

Instead of exporting data, you could generate new workbooks that are copies of either your original workbook, or are copies of a 'carrier' workbook that is a template which has the macros.

Here is a macro I am using to create archive copies of the active file.
Maybe you can use it as a starting point.

Please Login or Register  to view this content.

The other thought I was having, not sure though, is if you can have the macros in the worksheet module, and once you have produced the data on this worksheet, you can just copy over this sheet to the destination file.
If the sorting is initiated by an active control, like a CommandButton, it will certainly work, once copied over.

Not sure I have understood your issue.

[rinser]

Sorry I didn't answer for a few days.
I will look at the solutions again and let you know how things are going.

[broro183]

hi,

Do you actually need the modules/macros/vba code in the new workbook?
(as the previous posters interpret from your thread title)

Or do you just need the "huge table" saved into a new workbook & have it "filtered" (do you mean Autofiltered?) in the new file?
(ie no macros needed just the finished product)
If the latter, record a macro of your actions (save as, filter & save) manually & then post the code for us to assist with.

Rob

[rinser]

Hi Rob,

I need the table that is exported to be filtered with a custom filter like "begins with" and criteria "123".

For the time being I am caught in solving another issue with one of my modules that I posted on this thread:
http://www.excelforum.com/excel-prog...as-needed.html.
Once I solve this issue I will go back to the exporting filter problem. If you have any idea please help me.

[broro183]

hi Rinser,

Your second post in the other thread makes it look like you've solved that issue

For help with this thread, can you please record a macro of your actions when you manually "Save As", Autofilter (we need to know the column/s) & Save the file containing the created table and then post the recorded code?
Also, can you please upload a sample workbook of what the finished file will look like?
- with no sensitive data but the correct layout & some explanation.

Rob