Calling Excel functions from vbscript

TheMSsForum.com: The Microsoft Software Forum

Hi

I'm using the following vbscript to call a function in an excel workbook. I
get the following error message: The macro 'MyFunction' cannot be found.

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Dim xlApp
Dim xlBook
Dim strReturn

Set xlApp = CreateObject("Excel.Application")

Set xlBook=xlApp.Workbooks.Open("c:\data\bok7.xls")

strReturn=xlBook.Application.Run("MyFunction")

Set xlBook = Nothing
Set xlApp = Nothing
Response.Write strReturn
</Script>

I've also tried:
strReturn=xlBook.Application.Run("bok7.xls!MyFunction"), resulting in the
same error.


MyFunction in bok7.xls is a testfunction and looks like this:

Function MyFunction() as String
MyFunction="Hi"
End Function

What am I doing wrong?

TIA
PO
Top

Re: Calling Excel functions from vbscript by Ray

Ray
Wed Aug 18 10:32:13 CDT 2004

> What am I doing wrong?

1. Is that macro in the VBA code for that workbook? Or perhaps it's in
your personal.xls file, but you didn't notice that.
2. Using the Excel object in server-side code is ill-advised.
http://support.microsoft.com/?kbid=257757

Ray at work


"PO" <po> wrote in message news:uu0wReThEHA.4064@TK2MSFTNGP12.phx.gbl...
> Hi
>
> I'm using the following vbscript to call a function in an excel workbook.
> I
> get the following error message: The macro 'MyFunction' cannot be found.
>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> strReturn=xlBook.Application.Run("MyFunction")
>
>



Top

Re: Calling Excel functions from vbscript by PO

PO
Wed Aug 18 13:15:54 CDT 2004

Hi Ray and thanks

After reading article 257757 I'm convinced that I should try an other
approach. I found article Q198703 which suggests client-side scripting.

The example code starts the clients Excel and places the data in a
worksheet. Is it possible to start Excel in a frame in IE instead and
display the worksheet (I don't want the user to have to "jump" between Excel
and IE), and how do I accomplish that?

Regards
PO




"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OooO2hThEHA.1184@TK2MSFTNGP12.phx.gbl...
> > What am I doing wrong?
>
> 1. Is that macro in the VBA code for that workbook? Or perhaps it's in
> your personal.xls file, but you didn't notice that.
> 2. Using the Excel object in server-side code is ill-advised.
> http://support.microsoft.com/?kbid=257757
>
> Ray at work
>
>
> "PO" <po> wrote in message news:uu0wReThEHA.4064@TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I'm using the following vbscript to call a function in an excel
workbook.
> > I
> > get the following error message: The macro 'MyFunction' cannot be found.
> >
> > <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> > strReturn=xlBook.Application.Run("MyFunction")
> >
> >
>
>
>


Top

Re: Calling Excel functions from vbscript by Ray

Ray
Wed Aug 18 18:39:45 CDT 2004

The quick and dirty way is to just set the content type of your response to
be an spreadsheet.

<%
Dim i
i = Request.Querystring("excel") & "0"
If CBool(i) Then Response.ContentType = "application/vnd.ms-excel"
%>
<a href="./?excel=<%=CInt(Not CBool(i))%>">Change Me</a>
<table>
<tr>
<td>Cell A1</td>
<td>Cell B1</td>
</tr>

<tr>
<td colspan="2">Cell A2, as A2 and B2 merged</td>
</tr>
</table>

Note that this may or may not open in your browser window based on settings
on YOUR machine, which you will not be able to control on other people's
machines - not via ASP anyway.

(
Windows Explorer -- Tools -- Folder Options -- File Types -- XLS --
Advanced:
Browse in same window: True
Confirm open after download: False
)

Ray at home


"PO" <po> wrote in message news:O9ZvL%23UhEHA.536@TK2MSFTNGP11.phx.gbl...
> Hi Ray and thanks
>
> After reading article 257757 I'm convinced that I should try an other
> approach. I found article Q198703 which suggests client-side scripting.
>
> The example code starts the clients Excel and places the data in a
> worksheet. Is it possible to start Excel in a frame in IE instead and
> display the worksheet (I don't want the user to have to "jump" between
Excel
> and IE), and how do I accomplish that?
>
> Regards
> PO
>
>
>
>
> "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> message news:OooO2hThEHA.1184@TK2MSFTNGP12.phx.gbl...
> > > What am I doing wrong?
> >
> > 1. Is that macro in the VBA code for that workbook? Or perhaps it's in
> > your personal.xls file, but you didn't notice that.
> > 2. Using the Excel object in server-side code is ill-advised.
> > http://support.microsoft.com/?kbid=257757
> >
> > Ray at work
> >
> >
> > "PO" <po> wrote in message news:uu0wReThEHA.4064@TK2MSFTNGP12.phx.gbl...
> > > Hi
> > >
> > > I'm using the following vbscript to call a function in an excel
> workbook.
> > > I
> > > get the following error message: The macro 'MyFunction' cannot be
found.
> > >
> > > <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> > > strReturn=xlBook.Application.Run("MyFunction")
> > >
> > >
> >
> >
> >
>
>


Top

Re: Calling Excel functions from vbscript by Ray

Ray
Wed Aug 18 18:40:34 CDT 2004

Oops. My cells should have started on row 2, not 1.

RAy at home

"PO" <po> wrote in message news:O9ZvL%23UhEHA.536@TK2MSFTNGP11.phx.gbl...
> Hi Ray and thanks
>
> After reading article 257757 I'm convinced that I should try an other
> approach. I found article Q198703 which suggests client-side scripting.
>
> The example code starts the clients Excel and places the data in a
> worksheet. Is it possible to start Excel in a frame in IE instead and
> display the worksheet (I don't want the user to have to "jump" between
Excel
> and IE), and how do I accomplish that?
>
> Regards
> PO
>
>
>
>
> "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
> message news:OooO2hThEHA.1184@TK2MSFTNGP12.phx.gbl...
> > > What am I doing wrong?
> >
> > 1. Is that macro in the VBA code for that workbook? Or perhaps it's in
> > your personal.xls file, but you didn't notice that.
> > 2. Using the Excel object in server-side code is ill-advised.
> > http://support.microsoft.com/?kbid=257757
> >
> > Ray at work
> >
> >
> > "PO" <po> wrote in message news:uu0wReThEHA.4064@TK2MSFTNGP12.phx.gbl...
> > > Hi
> > >
> > > I'm using the following vbscript to call a function in an excel
> workbook.
> > > I
> > > get the following error message: The macro 'MyFunction' cannot be
found.
> > >
> > > <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> > > strReturn=xlBook.Application.Run("MyFunction")
> > >
> > >
> >
> >
> >
>
>


Top