# Solved: Office 2007 Macro Help please.



## 3DollaBillz (Jan 10, 2008)

Hello,
Well I have a problem and hope that you have the solution. I tried searching and was unable to locate anything relevant to my issue. So here we go...

We primarily use Office 2k3 and everything works fine. We are in the slow process of migrating to 2k7 and are experiencing some heart burn. The major issue of the day deals with macros. We have created a macro designed to sweep what ever it is attached to into some imaging software in an event to go paperless. We have copied it into Excel 2k3 on a blank spreadsheet and named it 'Index Spreadsheet'. When the button is pushed it quite nicely comes up and performs as mentioned, everytime after the inital install. (I hope this is making sense) so now we fast forward to the present Excel 2k7 and I have converted the macro to a .xlsm and tested it on that new spreadsheet. Worked perfectly. I then added the button to the Quick Launch Bar and tested. Worked perfectly. I then closed the blank spreadsheet and opened a new one and there my button sits. I give it a little tappy tap and BAM  'Cannot run the macro 'Index_Excel'. The macro may not be available in this workbook or all macros may be disabled.'. Well, I have enabled ALL macros so I know that is not it. I have read the web and my brain hurts. PLEASE for the love of god HELP ME!

Thanks in advance,
Jeff


----------



## Zack Barresse (Jul 25, 2004)

Hi there, welcome to the board!

So your macros are in a specific workbook? And you close the workbook, with a button on the QAT pointing to that macro, then when you click on it the macro does not run? Or do you have the aforementioned workbook (containing) the macro open? Is this something you want available all the time? If so, I suggest you make it an add-in. And having an add-in with Excel 2007 will mean you want to store it somewhere in a trusted location, meaning you should set the location of the add-in as a trusted path. I generally use C:\WINDOWS\ADDINS, because it's 'stupid simple', which I need to make things for myself. 

Also, if it is a question of whether the code executes consistently (as opposed to a workbook issue as mentioned above), runs efficiently, or runs smoothly/correctly in the 2007 version, you would need to post your code along with the expected results (and if you do that a sample file would be nice too).

HTH


----------



## 3DollaBillz (Jan 10, 2008)

Zack, you are THA MAN!!!!! Maybe I skipped right over Add-Ins because it sounds SOOOO much like a macro, but either way, you totally saved my day. Thanks! Jeff


----------



## Zack Barresse (Jul 25, 2004)

Great, glad I could help!!

Don't forget, you can mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action. 

Take care!


----------



## Rollin_Again (Sep 4, 2003)

Zack,

Where are your manners.....You didn't welcome our new friend to the TSG forums. (Just kidding w/ you)

Nice to see you around again. I've noticed you periodically go missing in action for brief periods of time. Good to see you back.

Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

