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?
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.
__________________
If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/
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.
If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
Bored? Visit http://www.kaelisspace.com/
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.
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
Last edited by TheShaner; 09-13-2005 at 03:53 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.
Code:
<%@ 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! 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.
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).
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....
Haha, yay for MS! 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.
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.
Before you post, read our: 
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.
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.
