none
import multiple Excel 2007 files using openrowset

    Question

  • 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 08, 2010 1:29 PM

Answers

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

     sheetName=objWorkbook.worksheets(i).Name
     
      objExcel.Workbooks.close

     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&"$]')")

    next 

    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 08, 2010 3:32 PM
    Moderator

All replies