Macro shortcuts dont show up in Ribbon

[stevebrugel]

Can someone provide a work around or explain how to make macro available when Excel is initiated from "other software"
All locations I have tried do not "initiate" Excel. I am basing this on the ghost image of the Workbooks that normally appears when you open Excel from the start menu or double clicking on any Excel file.
This ghost image is not seen when you have the 3rd party open Excel.

[satyen]

If this is what I think it is read up on QAT file. Theres are some good links on the web for this. You'l have to customise the file following the instructions online. It is stored somewhere in your C: drive. Once customised the macro created on the Quick access toolbar will be available to run.

[stevebrugel]

While you can add icons to the QAT this way, the macro still will not run. This appears to be true due to the fact that the macros are not available.
Here are a couple steps I am taking to reproduce the error.
Using this page as a guinea pig "__________w3schools.com/Html/tryit.asp?filename=tryhtml_intro" using IE 7 Right click, brings up window selection box, which has a "Export to MS EXCEL". Select it, it opens what appears to be EXCEL but I think its only an Object or Instance (not sure) but if you look at any files in the XLSTART folder they have not been Initiated (hence no ghosting of the Excel WB's
Thoughts

[Bob Phillips]

I can't see this 'Export to MS Excel' option.

[stevebrugel]

It only appears if you right click on ?? HTML ?? DATA, otherwise it is not included in MS window

[Bob Phillips]

The data you are grabbing is not an Excel workbook, it is an HTML page, rendered to XML and imported into Excel, so it won't have any macros.

[stevebrugel]

Thank you for your quick reply. This explains the reason but I am still looking for a way to fire a macro (ie with a button in QAT or something similar).
In the past I have set something up in the personal folder, which opens a document on a network which parses the ‘xml’-Excel file. I set up several users, and the users keep growing. I would like to give them a .xlam or .xlsm and allow them to put in a XLSTART folder or equivelant.

[Bob Phillips]

Seems a viable approach. So what is stopping you doing that?

[stevebrugel]

I have tried .xlam .xlat .xls .xlsm but get the same result with each giving an error cannot run specified macro blah blah

[Bob Phillips]

I really have no idea what you mean, so you will need to be far clearer and more descriptive.

[frank_m]

(Bug in Excel 2007)
I had a similar issue before where I would create an .xlsm workbook with several QAT buttons with Macros assigned,
and when I would email the workbook the user would get the message that the macro could not be found. - After further investigation I found that this would also happen on my pc if I saved the workbook to a different location or with a new name.

The solution was to go into the customUI.xml file in the userCustomization folder inside the .xlsm file, and for every button remove the macros's path
leaving only the macro name in the idQ and onAction properties

When making those changes it's crucial that you leave the idQ property prefixed with "doc:" and the suffix "_1" (idQ="doc:MacroName_1")
Detailed instructions here:
http://www.pcreview.co.uk/forums/excel-2007-macro-quick-access-toolbar-not-working-t3834831.html
the 3rd from last post by member si24392

[stevebrugel]

Thanks all for weighing in on this. I will try to step by step explain this to try to find a solution. This code pasted in WORD with a reference to Excel (in the tools tab) gives the same result as what I described in earlier post.
Sub DialogOpenXLFile()
Dim xlwb As Excel.Workbook
Dim CompleteFilePath As String
Dim What2Open As String
Dim xlapp As Excel.Application
'//make a junk file and place it anywhere to open with the following
CompleteFilePath = "C:\Documents and Settings\Administrator\My Documents\Trial.xlsx"
What2Open = "Open The Customers Test PLAN" 'just a Title
Set Myfd1 = Application.FileDialog(msoFileDialogFilePicker)
With Myfd1
.AllowMultiSelect = True 'false
.Title = What2Open
.InitialFileName = CompleteFilePath
.Filters.Clear
.Filters.Add "ALL Files", "*.*"
.Filters.Add "Excel 2000-2003", "*.xls"
.Filters.Add "Excel 2007", "*.xlsx"
.ButtonName = "Open DSS"
If .Show = -1 Then
What2Open = .SelectedItems(1)
Set xlapp = New Excel.Application
xlapp.Visible = True
Set xlwb = xlapp.Workbooks.Open(What2Open)
End If
End With
Set Myfd1 = Nothing
End Sub

If you have any shortcuts in the QAT I am guessing they will not work, and if you had any CustomUI icons in the Ribbon they will not be there either. Thoughts

[frank_m]

I would like to give them a .xlam or .xlsm and allow them to put in a XLSTART folder or equivelant.
I was under the impression by what I have quoted above, that you would be able to distribute an .xlsm workbook to the users.

See attached sample workbook where I have three QAT Buttons assigned to different macros, and I have made the .xml file edit's I described previously

The fact that you are opening the file using automation from another program shouldn't matter

[stevebrugel]

to XML and imported into Excel, so it won't have any macros.
I think xld hit it on the head with what he said here, but I seem to have lost him with my jargon in later post. Using your macro in a startup location gives me access to the three buttons in the QAT --- starting excel in a normal way. But even your shortcut buttons disappear from view when you right click - import to Excel.
I had only provided the code to show "that it gives the same result"
Still looking for a workaround
If you havent tried to import any HTML data please do so (using the right click method) If seeing is believing you will see what i mean
Thanks for the .xlsm BTW, I will get some miles out of it
Steve

[Bob Phillips]

Steve,

I did import the HTML as you said, but I didn't see what you mean I am afraid.

[stevebrugel]

For clarification I have tried this on xp vista and Win7 using IE7 or IE8 with the same results. Recap....
Using Frank_M's Test TestQATmacros.xlsm installed in a XLStart folder
close all instances of XL
Start XL from start menu, and macro shortcuts appear in QAT.
close XL
Start XL by right click on HTML Data -- import data -- the new XL sheet has NO shortcuts. (or any customUI Ribbon Icons)
Are you using IE for a browser.
I also ran the same scenario leaving the first instance of XL open and then Rclick on the HTML and this opens a second instance (again with no Icons in the second opened Wb, the first still displays QAT shortcuts)
Being new to the forum I just saw that an image can be attached, would that be helpful for future reference???
Steve

[frank_m]

Hi Steve,

Thought I'd mention what I have described below so you know that I did replicate the issue of loosing the QAT and macros.

I'm not too familiar with importing xml or html and the so called "right click method" that you mentioned, as I don't know what that is, but I did import an html table from the web after starting Excel from the start menu (of course after I put may QAT sample file in the xlstart folder) - That worked ok, but if click New>blank workbook from within an instance of Excel, the QAT images and macros are all lost regardless of whether or not I import anything.

Apparently there is a different "new workbook template" used with different ways of starting Excel. - Automation I don't think has much to do with it.

I realize that this has been mostly all information that you already have determined, but sometimes the "littlest thing" can spark a solution from the more advanced minds... "Minds" such as Bob's for example :bow:

[stevebrugel]

Thanks for the update..I was beginning to wonder if I was going crazy.
What you described is basically what I am seeing, with the exception that if no instance of XL is running and you start it via the method mentioned above you would note that no XL Wkbk in any startup folder makes a ghost image (only seen if you go to folder options and show all hidden folders including system files). Start XL from start menu and see ghost images (I mention this b/c if they dont "ghost" you dont have access to the macros contained within
Steve

[frank_m]

When you say "ghost image" do you mean a template is open but hidden from view?

I haven't been able to locate the Excel Blank workbook template. - by googling I'm under the impression that it should be book.xltx (for Excel 2007) and the path C:\Users\UserName\AppData\Roaming\Microsoft\Templates\book.xltx
-- I do have hidden files and folders, as well as system files and extension's all set to "show" in Folder Options

[stevebrugel]

~$TestQATmacros.xlsm
This is the ghost file name, the image(XL Icon) shown looks like the original just washed out

[frank_m]

No time to play around with it now, but I feel fairly confident that there is a method in which an .xlam add-in could be used instead of an xlstart item, so you would have your default QAT Images and macros directly within any new .xlsm blank workbook.

Take a look at this VBAX Link for instructions about creating an add-in. (it covers both 2003 and 2007 add-in's)
http://vbaexpress.com/forum/showthread.php?t=10855

[frank_m]

I got around to tinkering with this.

I truly believe that if you follow the steps that I've outlined below, you should be able to achieve your goal, assuming that you can live with the fact that every workbook, (not just new ones), will have your custom QAT (macro linked) images

(1) Save the QatTemplate.xlsm as QatTemplate.xlam (name it anything that’s easy to remember)
(2) If Vista, Save to C:\Users\YourUserName\AppData\Roaming\Microsoft\AddIns\QatTemplate.xlam
(ok to save to another location, but saving here eliminates browsing to it later)
(3) Close Excel, then re-open it,
(4) click the Office button
(5) At the bottom of the dialog click Excel Options
(6) From the list on the left side, select Add-Ins
(7) At the button left you’ll see a Manage drop-down, select Manage Excel Add-ins,
(8) click the Go button.
(9) A list will appear, put a check mark next to QatTemplate (or whatever name you gave the add-in)
(10) click ok

Now no matter how you open a new blank workbook, the default blank workbook should have the Qat images and be able to run the macros from the QatTemplate.xlam even if your actual workbook is saved as an .xlsx (non-macro enabled file type)
To update the macros or custom QAT, just replace the QatTemplate.xlam with a new version with the exact same name in the exact same location.

Note: It is possible to write an installer program such as the highly respected free Inno Setup to install the add-in

[stevebrugel]

I will be out for a bit but will check in later.
I have already tried the method above with no luck, same fail as before. I may post a video on YouTube lest you start to think I am crazy

[frank_m]

Hi Steve,

(although seeing it all on youtube would be a blast), I don't think it will be worth the time it would take) Don't take anything I say with much more than a grain of salt. - If you state that you followed the steps as I described and used the QAT sample workbook that I provided, than neither I nor anyone here will doubt that you did it correctly.

-- That's my first add-in, by the way, and I have the need for one coming up soon, and it all will work with a customized ribbon as well, (as I read about both that and the QAT at the rondebruin site), so I'm gaining from the googling and tinkering.

Hopefully Bob or another expert will weigh back in on to this soon.

[stevebrugel]

Thanks again for your reply, I had another thought but have no knowledge of where to find the answer to this latest thought.
Goes like this, When I right click on HTML material (using IE7 or 8, but not Chrome, it doesn't work) something in IE7 or 8 senses the material is HTML and gives different options like 'Export to MS Excel' the calling from this menu it would seem is what really needs to be addressed, forgive my wording -- but I think it is setting only a object with all the properties of an excel workbook, but it is not really firing the excel '??.exe??'. I think thats why I do not see the other .xlam or .xlsm actually open.
In other words, IE is using excels .dll to create an object but really isn't opening excel in the traditional way
Thoughts Steve

[frank_m]

Hi Steve,

Can I get you to try exporting this page into Excel? http://www.x-rates.com/d/USD/table.html
because when I right click on it and choose Export to Microsoft Excel, nothing happens.

From a little checking I did just now on google, apparently some Html web pages can be exported into Excel using that right click menu option in Internet Explorer and certain others cannot, apparently an issue that has to do with what programing language was used to create the web page.

If you are able to Export the page I gave to Excel, then I have no idea why I can't, so I won't be able to comment about that.

But, if you also cannot export that page to Excel, please supply me with the URL to a page where you are able to export it to Excel so that I may try it?

[stevebrugel]

Using IE7, http://www.w3schools.com/html/tryit.asp?filename=tryhtml_intro on RH side of page click on "My First Heading"

[frank_m]

HI Steve,

Unfortunately I only have IE 8 running on Vista and when I right click and select Export to Excel nothing is happening. I set all the IE security settings to the lowest setting, as well as set to allow all cookies, but still no luck.

Does the alternative method described at the link below help ? all:http://www.mrkent.com/tools/converter/

[stevebrugel]

Not that I am trying to fix your problem, just trying to get to the borrom of mine. The last reply i sent I was using IE7 with XP, this computer is also running IE8 and vista...so it is possible for your computer to have the funtionality, found this site http://www.winhelponline.com/blog/add-export-to-microsoft-excel-to-the-right-click-menu-in-internet-explorer/ which restores that function. something else to check on your cmp is in the tool menu under internet options. You can set Excel to handle HTML as well as others. What were your thoughts or experiance with other programs opening Excel
Steve

[frank_m]

Not that I am trying to fix your problem, ......... HI again Steve,

, always glad to hear advice and tips. Never know when I might need that functionality down the road. I think though that tinkering with making that fix right this minute though, isn't going to advance you towards a solution, so I'll get around to that on another day.


...... What were your thoughts or experience with other programs opening Excel
Steve Well, I'm nearly sure that if I run a test automating Excel from MS Word, that any new workbook I would create would have the QAT if I have my test add-in installed.
(I'll be the first to admit, though that I could very well be wrong about that)
Sorry to make you repeat yourself, but when you opened excel using ms word using the code that you posted previously, are you saying that new workbooks that you create that way do not Have the QAT if an add-in like my sample is installed? - If you are saying that, as I said I will be surprised but I will believe you. Still though if that is what happens to you, I would like to try it, just to make sure that it's not a pc specific issue. Unfortunately if I do test that out, I will have to re-install the test add-in again, as I just finished restoring my pc to a backup image to get back the settings etc, i had tinkered with. -- I am happy to do it though.

First let me ask though that you summarize once again the exact need and goal. -- I believe you said that you have a app (other than ms word), that accesses or imports .html data, then exports it into Excel using automation. (1) Is that correct? If so, (2) is the 3rd party app getting the .html from a website, or where? (3) if so, is it getting it from that 3schools page that you provided? (4) Is it not possible to have Excel use VBA to import that data without the aid of your 3rd party app?

[frank_m]

Hi Steve,

Well at least you'll feel a little better now that I re-installed my add-in that has the custom QAT and macros, and opened a pre-existing workbook using automation from MS WORD, and the QAT is missing, but if I open the same workbook by double clicking the file, then the QAT is there and the macros available ..

It sure does seem that there would be a way to do this though.

Bob.... you there? does this make sense to you? :help
Sub Open_Excel()
'not sure if I coded this perfectly right, but it should be fine for this test I would think
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook

Set oExcel = New Excel.Application

oExcel.Visible = True

On Error Resume Next ' in case the file does not exist
Set oWB = oExcel.Workbooks.Open("F:\Test.xlsx")
'also ran code that creates a new workbook with the same result
On Error GoTo 0

Cleanup:

Set oWB = Nothing
'If Not oWB Is Nothing Then oWB.Close
'oExcel.Quit
Set oExcel = Nothing
End Sub

[stevebrugel]

To answer a couple questions from your previous post,

First let me ask though that you summarize once again the exact need and goal. -- I believe you said that you have a app (other than ms word), that accesses or imports .html data, then exports it into Excel using automation. (1) Is that correct?

YES AND NO, IT IS THE RIGHT CLICK METHOD WHICH BRINGS UP A ??? LIST ??? THAT INCLUDES BACK,FOWARD,SAVE BACKGROUND AS,SET AS BACKGROUND, SELECT ALL.............,REFRESH,EXPORT TO EXCEL,PROPERTIES

(2) is the 3rd party(THIS IS A MICROSOFT ADD ON OR JUST STD STUFF, EVERY COMPUTER I HAVE TRIED HAS IT. PLEASE NOTE THE URL I SENT THAT REFERS TO THE REGISTRY (WHICH MAKES ME BELIEVE ITS A MS THING-COULD BE WRONG). IT CAN BE DISABLED,WHICH IS MOST LIKELY WHAT HAS HAPPENED TO YOUR COMPUTER) app getting the .html from a website, or where?

(3) if so, is it getting it from (A REPORT THAT IS GENERATED BY A APPLICATION WHERE I WORK, IT OPENS UP IN IE7,OR 8 WHATEVER BROWSER THE USER HAS)that 3schools page that you provided? (4) Is it not possible to have Excel use VBA to import that data without the aid of your 3rd party app?(YES THIS IS AN OPTION, BUT WOULD CHANGE THE WAY CURRENT USERS OPEN VS HOW FUTURE USERS WOULD OPEN/IMPORT. NOT TO MENTION THE RIGHT CLICK THING IS WAY TO EASY (BAR THE SETUP OF A MACRO IN THEIR PERSONAL FOLDER)

SUMMARIZE- Do NOT want to set up a macro in xx user's computer
Do want to give them a .xlam or .xlsm with custom buttons for Ribbon and or QAT

Problem: When importing DATA via Right click method custom buttons or QAT shortcuts are not available

[frank_m]

Hi Steve,

We need to have Bob or another one of the far greater minds than mine here, to weigh in on this, but I'm nearly sure that you won't be able to find any "simple fix", such as any setting or registry change, that is going to permit for the standard I.E. right click function (Export to Excel), and result in an open workbook that has the QAT and add-in macros available. - I really think it's going to need to be a VBA solution.. There would not be any need to have anything in the XL Start folder, as all of the code would be in the add-in. - QAT macros from the add-in are able to run in a non-macro enabled workbook, such as .xlsx -- The user would likely have to first open a blank workbook by normal means, highlight the table that they want to import at the website, then right click export, but fairly close, (in my opinion), to what you want.

Regards,

Frank

[stevebrugel]

Is there a way to get the attention of Bob or "greeater minds to weigh in??

[frank_m]

Hi Steve,

In support of the likely truth that you won't find any settings that will correct your issue, See post#2 and others here:
http://www.excelforum.com/excel-programming/332757-add-in-functions-not-listed-in-automation.html

I think you said you tried already first opening a workbook that has the add-in macros available, before you do your export, and the macros from the add-in were still not available to the new workbook, correct?

Even though I'm not an expert, I'm likely correct in my opinion that you need to develop code to aquire the web-site export... either from the web site directly, or from the new workbook that is created during the 'right click export to Excel'

[frank_m]

The steps described below could be done manually, or with code.
First open a workbook by normal means, then after the user creates a new workbook by way of right clicking on the web page and choosing 'export to Excel',
go to the sheet tab, > right click > copy > select the workbook you previously opened from the drop down list, as the copy to workbook.
Then save that, and close the new workbook. (I figure you're likely already aware of this approach, but I thought worth mentioning just in case)
Are there other obstacle's to prevent easily doing the above with VBA, other than the time to develop the code? It seems to me that you could, as you already suggested that using an add-in would be ok. (* Remember, the add-in's QAT and macros will be available in the workbook that you open by normal means.)

[stevebrugel]

I thought this trail had gone cold, thanks Frank for sticking in there. Presently its late but I will comb thru this tomorrow and give a better response. Thanks for the research, I'll be back.