import multiple Excel 2007 files using openrowset


  • hello,

    I Have a folder that includes multiple excle files (ver 2007), i am trying  to loop through each file using vbscript to import its data to sql 2000 using openrowset

    but i am getting this error in the openrowset line "[Microsoft][ODBC sql server driver] [sql server] [OLE/DB provider returned message: the microsoft office access DB engine cannot open or write to the file '', it is already open exclusively by another user or you need permission to view or write its data]

    code 80040E14

    source microsoft OLEDB provider for ODBC drivers

    Any help please



    Wednesday, September 8, 2010 1:29 PM


  • This code For i = 1 To objWorkbook.Worksheets.Count


     con.execute("Insert into ['"&SheetName&"$'] Select * FROM OPENROWSET('Microsoft.ACE.Oledb.12.0','Excel 12.0;HDR=YES;IMEX=1;Database="&FilePath&";HDR=YES;IMEX=1','SELECT * FROM ["&SheetName&"$]')")


    seems wrong to me. I think you need to get all sheet names into array first, then make sure the Excel is closed and then run a separate loop for each sheet name..


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, September 14, 2010 9:19 AM
    Wednesday, September 8, 2010 3:32 PM

All replies