1

I am trying to take information from a HTML Form and input into an Excel File (xlsx) with ASP / VBScript (not VB.NET). I have some experience in Java and PHP but am new to the VB world. Sofar I have found ways to get the Data from the GET/POST methods. Now I am trying to create an ADO connection to the excel file. here is my code so far: 

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=EXCEL_FILE.xlsx;" & _"Extended Properties=Excel 12.0 Xml;HDR=YES"
    .Open
End With

I got the connection String from here: connectionstrings.com and tried to stick to this guide: http://support.microsoft.com/kb/257819/en-us But no luck up until now. So here are my questions:

1) Is this the right idea in general? So grabbing the Data from POST for example and then opening a connection with ADO to the excel file and adding the info with queries on the connection object?

2) Any obvious flaws in the code ?

3) Would be great if someone could outline a solution, writing data from a HTML Form into an Excel file.

Thanks in advance.

Edit: Ok Here is the code I try:

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=EXCEL_FILE.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""
'From : http://www.connectionstrings.com/excel-2007
    .Open
End With

Once I call "Open" on cn it gives me a 500 internal Error. I am not sure if I am making an obvious mistake, but since I don't know where to find error logs I don't have a clue where to start looking.

MrPink
  • 66
  • 1
  • 6
  • One more question. I am not entirely sure about the Connection String thing... or in general if ADO is the right way because I have read about various other buzzwords (DSN for Access as an alternative to Excel) and am not sure exactly what the way to go is. – MrPink Oct 11 '11 at 22:19

1 Answers1

1

1 - If you have to use Excel as database, yes it's right. But, if you need a database, should use a database, not an excel sheet.
2 - VBScript doesn't support early binding. You should define variables without data type, should create objects using CreateObject. And you need to some changes in connection string (quotes).
e.g.

Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=EXCEL_FILE.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""
    'From : http://www.connectionstrings.com/excel-2007
.Open
End With
'
' Add new records etc.
'
cn.Close
Set cn = Nothing

3 - An example to add new record (put the instead of above 'Add new records etc.) gets the values from HTML Form (post method).

Dim rs
Set rs = Server.CreateObject("Adodb.Recordset")
With rs
    .Open "[Sheet1$]", cn, 1, 3
    .AddNew 
    .Fields(0).Value = Request.Form("Param1") 'Column A1 (or with name rs.Fields("col1").Value = exp )
    .Fields(1).Value = Request.Form("Param2") 'Column B1
    .Update
    .Close
End With
Set rs = Nothing
Kul-Tigin
  • 16,728
  • 1
  • 35
  • 64
  • possibly should point out that connecting to Excel (especially with write access if you have it) may fall foul of file locking for multiple users. Also, you should use `Server.MapPath()` to get the actual path to the Excel file and add that to the connection string –  Oct 12 '11 at 07:48
  • I didn't think of the problem of Concurrency. It would be better to use an Access DB (.mdb) and then export the data to excel later. Thanks for the answers so far. I am still getting an Error on the above code, once I tried to open the connection: .Open The Problem is all I get is "Internal 500" Error. I only have a provider with a folder where I can drop ASP files and then look at them online. Any idea how I can debug? Are there any logs I don't know of? Without error messages its hard to know what's going wrong in an unfamiliar language. Any ideas are welcome. Thanks! – MrPink Oct 12 '11 at 09:45
  • you should update the question with your code if you've made changes –  Oct 12 '11 at 11:32
  • Hey, I updated the question. Basically it's the same code as Kul-Tigin pasted. But without any error Messages it's impossible to know where to start. – MrPink Oct 12 '11 at 11:54
  • 1
    @MrPink **oracle certified professional** said. You should use `Server.Mappath` to getting physical path from relative / virtual path. e.g. `.ConnectionString = "Data Source="& Server.Mappath("EXCEL_FILE.xlsx") &";Extended Properties=""Excel 12.0 Xml;HDR=YES"""`. And will be better you could get detailed errors : http://stackoverflow.com/questions/2640526/detailed-500-error-message-asp-iis-7-5 – Kul-Tigin Oct 12 '11 at 13:11