I developed an Excel 2003 spreadsheet with an embedded worksheet form button with an assigned macro written in VBA. The Excel file is hosted on a web server. User will open the Excel file from a web page via IE browser.
Customer's Thailand branch office says it doesn't work. So far we tested with Excel 2003 in Singapore branch and no problems at all. I found out that the Thai branch users are running Excel 2000, but don't know if there are any SRs applied. When they click on the embedded worksheet form button it says...
"The macro <xxxxx> cannot be found."
where <xxxx> is the correct & complete path of the assigned macro.
It seems to me that the macros perhaps somehow "disappeared" when users open in Excel 2000 in web browser. We have tried the following but doesn't help:
- Set macro security to LOW
- Reprogrammed embedded worksheet form button to point to the full complete macro path
- Clear browser cache
What else to check Any advice would be appreciated.
Thanks.
Randy Foo
randy@biz-process.com
Excel 2000 - macro not found
Blinky Bill
Thank you very much for your detailed explanation and workarounds.
Best Regards,
Randy
Tom Z.
Hi Randy,
Here's some info from our support engineer:
I found a case,
https://vkbexternal.partners.extranet.microsoft.com/VKBWebService/ViewContent.aspx portalId=1&filePath=file://B11VKBFILE/SRCatalog/US/03/12/03/Z002155.xml&contentType=ASR&displayTermHighlighting=true&promoteToEnglish=true&displayXMLForm=false&searchTerms=%3cSearchTerms%3e%3cTerm+Text%3d%22%26quot%3bThe+macro%26quot%3b%22+%2f%3e%3cTerm+Text%3d%22%26quot%3bcannot+be+found.%26quot%3b%22+%2f%3e%3cTerm+Text%3d%22excel%22+%2f%3e%3cTerm+Text%3d%222003%22+%2f%3e%3cTerm+Text%3d%222000%22+%2f%3e%3c%2fSearchTerms%3e&displayActions=true&sessionId=105714591261&sequence=17
His issue is similar with ours.
Problem Description: When opening a spreadsheet that contains macros from a web
site (via plumtree portal) pressing any button with attached macro gives the error.
For example [The macro "fileopen.asp ID=11660'!Waltham' cannot be found]
Resolution:
===========
This is a bug in Excel 2000 that was fixed in Excel 2002. For Excel 2000, the
following 2 workarounds can be tested (in addition to saving the file to the local
machine):
Workaround 1: Use 2 ASP pages and session variables. The first asp page receives
the querystring and sets a session variable and calls the second asp page. The
second asp page reads the session variable to find out which ID, file, etc. to
stream and then streams that file. In this way, Excel does not receive any
querystring as the workbook name because its opening the second asp page, not the
first one. For example, here are 2 ASP pages: Default.asp and FileInfo.asp. The
user calls: Default.asp ID=MyBook.xls to open the MyBook.xls file:
Default.asp
-----------
<% @LANGUAGE=VBScript %>
<%
Session("Filename") = Request.Querystring("ID")
strRedirect = "FileInfo.asp"
Response.Write("<META HTTP-EQUIV=Refresh CONTENT=""0;URL=" + strRedirect + """>")
%>
FileInfo.asp
------------
<% @LANGUAGE=VBScript %> <%
sFile = Session("Filename")
Response.ContentType = "application/x-msexcel"
Const adTypeBinary = 1
Dim strFilePath
strFilePath = "C:\inetpub\wwwroot\kenneth\" & sFile
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
%>
To use session variables, make sure Office 2000 SR1 or later is installed as per
the following KB article:
264143 - FIX: ASP Session Variables Empty When Office 2000 MIME Types Are Streamed
with Internet Explorer
http://support.microsoft.com/default.aspx scid=KB;en-us;264143
Workaround 2: Force the Excel file to open outside of the Internet Explorer browser
for all users by adding the Content-Disposition header in the original ASP page as
follows:
Response.AddHeader "Content-Disposition","attachment;filename=Test.xls"
For example:
FileInfo2.asp
-------------
<% @LANGUAGE=VBScript %>
<%
sFile = Request.Querystring("ID")
Response.ContentType = "application/x-msexcel"
Response.AddHeader "Content-Disposition","attachment;filename=Test.xls"
Const adTypeBinary = 1
Dim strFilePath
strFilePath = "C:\inetpub\wwwroot\kenneth\" & sFile
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
%>
I think it can help our ISV. Thanks.
-brenda (ISV Buddy Team)