Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts

    Append Form Data to an Excel File via ASP

    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

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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/

  • #3
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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.

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

    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/

  • #5
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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

  • #6
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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:
    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
    Last edited by TheShaner; 09-13-2005 at 03:53 PM.

  • #7
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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.

    -Shane

  • #8
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    In case anyone was wondering, I got this code below to work from the help of this post here.

    Code:
    <%@ 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

    -Shane

  • #9
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @TheShaner,
    What OS is your code running on?

  • #10
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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

  • #11
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

  • #12
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Thanks neocool, I've got the permissions worked out now, so everything's running smoothly now

    -Shane

  • #13
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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....

  • #14
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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.

    -Shane

  • #15
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •