Trouble Adding Worksheet via VBScript

[drvice]

I'm having trouble adding a Worksheet to a Workbook via VBScript. I'm
using an ASP.NET page to generate a few tables. I'm then attempting
to "export" those tables to Excel via the script below. I get an
error on the oBook.Worksheets.Add line, the error telling me "Add
Method of Sheets class failed". Can anyone tell me why?

<script language="vbscript">
dim i
i = 1
Dim oXL, oBook, oSheet, x
Set oXL = CreateObject("Excel.Application")
Set oBook = oXL.Workbooks.Add
for each childobj in window.Form1.children
If Instr(1, childobj.outerhtml, "<TABLE") > 0 Then
If i > 3 then
oBook.Worksheets.Add NULL, oBook.WorkSheets(i-1), 1
End If
oBook.HTMLProject.HTMLProjectItems("Sheet" & i).Text =
childobj.outerhtml
i = i + 1
End If
Next
oBook.HTMLProject.RefreshDocument
oXL.Visible = true
oXL.UserControl = true
</script>

When I use the code below, I have no problems:

Set oXL = CreateObject("Excel.Application")
Set oBook = oXL.Workbooks.Add
oBook.Worksheets.Add NULL, oBook.WorkSheets(3), 1

oBook.WorkSheets(1).Name = "Number 1"
oBook.WorkSheets(2).Name = "Number 2"
oBook.WorkSheets(3).Name = "Number 3"
oBook.WorkSheets(4).Name = "Number 4"

oXL.Visible = true
oXL.UserControl = true

Help!

Dan

 

[Joel]

If you add a worksheet it creates a new workbook unless you use the before or
after

Worksheets("Sheet1").Add After:=Worksheets("Sheet3")

Your problem maybe you are creating new workbooks.

 

[drvice]

If you add a worksheet it creates a new workbook unless you use the before or
after

Worksheets("Sheet1").Add After:=Worksheets("Sheet3")

Your problem maybe you are creating new workbooks.

Joel,

Thanks for the reply.

I don't think this is the case, because the code at the bottom of my
post works just fine by adding a new worksheet to the existing
workbook. I'm using the after parameter, specifying that I want the
new worksheet to show up after the last one:

oBook.Worksheets.Add NULL, oBook.WorkSheets(i-1), 1

Could ot be the HTMLProject/HTMLProjectItems stuff that's doing it? I
don't really see any other differences between the code that does work
(at the bottom of my original post) and the code that fails.

Dan

 

[Joel]

Maybe your problem is your referencing worksheet 0 (zero). If i = 1, then
oBook.Worksheets.Add NULL, oBook.WorkSheets(i-1), 1

is worksheet(0) which would cause an error.

 

[drvice]

Maybe your problem is your referencing worksheet 0 (zero). If i = 1, then
oBook.Worksheets.Add NULL, oBook.WorkSheets(i-1), 1

is worksheet(0) which would cause an error.

I don't think this is the case either, as the Worksheets.Add will only
execute when i > 3 based on the IF statement it's contained in.

Dan

 

[gimme_this_gimme_that]

You're confusing JScript and VBScript.

JScript is like a Visual Basic alternative to JavaScript.

VBScript is a Windows scripting language (M$'s Visual Basic version of
Perl so to speak.)

You call JScript in an HTML or HTA page (ASP Technology).

You can call VBScript from JScript but the suffix on the file has to
be HTA and you have to set your security settings to allow it.

You don't need an IIS server or ASP to use HTA.

Start with getting your VBScript running on the command line.

Then google M$'s scripting newsgroups for examples on calling VBScript
(also known as WScript) from JScript in a HTA file.

I've posted an example.

 

[drvice]

You're confusing JScript and VBScript.

JScript is like a Visual Basic alternative to JavaScript.

VBScript is a Windows scripting language (M$'s Visual Basic version of
Perl so to speak.)

You call JScript in an HTML or HTA page (ASP Technology).

You can call VBScript from JScript but the suffix on the file has to
be HTA and you have to set your security settings to allow it.

You don't need an IIS server or ASP to use HTA.

Start with getting your VBScript running on the command line.

Then google M$'s scripting newsgroups for examples on calling VBScript
(also known as WScript) from JScript in a HTA file.

I've posted an example.

Huh? Did you post to the wrong thread? Your response has very little
to do with my problem.

Dan

 

[gimme_this_gimme_that]

No. Right thread.

Sorry. I figured you wanted to have users click on an ASP page and
locally create an Excel Workbook.

Since we're on different pages.

Now I figure this is what you want: When someone clicks you want to
save a brand new Excel Workbook on the server.

The code you have looks strange because: you don't the Workbook. So if
you hit the page 100 times you have 100 unsaved Workbooks.

When I've used VBScript in my stuff it's always executed the code on
the clientside.

You have "Add Method Of Sheets Failed".

If you're wanting this to occur on the server side it suggests that
you can't do what you want to do which is to create a Worksheet Object
in IIS.

If you're wanting this to occur on the client side it suggest that
there is a permission problem.

Normally when developers do what you want to do, they store data in a
database and then they handle the creating of Worksheets outside of
IIS. Then they fetch the data from the database.

Now, if what you wanted to do was create a Worksheet, but on the
client side, then, yes, you could re-read my other post and that would
be of help.

 

[drvice]

No. Right thread.

Sorry. I figured you wanted to have users click on an ASP page and
locally create an Excel Workbook.

Since we're on different pages.

Now I figure this is what you want: When someone clicks you want to
save a brand new Excel Workbook on the server.

The code you have looks strange because: you don't the Workbook. So if
you hit the page 100 times you have 100 unsaved Workbooks.

When I've used VBScript in my stuff it's always executed the code on
the clientside.

You have "Add Method Of Sheets Failed".

If you're wanting this to occur on the server side it suggests that
you can't do what you want to do which is to create a Worksheet Object
in IIS.

If you're wanting this to occur on the client side it suggest that
there is a permission problem.

Normally when developers do what you want to do, they store data in a
database and then they handle the creating of Worksheets outside of
IIS. Then they fetch the data from the database.

Now, if what you wanted to do was create a Worksheet, but on the
client side, then, yes, you could re-read my other post and that would
be of help.

Gimme...

Sorry...my bad. I didn't follow your post at all.

I have an ASP.NET page that generates ASP.NET tables. I then have
client side code (in my initial post) that takes the <table...> tags
and places them into Excel using the HTMLProject/HTMLProjectItems
objects. I want Excel to come up with the data in the sheets so I can
save them. It works fine, as long as I only need 3 sheets or less.
If I need more sheets, that's where the problem arises. The
Worksheets.Add call fails. If I don't use the HTMLProject/
HTMLProjectItems, I can create all the worksheets I want.

What's the difference?

Dan

 

[gimme_this_gimme_that]

I'm going to pass on the innerHTML thing you've got going on. It looks
like it'd work OK. My inclination would be to use the DOM that's
portable over all browsers.

Now.

Save this file, but give it a .hta suffix. I'm getting data, sort of
like your innerHTML table thing from inputs. You can convert to your
set up.

Note the call to WScript.

The WScript executes a call to from_hta.vbs which is a VBScript.

I didn't test this, but it comes from a cut and paste of something
that has been tested. I think it works.

Note that to get this to work you have to go into Excel->Tools-

Security->Medium and you have to check Trusted if you're going to be

adding Modules to the Workbook you're creating.

