Macro Distribution

**Fo_Fa** · 01-19-2011, 06:29 PM

Been going through Google and searches here for 3 hours now, and trying different things with no luck. But not a big Excel person (access mostly).
So I have a working VB macro in Excel 2007.
I need to distribute this (well 2 macros and a function actually) to some people that do not know how to install anything.
So I was getting the best way is via an Add-In.
But when I create the add-in file, and place it in my add-in folder, it shows up and I can check it, and it is listed as an active add-in.
How ever I see no way to run the macros.
Nothing is shown on my Add Ins tab, nothing is listed via the Macros list, I am not seeing anything.
So any suggestions as to what I am doing wrong?

**shg** · 01-19-2011, 06:39 PM

Welcome to the forum.

First, in the VBE, select the add-in project and do Tools > VBA Project Properties. On the General tab, change the name to something recognizable, like projFoFa.

Then, select the project in your workbook, and do Tools > References, and set a reference to projFoFa.

**jaslake** · 01-19-2011, 07:41 PM

Hi Fo_Fa

You're not doing anything wrong. As shg pointed out, you'll need to set a Reference to your Add-In. However, having done so, still

...nothing is listed via the Macros list, I am not seeing anything.

This will not change.

One of the anomalies of an Add_In is that the procedures DO NOT APPEAR on your macro list. However, they're there if the Reference is set.

You can access these procedures directly (if they're not Private) in your code with the Call construct such as Call DeleteDups where DeleteDups is a procedure within your Add_In. Using an Add_In takes practice and patience...having learned the anomalies, distribution becomes a breeze. You only need to give the file to the user and tell them where to put it. Better yet, I use TeamViewer and do it myself. Great, free tool (free for non-commercial use).

Add-Ins are a wonderful tool...only one workbook to maintain. Good luck.

**Fo_Fa** · 01-20-2011, 01:25 PM

OK, I am obviously doing something wrong. Quick step guide maybe you can point my error/s.
Firstly removed my attempts of the add-in from excel, removed all files related, start from scratch kind of thing.
Create XLSM renamed VBproject as suggested.
Add new Module (Module1, does not seem to allow me to rename that).
Paste my 2 subs and 1 function. The subs and function are defined as Public Sub/Function xxx.
At this point, if I go through Developer, Macros, I can see/run my two macros.
Now File, Save As
Select Excel Add-In (*.xlam)
Let it default to my Add-In folder
Close Excel (makes me feel better, don't know if it is required)
open blank workbook
Go through excel options, excel add-ins, check next to the one I saved
Close Excel (makes me feel better, don't know if it is required)
open blank workbook
ALT-F11
the code shows up.
Tools, References
Can't see anything of my add-in.
I can see UNSAVED: VBAProject (which I take it is the blank workbooks project.
Back to Excel Options
It shows my xlsa as an Active Add-In
Nothing under the Add-In tab, but I have not created anything but the actual macros if that matters.

**shg** · 01-20-2011, 01:33 PM

Originally Posted by shg

First, in the VBE, select the add-in project and do Tools > VBA Project Properties. On the General tab, change the name to something recognizable, like projFoFa.

Then, select the project in your workbook, and do Tools > References, and set a reference to projFoFa.

Sorry, that is completely bogus information. I was posting in a similar thread in another forum for someone who wanted to access macros in another open workbook that was NOT an add-in.

You don't need to set a reference to an add-in. (You would want to set one to Personal.xls, which is not an add-in, but a hidden workbook.)

As John says, you won't see the macros in an add-in if you do Alt+F8, but you can enter the name (the Run button will enable when it's recognized), and you can assign a shortcut.

So now that I've totally confused things (sorry!), what is it you want to do that you can't?

**shg** · 01-20-2011, 01:59 PM

If you want to write code that invokes procedures in an (installed) add-in or another (open) workbook, you can do this without a reference:

Please Login or Register  to view this content.

If you create a reference, you can do this:

Please Login or Register  to view this content.

**jaslake** · 01-20-2011, 02:10 PM

Hi Fo_Fa

Shg beat me to the last comment regarding addressing the Add-In DIRECTLY if you've set a reference. One more point about setting the reference (Tools --> References)...you may need to Browse for your new Add-In to find it. I've had to do such on many occasions.

If you've been successful, you'll see the References as part of your project (see attached).

**Fo_Fa** · 01-20-2011, 04:57 PM

Ok, so basically what I want to do is give them the add-in, and having a button or something easy for them to run it would be ideal.
I am assuming it would appear in the ADD-IN tab then? (that's a question).
And I have been unable to find how to actually do that.
So I envision they add this Add-In,
and magically they have a button that will run the macros.
Is that expecting too much from Excel?

**shg** · 01-20-2011, 05:18 PM

I am assuming it would appear in the ADD-IN tab then?

It would appear in Excel Options > Add-Ins, yes.

So I envision they add this Add-In,
and magically they have a button that will run the macros.

No magic, just work.

Adding a toolbar, or a button to a toolbar, in Excel 2003- was easy. Doing it in Excel 2007 is completely different, and I've never done it (lots of other people here have, though).

If you have only a few macros, you could assign shortcuts to them. Your assigned shortcuts would become live when they installed the add-in.

**Fo_Fa** · 01-20-2011, 05:28 PM

probably the issue I have been encountering is I could find examples of excel 2003, and it didn't work (never having done it before).
So what is this shortcut of which you speak?
And where does it show up?

**shg** · 01-20-2011, 05:32 PM

Do Alt+F8 to display the macro dialog. Select a macro from the list, or type the name of one in the box, and press Options. Follow your nose from there.

**jaslake** · 01-20-2011, 05:42 PM

Hi Fo_Fa

I'm attaching a couple more pictures from my personal workbook to give you an idea what you'll see (and not see). In the "Screen Shot.jpg" you'll see that I've put a Button "Validate Register". In the "Sample Add_In Macro.jpg" you'll see the macro I assigned to that button.

As you see, the macro "validate_check_register" does not show up as an available macro. I simply typed the name in and it's available BECAUSE I set a reference to the Add-In. You don't see them but they're there. It's a difficult concept...not intuitive. Once you get it to work, it'll fall in to place.

Long story short, you assign the Add-In macro to a button (or call it from a procedure) the same way you would if it were in the active workbook. You just can't see it. Tough concept. Work through it...it works.

**Fo_Fa** · 01-24-2011, 02:56 PM

if I am understanding this correctly then, you have to add the button to the current workbook to point to the macros in the add-in.
I can't do that because the current workbook is created automagically by a third party and I have no control. The people (96 locations around the globe) are not all excel savvy enough to do that much.
I was thinking it would be possible to put the buttons, already linked to the macros, actually in the Add-In and have them appear (in the add-in tab maybe).

Thoughts?

**jaslake** · 01-24-2011, 05:55 PM

Hi Fo_Fa

I'm not familiar with this

put the buttons, already linked to the macros, actually in the Add-In and have them appear (in the add-in tab maybe)

although I'm quite certain others on the Forum could help you through that.

I'm AM familiar with working with Excel .xla and .xlam files. If you describe your process, perhaps I can help. For example, you get this

created automagically by a third party and I have no control

workbook. Do YOU get it or do all

people (96 locations around the globe)

get this "automagically" created workbook. Are YOU in between the workbook and the 96 users?

If you're in between and the "automagically" created workbook is not protected up the kazoo, you can do anything you want with it. You can add buttons to the worksheets, you can add code to the buttons and this can all be done WITH code. All you gotta do is press a button or call a procedure.

This may be simple, it may be complex. I may be able to help, I may not. If you're interested, lay out the process, step by step. If you're REALLY interested, post sample files and describe what you wish to do. (By the way, what is it that these Add-In procedures do that's "Special")?

**snb** · 01-24-2011, 06:41 PM

How to load an addIn

Please Login or Register  to view this content.

How to activate the macro 'vergeet' in 'module1' of the addin snb.xla

Please Login or Register  to view this content.

To add magic to your code:

Please Login or Register  to view this content.

**Fo_Fa** · 01-25-2011, 10:16 AM

The 96 locations around the globe are each Emailed a generated spreadsheet from some automated system.
What they get actually is a program generated password protected ZIP file with a spreadsheet inside.
They have to unzip, and apply any specified changes to their local payroll system.
Then next month, populate the spreadsheet with contribution data, and populate a formula down, verify any discrepancies, export one column's values in a fixed width text file they upload to the same third party that created the initial spreadsheet.
That third party then a few days later, makes any changes/adjustments needed, and sends new spreadsheets out, The cycle repeats itself each month.
Rather than following all their convoluted instructions, I am trying to create these macros to facilitate the process, and maybe eliminate some human error, by allowing them to copy/paste the contribution data into a sheet, run a macro that updates the main sheet.
Populates their formula down (which BTW is too long to control externally, it's like 286 length) and export the resulting values to a file they specify.
Basically.

Note - told NOT to change their formula.
So working within the limitations placed on us, an Add-In seems like the best choice.

**snb** · 01-25-2011, 10:42 AM

Updating the Excel-file could be done more efficiently using a Webquery (no need to mail zipped, passworprotected Excelworkbooks).

Or you could update the workbook and the addin using Excel's builtin ftp-facilities:

Please Login or Register  to view this content.

**jaslake** · 01-25-2011, 01:18 PM

Hi Fo_Fa

An approach you might wish to consider:

Prepare a "Macro Book". Your Add-In would be referenced to the VBA Code of this "Macro Book". This book would have a UserForm Menu that the 96 Users could perform their functions from, including opening the payroll file and including the buttons that run your procedures.

Alternately, your procedures could be contained in the "Macro Book"; either way, you'd have only one Book to maintain.

Just thinking out loud but I'd think this could be made to work. If you wish to explore, let me know.

**Fo_Fa** · 01-25-2011, 02:48 PM

Some other distribution would be preferred, but everything we know of, such as SharePoint, etc is a maint. nightmare handling security.
There are about 170 different spreadsheets distributed to the 96 people.
Who can get and see what is horrible. Currently out of our control.
That's been the biggest problem to distribution so far.

**Fo_Fa** · 01-25-2011, 03:01 PM

But while I am on a roll. I searched and found something about not being able to import from a closed workbook.
So given you have a workbook open (WB2) run a macro (VBA) to:

Ask for the data workbook (WB1) to open
open that WB1
create a sheet1 in current workbook (WB2)
copy the data from the opened WB1 into the current WB2 new sheet1
close the opened WB1?

**jaslake** · 01-25-2011, 03:11 PM

Hi Fo_Fa

Are you asking me or are you addressing snb?

If you're asking me, yes that is an approach; or, work on WB1 directly. What I'd suggest is you emulate what the 96 users currently do; if they work on a copy, create a copy; if they work on the original file, work on the original.

Let me know if I can help.

**Fo_Fa** · 01-25-2011, 04:46 PM

Well the macro that applies the data, is looking for it in a sheet1 (I can change that),
but currently we have them opening the up the WB1, copy/paste into sheet1 manually. If I could automate that with a macro it would just make that even easier.
I think we will try to handle the distribution, but after we get these macros out. The macros will eliminate a lot of the problems (and complaints) we are getting.
So yes, any help would be appreciated.

**jaslake** · 01-25-2011, 05:09 PM

Hi Fo_Fa

This can be handled in the procedure

we have them opening the up the WB1, copy/paste into sheet1 manually. If I could automate that with a macro it would just make that even easier

I have to assume, since this is payroll information, the files are confidential. It'll be bit difficult trying to "see" what you want to do without seeing what you're working with but, we can try.

I guess I need to ask "Do you understand the concept? Do you have any confidence it'll work? Or is it too soon?" I believe it'll work. MAKING it work will be a challenge if you're not able to SHOW me what you have...but, we'll try

.

Once you get the process down, distribution will be a breeze.

I'd like you to think about what this project is going to look like. What steps are going to be required...I need to the process to do this...and this...and this...etc. All these steps can be built into the Menu which will call the appropriate procedure. No code need be in the "automagically created workbook". It'll all be in the Add-In or, alternately, in the "Macro Book" or both.

Love these kind of challenges...where do you wish to start...how can I help?

**Fo_Fa** · 01-27-2011, 01:14 PM

OK, let me try to give you as much info as I can without over stepping any bounds.
First I got an add-in that creates a menu driven shortcut in the QAT.
I have changed this add-in to put all my macros in it, modify the menu to show my macros. This seems to be working and I should be able to distribute the new add-in with my macros in it, driven by the QAT menu selection. So giving them visability to the macros seems to be solved.
So what I would like to do now is:
1 - have them open the main workbook
2 - select a "merge payroll contributions" macro
3 - Run the other macros as required.

Step 2 today is as follows:
Open an XLS file that comtains two columns of data. Employee Number and Contributions Amount
They manually insert a Sheet1 to the main workbook, then copy and paste the 2 columns of data from this XLS into that.
Then they can close the payroll workbook.

The first macro they then run (right now) matches the employee numbers from Sheet1 into the main sheet and populates the contributions amounts. If no match is found, it puts a zero value in.
It then deletes the Sheet1.

So if I could ask for the payroll contributions spreadsheet (file name via normal file open dialog) (it is XLS because of the tool we have) and create a new Sheet1, and populate it with that payroll information, and eliminate the manual process, I think I would be good to go for now.

Searching (not saying I am using the proper key words) didn't turn up anything on it.

**jaslake** · 01-27-2011, 01:48 PM

Hi Fo_Fa

You've lost me a bit. How many workbooks are we talking about?

Do you have a "Menus" workbook?
What is the "Main Workbook"?
Is this another workbook?

Open an XLS file that contains two columns of data. Employee Number and Contributions Amount

What is this

Then they can close the payroll workbook.

What versions of Excel does this need to accommodate? I see from your profile, you use Excel 2007; what do the 96 users use?

Are the Workbook Names always the same?
Are they in the same folder?

This will take some "Back and Forth" since I don't see what you see.

**Fo_Fa** · 01-27-2011, 02:58 PM

This is what the Main Workbook (temp.jpg) and the Payroll Data (temp1.jpg) workbooks look like.
The Amount field in the Payroll Data workbook is matched up to the Main Workbook, and the amount populated in the Contributions Amount field.
This we have working.
But the macro needs to read the Payroll Data from a Sheet1 in the current (Main) workbook and applies it. I think it is using Vlookup by applying a formula to the Contributions Amount field.

What I would like to do is handle the open the Payroll Data workbook, create a new Sheet1 in the Main Workbook, copy the data from Payroll Data into it, and then close the Payroll Data workbook.

I tried recording a macro to do that, but it is very specific by what I keyed in.
I added a new sheet
Sheets.Add After:=Sheets(Sheets.Count)
But I don't know how to verify it is Named Sheet1, nor how to handle an exception if it is not named that. Maybe I should use a different name?

Then I opened the Payroll Data Workbook, but in the nacro I would like to just open a file open dialog, recording the macro has everything hard coded, which is not workable as the name will change depending on the location and person.

ChDir "C:\Temp5\Folder1"
Workbooks.Open Filename:="C:\Temp5\Folder1\Test_Contribution_PayrollData.xls"

I think the select and paste worked OK, except it has the Main Workbook's sheet hardcoded
Selection.Copy
Windows("TEST_CONTRIBUTIONS_FOR_17X7_20110114.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste

Then I wanted to close the Payroll Data workbook, again it hardcoded the name
Windows("Test_Contribution_PayrollData.xls").Activate
ActiveWindow.Close

And just make the Main workbook active again, it hard coded the name once again.
Sheets("TEST_CONTRIBUTIONS_FOR_17X7_201").Select
Range("A2").Select

So basically I want to make this more generic.

**jaslake** · 01-27-2011, 03:06 PM

Hi Fo_Fa

Will Sheet1 already exist? If not try

Please Login or Register  to view this content.

I'll look at the other issues.

**jaslake** · 01-27-2011, 03:21 PM

Hi

Here's some code I use to get the Open File Dialog

Please Login or Register  to view this content.

I'm still confused regarding how many workbooks you're using, what they're called and how/when you're opening them. Perhaps you could detail that for me.

**jaslake** · 01-27-2011, 03:50 PM

Hi Fo_Fa

To make this generic

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

The same would hold true of the other Workbooks.

**jaslake** · 01-27-2011, 04:19 PM

Hi Fo_Fa

Here's more complete code that incorporated the Open File Dialog, does a copy/paste and closes the file, all generic.

Please Login or Register  to view this content.

**Fo_Fa** · 01-27-2011, 05:28 PM

Instead of using hard coded sheet names (like "Sheet1") can you add the sheet, but instead of specifying the name, allow it to default, and capture the sheet name and store it. Use the stored reference in other macros?

**snb** · 01-27-2011, 05:55 PM

NB. Avoid 'select' & 'activate' in VBA
'Chdir' is redundant in VBA most of the time
Avoid 'copy' if only values have to be the same in 2 places

Please Login or Register  to view this content.

Use workbook("name") instead of Windows("name")
Use application.screenupdating =False, application.enableevents=false & application.displayalerts=false.

**jaslake** · 01-27-2011, 06:11 PM

Hi Fo_Fa

Yes you can do this

allow it to default, and capture the sheet name and store it. Use the stored reference in other macros?

Use this

Please Login or Register  to view this content.

Where SheetName becomes a variable that you can

reference in other macros

However, search on "Life of a variable Excel VBA". Depending on HOW you Dim "SheetName" and WHERE you Dim "SheetName", it's use as a variable is limited from the immediate procedure to all procedures.

**jaslake** · 01-27-2011, 06:13 PM

Hi snb

Thanks for your input...your criticism is valid...and I'm well aware of the points you raise. My immediate concern is getting the OP some usable tools...the OP and I can clean them up after we have them working.

**snb** · 01-27-2011, 06:19 PM

consider:

Please Login or Register  to view this content.

In this case there's no need to store the name. You can always refer to it, using sheets("last_sheet")

**jaslake** · 01-27-2011, 06:21 PM

Hi snb

Good point...I like it.

**jaslake** · 01-27-2011, 06:27 PM

Hi Fo_Fa

If you decide to go with snb's "last_sheet" naming convention, you'll need to include code to either delete "last_sheet" after the current run or check for it's existence. Either approach is rather simple to deal with.

**snb** · 01-27-2011, 06:35 PM

Instead of

Please Login or Register  to view this content.

This would suffice:

Please Login or Register  to view this content.

As alternative you could consider a databasetable in the last sheet that reads the data from the payroll-workbook.

**Fo_Fa** · 02-01-2011, 03:03 PM

Why is it the simplest things seem so complicated at times?

Please Login or Register  to view this content.

Throws error shown in Temp.jpg

If I run it this way

Please Login or Register  to view this content.

Throws the error shown in Temp1.jpg

**shg** · 02-01-2011, 03:12 PM

Object variables require Set:

Please Login or Register  to view this content.

**jaslake** · 02-01-2011, 03:15 PM

Hi Fo_Fa

Try

Please Login or Register  to view this content.

Why do you have AddedWs set as Public?

**Fo_Fa** · 02-01-2011, 03:52 PM

Object variables require Set:

LOL, Banging head on desk, I knew that, just working with the excel code made me stupid momentarily. The, I don't understand this bit, so I'm going to forget that other bit as well, syndrome.
Thanks guys.

**Fo_Fa** · 02-01-2011, 03:56 PM

Why do you have AddedWs set as Public?

Actually I have it in the declaratives section, and want to make sure it is available to all my subs as I have them broken up to handle separate functions, basically.
Might change that up later, once I get it all working. Don't like to change too much at one time, or you don't know what broke something.

**jaslake** · 02-01-2011, 04:01 PM

Hi Fo_Fa

Remembering, forgetting, being stupid...wait'll you get old and grey...it becomes part of the daily routine.

How you coming with the Add-In issue?

**Fo_Fa** · 02-01-2011, 04:21 PM

Remembering, forgetting, being stupid...wait'll you get old and grey...it becomes part of the daily routine.

Already Old and Grey, no stranger to it.

How you coming with the Add-In issue?

It is coming nicely now. had not been back to it for a few days due to other more pressing issues, but time is almost out also.
But looks like a couple "quirks" I managed to put in, get by those, should all be good.

**jaslake** · 02-01-2011, 04:25 PM

Great news (not about "Old and Grey") 'bout the Add-In. Frustrating "stuff", Add-Ins...wonderful tools. Need my input, holler.

**snb** · 02-01-2011, 04:28 PM

Mind the brackets; you don't need any variable:

Please Login or Register  to view this content.

gives an error

Please Login or Register  to view this content.

doesn't
and MS invented With.... End With to prevent objectvariables and set statements (mind the brackets again):

Please Login or Register  to view this content.

**Fo_Fa** · 02-01-2011, 04:41 PM

OK, something else I am not finding

Please Login or Register  to view this content.

Causes a popup window to appear and ask if you really want to.
Can I NOT have that popup window window appear?
I tried adding False after it, it didn't like that.

**snb** · 02-01-2011, 04:42 PM

Please Login or Register  to view this content.

**jaslake** · 02-01-2011, 04:59 PM

Set

Please Login or Register  to view this content.

back to

Please Login or Register  to view this content.

when finished with your delete.

**Fo_Fa** · 02-01-2011, 05:19 PM

man all these stupid little tweaks.

I have a cell I need to copy to every cell in a column with data rows.
How ever this column I am coping to, is empty.
So I am doing a Range(Selection, Selection.End(xlDown)).Select
and it copies it all the way to the bottom (1.5 million rows basically)
How can I limit it to just the rows (in other cells) that have data.
In my test spreadsheet, 1200 rows?

**jaslake** · 02-01-2011, 05:40 PM

Hi Fo_Fa

Firstly, unless an issue is related to this Thread, you probably shouldn't be asking it in this Thread. Start a new one. You'll get hammered for this.

Secondly, to address your question. Find the last row with data; this can be done several ways; these are JB's preferred methods

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I prefer

Please Login or Register  to view this content.

when I KNOW what column has the last row of data I'm interested in.

You can then do something like this

Please Login or Register  to view this content.

Mind you...you typically don't need "Select" in your code.

**aclawson** · 02-01-2011, 06:12 PM

This might be a bit of a long shot, but it works in a macro workbook I created for distribution to about 30 users. I wrote it in Excel 03, but it works fine in Excel 07.

Please Login or Register  to view this content.

I'm not 100% certain that will work in an add-in file (works great in a regular xls or xlsb). Good luck.

**aclawson** · 02-01-2011, 06:15 PM

Additional Face Id's

**aclawson** · 02-01-2011, 06:16 PM

One more face id file

**jaslake** · 02-01-2011, 06:27 PM

Hi aclawson

Sorry...I don't follow the connection...perhaps Fo_Fa does...

**aclawson** · 02-01-2011, 06:53 PM

Sorry about the non-sequiter there. I was following the frist page of the thread (didn't see there were three more pages of back-and-forth after it). It was my attempt to help with the toolbar question Fo_Fa asked early in the thread.

After I posted, I saw the end of the thread and realized I came in a bit behind the curve. Either way, it's been useful code to me.

Macro Distribution

LinkBack

Thread Tools

Rate This Thread

Display

Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Re: Macro Distribution

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions