locked
Opening Excel Sheet with ODBC Driver RRS feed

  • Question

  • I am trying to open an Excel sheet from a server where Office isn't installed. Although, we have Microsoft Excel Driver installed.

    I am trying to do something fairly simple: Import into Powershell the data from that Excel sheet.

    $Excel = New-Object -Com Excel.Application
    
    $path= '"C:\Users\gpiche001\Desktop\UserList.xlsx'; 
    $openfile= $Excel.workbooks.open($path); 
    $Sheet= $openfile.worksheets.item(2); 
    $arrExcelValues = @() 

    I get the following error:

    New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

    I understand that this is probably caused by Powershell not being able to open Excel. I was wondering how can I make it work by leveraging the MS Excel Driver?

    Thanks!
    • Edited by Gab Piche Wednesday, August 2, 2017 6:30 PM
    Wednesday, August 2, 2017 6:29 PM

Answers

  • This is caused by Excel not being installed.

    The MS Excel driver requires the installations of the ACE drivers from the MSAccess runtime package.

    <#	
    	.NOTES
    	===========================================================================
    	 Created with: 	SAPIEN Technologies, Inc., PowerShell Studio 2015 v4.2.81
    	 Created on:   	3/6/2015 10:51 AM
    	 Created by:   	James Vierra
    	 Organization: 	Designed Systems & Services
    	 Filename:     	
    	===========================================================================
    	.DESCRIPTION
    		THis is a template derived from this blog post:
            	http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/15/how-can-i-write-to-excel-without-using-excel.aspx
        .LINK
    		http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/15/how-can-i-write-to-excel-without-using-excel.aspx
    		http://1drv.ms/1waQ1LA
    #>
    Begin{
        [string]$fileName = 'C:\temp2\update_tester.xlsx'
        #	# old connection string won't work on Win 7 and later systems.
    	#$connectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$fileName;Extended Properties=Excel 8.0'
    	$connectionString= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp2\update_tester.xlsx;Extended Properties="Excel 12.0;HDR=YES";Persist Security Info=False'
    	$query='select * from [Sheet1$]'
    	$conn=new-object System.Data.OleDb.OleDbConnection($connectionString)
    }
    Process{
    	$conn.Open()
    	$cmd=$conn.CreateCommand()
    	$cmd.CommandText=$query
        $adpt= New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
        $dt= New-Object System.Data.DataTable
        $adpt.Fill($dt)
        $dt
        
    }
    End{
    	$conn.Close()
    }
    


    \_(ツ)_/

    • Marked as answer by Gab Piche Wednesday, August 2, 2017 7:05 PM
    Wednesday, August 2, 2017 6:42 PM