''''create_1111.hta

<head>
<title>1111</title>
<HTA:APPLICATION
APPLICATIONNAME="1111"
SCROLL="no"
SINGLEINSTANCE="yes"</head>

<script language="vbscript">
window.resizeto 600,220
window.moveto 100,100
</script>

<script language="VBScript">

Function GetPath(Path)
ix=InstrRev(Path,"\")
Path=Left(Path,ix)
GetPath = Path
End Function

Function CreateReport
File1 = escape(document.aForm.elements("file1").value)
File2 = escape(document.aForm.elements("file2").value)
Set WSH = CreateObject("WScript.Shell")
WSH.Run("from_hta.vbs " & Chr(34) & File1 & Chr(34) & " " & Chr(34) &
File2 & Chr(34))
Window.Close
End Function

</script>

<body bgcolor="lightblue">
<center>
<form name="aForm">
<table border="1" bordercolor="darkblue" width="94%">
<tr>
<td align="center"><font color="darkblue"><h2>1111</h2>
</td>
</tr>
</table>
<table border="1" bordercolor="darkblue" width="94%">
<tr>
<td><nobr>Full Path to 1: </nobr></td><td><input type="file"
name="file1" style="width: 250px" value="" /></td>
</tr>
<tr>
<td><nobr>Full Path to 2: </nobr></td><td><input type="file"
name="file2" style="width: 250px" value=""/></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="button" value="Create
Report" name="run_button" onClick="CreateReport"></td>
</tr>
</table>
</form>
</center>
</body>

'''''''''''from_hta.vbs is a VBScript with the following code:


Dim HR_FILE_CORP, HR_FILE_PROT

Sub Include(sInstFile)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set f = oFSO.OpenTextFile(GetPath() & sInstFile)
s = f.ReadAll
f.Close
ExecuteGlobal s
End Sub

Function GetPath()
Path=WScript.ScriptFullName
ix=InstrRev(Path,"\")
Path=Left(Path,ix)
GetPath = Path
End Function

Include "Build.vbs"

Sub FromHTA()
Set objArgs = WScript.Arguments
FILE_1 = objArgs(0)
FILE_2 = objArgs(1)
Main FILE_1, FILE_2
End Sub

FromHTA()

---------

''''''''Build.vbs looks like this. This is the VBScript that actually
creates the Workbook that you want to create on the client side.

Const DARK_BLUE = 47
Const LIGHT_BLUE = 37

Dim i,objws,objXL,objwb,ObjDomain

Set ObjRoot = GetObject("LDAP://RootDSE")
strDNC = ObjRoot.Get("DefaultNamingContext")
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Sub Main(FILE_1, FILE_2)
'Do your manipulations on objXL and add Worksheets and Modules to the
VBProject here
MsgBox "Done"
End Sub

 

[gimme_this_gimme_that]

I must have lost you.

Basically what you were trying to do, create an Excel Worksheet with
VBA Modules using VBScript is something you can't do with default
settings in Excel and IE, it's a security issue. If you could do that
you could take control of someone's machine.

The closest you can come to that is to put an hta file in a frameset
and fish for users who have their security set to low and who've check-
allowed access to their VBProjects.Yeah, and you could store the VBA
modules on a remote server and embed the VBA in XML files. This would
allow you, the evil hacker, to modify the VBA that executes on your
victim's computer anytime you want.

Fat chance.

Even if you embed the hta in a frameset IE will tell the user that
they are about to run an executable. So on the one hand you're looking
for power users who've slacked on their permissions - but also for
idiots who would click OK when asked if they wanted to run an hta.

 

[drvice]

I must have lost you.

Basically what you were trying to do, create an Excel Worksheet with
VBA Modules using VBScript is something you can't do with default
settings in Excel and IE, it's a security issue. If you could do that
you could take control of someone's machine.

The closest you can come to that is to put an hta file in a frameset
and fish for users who have their security set to low and who've check-
allowed access to their VBProjects.Yeah, and you could store the VBA
modules on a remote server and embed the VBA in XML files. This would
allow you, the evil hacker, to modify the VBA that executes on your
victim's computer anytime you want.

Fat chance.

Even if you embed the hta in a frameset IE will tell the user that
they are about to run an executable. So on the one hand you're looking
for power users who've slacked on their permissions - but also for
idiots who would click OK when asked if they wanted to run an hta.

I'll give it a shot....thanks for your help!