...

View Full Version : Append Form Data to an Excel File via ASP



TheShaner
09-09-2005, 08:26 PM
Ok, I did a search on these forums and google, and just couldn't find exactly what I wanted.

Basically, I will have an Excel spreadsheet already set up on a server, ready for input. Now, I would like the form results of an html page to be processed by an asp page and store them in this Excel file.

How do I go about opening this Excel file using asp (vbscript), write a new a row of info to it, and then close?

Most articles explain how to create a new Excel file. I don't need to do that. I just want to append info to a pre-existing Excel file. Would I use GetFile, just as you would for plain text files?

Thanks for any help and direction in this!

-Shane

nikkiH
09-09-2005, 08:35 PM
No, you have to use the Excel object and all its methods. See the MSDN reference for Excel VBA.

I believe it's
new ActiveX(Application.Excel)
...?

Something like that.
Then you have to use Excel VBA to manipulate the workbooks.

TheShaner
09-09-2005, 08:55 PM
Ok, so after creating this new ActiveX control to the Excel file, I'll be able to reference the Excel VBA with this control, correct? Or did I just get lost somewhere? Does this require any components to be installed on the server? I'm just hoping to use pure ASP and VBScript to access and manipulate the Excel file. Sorry, still a bit noobish in this area, hehe.

Thanks again.

-Shane

nikkiH
09-09-2005, 09:24 PM
YES.
The server must have Excel on it or you'll get an automation-something error. Can't instantiate or some such.

And sorry, it's createObject. COM, not activeX. My bad.
I should actually google before I post something I haven't used in awhile. :o

http://www.sunny-beach.net/manual/306.htm

TheShaner
09-09-2005, 09:49 PM
Ah ha! Ok. I did find references to using CreateObject and Get Object. Although I got a permission denied error when I ran tests using Set xl = CreateObject("Excel.Application"). The folder it's in has read and write permissions, which also allows the file to of course have those inherited permissions. Hmm. Going to have to look more into this.

Thanks for your help!

-Shane

TheShaner
09-13-2005, 03:45 PM
Ok, I'm still at a lost if anyone can help me. I'm getting:

Error Type:
Microsoft VBScript runtime (0x800A0046)
Permission denied: 'GetObject'
/espinc/excel/ModExcel.asp, line 7

For this code:

<%@ Language = "VBScript" %>
<%
Response.Buffer = True

Dim xlBook, xlBookPath, myBook
xlBookPath = "C:\Inetpub\wwwroot\espinc\excel\NewHires.xls"
Set xlBook = GetObject(xlBookPath)
xlBook.Application.Visible = False
Set myBook = xlBook.Workbooks.Add

myBook.Worksheets(1).Cells(2, 1).Value="Shane"
myBook.Worksheets(1).Cells(2, 2).Value="Jackson"

myBook.Save
xlBook.Application.Quit
Set xlBook = Nothing
%>

<html>
<head><title>Excel Test</title>
</head>
<body>

<% Response.Write "XlPath = " & xlBookPath %>

</body>
</html>

I have full permissions to the folder and the .xls file in the folder, so I can't possibly imagine why I get this error. Any help would be much appreciated, thanks!

-Shane

TheShaner
09-13-2005, 04:50 PM
Ok, so I finally decided to uncheck Anonymous Access for the Excel folder, although this is not what I want seeing that eventually I would want this for other users (not just localhost here) to be able to also have access to read/write of this folder and Excel file, but for now, I just want to bypass permissions for the sake of getting this code to work. Here is the ASP code I've changed and still stuck.


<%@ Language = "VBScript" %>
<%
Response.Buffer = True

Dim xlBook, xlBookPath, wks
xlBookPath = "C:\Inetpub\wwwroot\espinc\excel\NewHires.xls"
Set xlBook = GetObject(xlBookPath)
xlBook.Application.Visible = False
Set wks = xlBook.Sheets(1)

wks.Cells(2, 1) = "Shane"
wks.Cells(2, 2) = "Jackson"

xlBook.Save
xlBook.Application.Quit
Set xlBook = Nothing
%>

I went into the Excel file and it was totally blank. It erased the one worksheet on I had in there that was set up for input. Luckily it was the test one and not the real thing! :eek: I'm guessing this had to do with not actually declaring that i'm editting or appending data to the workbook, since it seems I skipped any references to the workbook and went straight to attempting to edit the data in the worksheet. What am I doing wrong? Thanks.

-Shane

