vbscript excel to sql

Posted on 2009-02-17
Last Modified: 2012-05-06
I need to open an Excel spreadsheet and export it to an SQL database.  I copies some code I found here but am getting errors.  I culled all the table stuff and am trying to just display the data I find on the spreadsheet.  I'm currently getting "object required: 'server'"  on line 1. I understand that I need to set the value, but to what?
Set cn = Server.CreateObject("ADODB.Connection")

cn.Open "DBQ=" & Server.MapPath("tsitimesheet20090212.xls") & ";DRIVER={Microsoft Excel Driver (*.xls)};"

'Set rs = Server.CreateObject("ADODB.Recordset")

' cell range as defined in excel

sql="select * from [Range1];"

rs.Open sql, cn

For counter = 0 To rs.fields.count - 1

' Write out the field names

response.write rs.fields.item(counter).name 

' Move to the next field


' Move to the first record


' Write out the record set

do while not rs.eof 

' Loop through all of the fileds

for counter = 0 to rs.fields.count - 1

' Write out the field values

response.write rs.fields.item(counter).value

' Move to the next field


' Move to the next record



Open in new window

Question by:larksys
    LVL 25

    Expert Comment

    Server object is usually used for ASP coding where you use the IIS's native server object, for plain vbscript use just a straightforward createobject  like -

    Set cn = CreateObject("ADODB.Connection")

    Have you considered using the OPENDATASOURCE transact-sql function to directly import MS-Excel data to an SQL table?
    LVL 1

    Author Comment

    I need to be able to schedule a task to run daily. The name of the Excel file has to be changed on the fly. I can import the data manually, no problem.

    LVL 1

    Author Comment

    Your comment lead me to do a little research on it.  Now I'm just slightly smarter than I was.  I've changed the script to the following that appears to work except it doesn't populate the columns with data;

    Option Explicit



    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    dim strSqlInsertString,objConnection2,objRecordSet2
    dim objExcel,objWorkBook,intRow

    Set objConnection2 = CreateObject("ADODB.Connection")
    Set objRecordSet2 = CreateObject("ADODB.Recordset")

    objConnection2.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=humpty;Password=dumpty;Initial Catalog=xxdev;Data Source=xxlive"

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open _

    intRow = 2

    dim CN,Company,City,State,Source,Title,Found,Compensation

    strSqlInsertString = "INSERT INTO xx_Imports (cN,Company,City,State,Source," & _
    "Title,Found,Compensation) " & _
    "VALUES('" & CN & "','" & Company & "','" & City & "','" & State& "','" & Source& "'," & _
    "'" & Title& "','" & Found& "','" & Compensation & "')"

    Do Until objExcel.Cells(intRow,1).Value = ""
        CN = objExcel.Cells(intRow, 1).Value
        Company = objExcel.Cells(intRow, 2).Value
        City = objExcel.Cells(intRow, 3).Value
        State = objExcel.Cells(intRow, 4).Value
        Source = objExcel.Cells(intRow, 5).Value
        Title = objExcel.Cells(intRow, 6).Value
        Found = objExcel.Cells(intRow, 7).Value
        Compensation = objExcel.Cells(intRow, 8).Value
        intRow = intRow + 1

    set objRecordSet2=objConnection2.execute(strSQLInsertString)



    set objConnection2 = Nothing


    LVL 25

    Accepted Solution

    Debug the contents of the string strSqlInsertString  using a msgbox..

    You should ideally refer to the sheet explicitly - objWorkbook.Sheets(1).cells(intRow, 1).value

    Executing an Insert statement will not return a recordset, so change line as follows -

    objConnection2.execute strSQLInsertString
    LVL 1

    Author Comment

    I also had to move the sqlstring build inside the loop.  Thanks very much.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now