LOL! Yeah, sometimes it gets too much and my postings (unfortunately) is generally what suffers (it's the easiest to trim down). I've been putting together a rather large basic fire academy course structure and it has been extremely time consuming. With my schooling and a few other things going on, it's been horribly busy. I like stopping by when I get the chance though, TSG is really relaxing to me.


----------



## 3DollaBillz (Jan 10, 2008)

Okay, it is not entirely taken care of. I have managed to get the button to launch the macro on the new Excel spreadsheets I create, BUT not the existing one that have already been created which need to be swept onto the imaging software.  
_EDIT: I am testing more because it looks like it only changes the one on my start menu. I made a change saved and closed it. Opened a new blank spreadsheet and there was my change...uncool_ 
_EDIT #2: After further investigation, all I have accomplished is to save the Macro_Name enabled spreadsheet onto the Start Menu. When I actually click the Office Button and NEW, I get a nice clean virgin spreadsheet that has the Macro_Name button on it, but when it is pressed "Cannot run the macro "Index_Excel". The macro may not be available in this wookbook or all macros may be disabled" Back to the drawing board.._

In an effort to be helpful to the masses, this is what has been done so far:
(Please note) the Macro_Name button was put on the QAT during previous attempts:
Copied macro_name.xlt onto desktop.
Open macro_name.xlt and saved as macro_name.xlsm into the c:\Doc & Setting\userid\App data\Microsoft\Addins
Close macro_name.xlsm
Opened new blank spreadsheet > Clicked on Office Button > Excel Options > Add-Ins
At the bottom where is says MANAGE I changed that to Excel Add-ins and pressed GO
Add-Ins box opens and clicked Browse, changed Files of type to .xlsm and selected macro_name.xlsm
Clicked Ok to close Add-Ins box and closed the spreadsheet.
Opened new spreadsheet, clicked Macro_Name button and it worked perfectly...and I sent the well deserved credit to Zack (thanks again :up: ) thinking I had won the war. As it turns out I only won a back all brawl but someone still made off with my wallet.

So in closing, I need the Macro_Name button to work on ALL Excel spreadsheets, not just the ones I have created.

Thanks and if you have any further advice or see what I did wrong, please feel free to explain.
Jeff


----------



## 3DollaBillz (Jan 10, 2008)

Apparently Macros that need to run in all workbooks in EXCEL 2007 have to be placed in the PERSONAL.XLS folder. Now your NOT going to find the PERSONAL.XLS until you record a macro and save it in the personal.xls folder because that is when it is created. I found these instructions that were VERY helpful:
Creating and Using a Personal.xls Workbook

The best place to store macros that are general purpose in nature and to have them available all the time is to store them in your Personal Macro Workbook. This is a (normally) hidden workbook that is loaded automatically by Excel. When you record a macro, you have the option of recording it to your Personal Macro Workbook. The file, Personal.xls, is stored in your \XLStart directory. The Personal.xls file doesn't exist until you record a macro to it.

The start directory used by Excel in WIn2K is

C:\Documents and Settings\Laurence Holbrook.AT4107680452\Application Data\Microsoft\Excel\XLSTART

The following directory is also used

C:\Program Files\Microsoft Office\Office\XLStart

The easy way to create a personal.xls workbook is to record a macro to it -

· Close any and all open Excel workbooks

· Start Excel

· Click menu item Tools|Macro

· Click Record New Macro...

· Click the down arrow In the "Store macro in:" list box

· Click on Personal Macro Workbook

· Click OK

Do something simple to create the macro...

· Click in cell B1

· Press the Delete key

· Click in cell A1

· Click on the square button in the Stop recording tool

· Click the X in the top right hand corner of the Excel window to close it (or press <Alt><F4>)

· Click Yes to respond to "Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next you start Microsoft Excel -

· Click No to respond to "Do you want to save the changes you med to 'Book1'?

Start Excel

· <Alt<F11> to open the Visual Basic Editor

· Click the + next to the VBAProject (PERSONAL.XLS)

· Click the + next to Modules

· Click on Module1

· Click on the View Code icon at the top of the Project explorer -

You can highlight all the code there (<Ctrl>a will select all) and press delete -

Let's add a couple of potentially useful general-purpose macros -

· Highlight the following lines (you needn't include the starting and ending '-----------' lines)

'-------------------------------------------------------------

Sub CopyVisible()
'
' Macro recorded 7/1/2003 by Laurence Holbrook
'

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

End Sub
Sub PasteSpecialValues()
'
' Macro recorded 7/1/2003 by Laurence Holbrook
'

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Application.CutCopyMode = False

End Sub

'---------------------------------------------------------

· Press <Ctrl>c

· Press <Ctrl>v module1 of the VBAProject (PERSONAL.XLS)

· Click menu entry Window|Unhide

· Highlight Personal.XLS and Click OK

· Press <Alt><F8>

· Highlight CopyVisible and click Options

· Type g in the Shortcut key

· Click OK

· Highlight PasteSpecial and click Options

· Type f in the Shortcut key

· Click OK

· Click X to close the Macro select window

· Click menu entry Window|hide

[Open any other workbooks you may have that contain 'general purpose' modules and copy the 'general purpose' code from those workbooks and paste into the Personal.xls Module1]

· Close Excel

· Click Yes to respond to "Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next you start Microsoft Excel -

· Unless you made changes that you want to keep to any of the other workbooks, you can click No to any "Do you want to save the changes you med to 'xxxxx'? that appear

The next time you open Excel you will have added two more 'copy and paste' type functions - <Ctrl>g copies only visible cells - <Ctrl>f does a PasteSpecial Values - both work in conjunction with the normal <Ctrl>c, <Ctrl>v, <Ctrl>x functions -

You can use <Ctrl>c to copy some cells that have calculated values, select a destination and <Ctrl>f will copy the cell values to the destination instead of the formulas -

Compare <Ctrl>f to

· Click menu entry Edit

· Click Paste Special

· Click the Values option button

· Click OK

You can us <Ctrl>g to copy only visible cells - if you have used autofilter to 'hide' cells you are not interested in and display cells that you are, you can highlight the visible cells and <Ctrl>g will copy just those cells that you can see and will ignore the cells that are hidden - <Ctrl>v will paste just those cells to a new destination

Note: In the future, as you close workbooks you may get asked if you want to save changes to your Personal.xls workbook - unless you have made a change that you know you want to save, I would generally recommend clicking "No"

GOD BLESS THE INTERNET!!


----------



## Zack Barresse (Jul 25, 2004)

Billz, there is some truth to what you said, but some misgnomers as well. You do not HAVE to have the macros in your Personal.xls file, although that acts kind of like an add-in as it is open with Excel like Add-ins. It is more or less a link for people to store things in their Excel session, often used macros, static data, etc. This is not like an add-in due to the fact that you can view/run the macros from the Run Macros dialog box (Alt + F8), whereas add-in routines do not appear in that list. Personal.xls is a regular workbook, but is hidden. It is not created unless you create it. Yes, the best way to create one is to record a macro to it (the easiest/fastest IMHO), but you can create one by just saving a workbook as Personal.xls and saving it to the XLSTART folder (which only means it will open everytime you open Excel - any file placed in that folder will open with Excel as it checked that folder when starting and opens anything placed in that folder).

With the add-in, when you set the add-in, it runs the Addin_install event (in VBA), which should open the workbook. The add-in should stay installed until you uninstall it (uncheck it from the manage add-ins dialog box). The macro on your QAT _must_ point to the add-in macro, not a template (xlt) routine.  So you must have the add-in saved as an add-in and installed before you set the routine to your QAT, otherwise you'll be pointing to the wrong file/location/routine, and with the file closed, you'll receive that error.

I think this is all boiling down to just your order of operations here with saving the correct file type and assigning the routine to the QAT. Please pay strict attention to your order of operations and what you are doing when. Sometimes, when you don't have the right information, it is easy to do something and not know you've done it (lord knows I'm the king of that...  ).

HTH


----------