TheShaner
09-13-2005, 05:09 PM
In case anyone was wondering, I got this code below to work from the help of this post here (http://forums.devx.com/showthread.php?t=10582).


<%@ Language = "VBScript" %>
<%
Response.Buffer = True

Dim xlObject, xlBook, xlBookPath, wks
Set xlObject = CreateObject("Excel.Application")
xlObject.visible = False
xlBookPath = "C:\Inetpub\wwwroot\espinc\excel\NewHires.xls"
Set xlBook = xlObject.Workbooks.open(xlBookPath)
Set wks = xlObject.ActiveWorkBook.Worksheets(1)

wks.Cells(2, 1) = "Shane"
wks.Cells(2, 2) = "Jackson"

xlBook.Save
xlBook.Close
xlObject.Quit
Set wks = Nothing
Set xlBook = Nothing
Set xlObject = Nothing
%>

Now I just have to get back to figuring out the Permissions problem without having to uncheck Anonymous Authentication :confused:

-Shane

neocool00
09-13-2005, 05:39 PM
@TheShaner,
What OS is your code running on?

TheShaner
09-13-2005, 05:49 PM
I'm using Windows XP and IIS 5.1 on my machine. If I get everything working correctly, then I will be moving this to Windows 2003 Server with IIS 6.0.

-Shane

neocool00
09-13-2005, 07:45 PM
IIS 5 - You need to give permissions to the IUSER account.
IIS 6 - You need to give permissions to the IIS_WPG group/user (can't remember if it's a group or a user).

TheShaner
09-13-2005, 07:58 PM
Thanks neocool, I've got the permissions worked out now, so everything's running smoothly now :cool:

-Shane

neocool00
09-14-2005, 01:20 PM
Shane,
Good luck when you migrate to IIS 6. I worked on a project at my previous employer where we were migrating from 2000 to 2003 and it was a pain in the arse! We had to open several preimum tickets with Microsoft and still did not have everything resolved before I left. Of course, our security team was causing the biggest headache. Their theory was lock everything down and then tell us what you need opened, including permissions, local policies, etc.. Which is not a bad way to go, but when going from one OS to another, you don't always know what needs to be changed. There are numerous differences between 2000 and 2003 and when I was working on the project, finding those differences was hard b/c there wasn't much info out on the web yet. We used a lot of COM+ objects because of the way our enviornment was setup and when we got everything installed on the new 2003 servers, we went from a 1-2 second load time to a script time out. Come to find out, the way 2003 handles COM+ objects and authentication is completely different. We discovered it was taking 6 minutes just to instaniate the COM object (ie. one line of code took 6 minutes "Server.CreateObject"). And then we found out that we couldn't get access to the Request and Response object from within our COM+ applications (never figured out why, or atleast we hadn't by the time I left). So just a word of caution to you and sorry for the long rant, it just stirs up bad memories...mainly me being on a conference call with the kings of MS and them going "um...well...you might want to try this...although we've never done this before..." Of course my favorite was when they told us that "It wasn't designed to do this that way" and we responded with "Um...you told us to do it this way 3 years ago" and then silence....

TheShaner
09-14-2005, 02:44 PM
Haha, yay for MS! :rolleyes: Well, thanks for the warning. I guess the smooth waters I'm on now will be getting pretty rough then when I attempt to migrate the ASP pages I've created to our 2003 server. Hopefully it's not as bad as your situation using COM objects. One thing about your troubles with COM objects: Did you try going to Control Panel -> Administration Tools -> Component Services, select Component Services on left, select Computers folder on left, right click on My Computer on right, and select properties? If you go there, there's a Default Properties tab and a COM Security tab that has some options about enabling COM and the permissions for COM. I discovered it yesterday when doing some searching on the web about COM access. Anyway, I'll make sure to post back when the switch finally happens to let you know whether I drowned or not in the migration, lol.

-Shane

neocool00
09-14-2005, 04:47 PM
Yes Shane, I'm WELL aware of the COM+ properties. It's also in there where you can set which ports for the server to listen for COM+ communication on. I believe, for the most part, that our problem resided in local policies and the rights our local/domain groups and accounts had. Another "bad place" for us was dealing with the application pools. That turned into an authentication nightmare. I'll let it go for now. If you have any more issues, now or when you migrate, post 'em and I'll help you when and if I can.

TheShaner
09-14-2005, 05:07 PM
All this is a really new area to me. I'm no where near IT-knowledgeable, haha. So pardon me if I sound a bit inexperienced, cuz I really am, lol.

Anyway, it seems today I'm getting the Permission Denied error on CreateObject again, but this time, even when I uncheck Anonymous Authentication on the folder with that has the Excel file. I even unchecked it on the ASP file with the code. I haven't changed any permissions on anything since yesterday and I even double checked to be sure. None of it works and still gives me that error. Regardless, I'm an administrator on my computer with full control of every folder and file, yet using localhost, I still get this Permission Denied error. I'm really at a loss now seeing that it was fine yesterday :confused:

-Shane

neocool00
09-14-2005, 08:04 PM
@Shane,
Are you getting permission denied on this line in your code?

Set xlObject = CreateObject("Excel.Application")
If so, then the problem is that the IUSER account does not have access to the excel dll (providing you have anonymous checked).

TheShaner
09-14-2005, 08:24 PM
Yes, it's that line, specifically the CreateObject command.

I've given full access to the IUSR account. Unchecking Anon Auth for the whole website and using localhost to connect does work, but that i really need the IUSR account to work since it would eventually be used on the internet. It's really got me ready to throw my computer off my desk here and say hell with it. But I don't think the company would appreciate my obvious display of power over inanimate objects :rolleyes:

-Shane

TheShaner
09-14-2005, 08:58 PM
Ok, I feel really stupid. This thread can definitely be closed now. I'm not sure why it worked yesterday, but I just noticed that my ASP page needed the Read & Execute permission checked for the IUSR account. This should have been one of the first things I checked, but I was only thinking about the folder which contained the Excel file. My computer can probably breathe easy now that it knows I won't be doing any harm to it.... for now.

-Shane



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum