Powershell script error

spiceuser-bexok · 2021-12-07T19:51:07.000Z

I get the following error when running my Powershell script.

Exception calling “Open” with “0” argument(s): “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on
the local machine.”

How do I fix this problem?

Thanks!

brianswales · 2021-12-07T19:59:43.000Z

Tell us what you are trying to do. It looks like Access isn’t installed or you are calling the wrong version.

Neally · 2021-12-07T20:02:26.000Z

spiceuser-bexok:

Exception calling “Open” with “0” argument(s): “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on
the local machine.”

How do I fix this problem?

That usually requires the Microsoft Access Database Engine Redistributable
e.g.
https://www.microsoft.com/en-us/download/details.aspx?id=54920

Why are you using OLEDB to connect to Excel?

I’d recommend to re-write your code to use the ImportExcel module by Doug Finke

spiceuser-bexok · 2021-12-07T20:14:40.000Z

I get emails with attached invoices every week. I take the emails and save them in a folder so that I can organize the invoices to send to correct people. When I run this script, it would take all the email attachments and save it as a separate file in the same folder. The error happens when it needs to create folders with names based on the attachment file names.

saves all email attachments in the same folder as the emails
creates folders in the same folder and names it based on the name of the attachment files
If possible, can I add another command to have all the attachments moved to the folder with the same name?

spiceuser-bexok · 2021-12-07T20:16:52.000Z

@alexw : I did download the Access Database Redistributable software but it didnt fix the issue.

Neally · 2021-12-07T20:18:47.000Z

spiceuser-bexok:

Neally: I did download the Access Database Redistributable software but it didnt fix the issue.

Did you install it and reboot?

Can you share the relevant area in your code?

So you use the OLEDB trying to connect to Outlook? Where do those folders get created? in outlook or local file system?

spiceuser-bexok · 2021-12-07T20:39:23.000Z

@alexw The folders get created in a local file system. Literally a folder on desktop. Used to be a server folder buy I’ve been trying to get this fixed

Neally · 2021-12-07T20:59:38.000Z

spiceuser-bexok:

Neally The folders get created in a local file system. Literally a folder on desktop. Used to be a server folder buy I’ve been trying to get this fixed

for which you’d use ‘new-item $folderName -itemtype directory’ which does not give the error you are describing.

we need to see the code.

Neally · 2021-12-07T21:47:11.000Z

Please read this:

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

spiceuser-bexok · 2021-12-07T21:54:43.000Z

Function FormatID ($strID)
{
    $modID = $strID

    $idLen =$modID.Length
    $idLen = 10 - $idLen

    if($idLen -lt 10)
    {
        for($i=0; $i -lt $idLen; $i++)
        {
            $modID = "0$modID"
        }
    
    }

    Return $modID
}

Function ConvertFrom-CurrencyString
 {                 
    param($target)             
            
    [ref]$number=$null             
            
    $styles = [System.Globalization.NumberStyles]::AllowCurrencySymbol -bor
        [System.Globalization.NumberStyles]::AllowParentheses -bor             
        [System.Globalization.NumberStyles]::AllowDecimalPoint -bor             
        [System.Globalization.NumberStyles]::AllowThousands -bor
        [System.Globalization.NumberStyles]::AllowLeadingSign                 
            
     if([decimal]::TryParse($target, $styles, $null, $number)) {             
        $number.Value             
     } else {            
       Write-Host 'Currency conversion error in file - ' $ExcelDoc            
     }            
}      

$EmailDictionary = New-Object System.Collections.Generic.Dictionary“[[System.String],[System.String]]”

Function Extract-GraebelUploadData
{

$currentDirectory = Get-Location
$outlook = new-object -comobject outlook.application
$emails = get-childitem $msgpath -filter *.msg

write-host "Revewing " + $emails.Count + "emails...."
 
foreach ($email in $emails)
{
     
    $RemoveEmail = 0
    
    $Message = $outlook.CreateitemFromTemplate($email.FullName)
    $Message.attachments|foreach{

	    if($_.filename -like '*Upload*')
	    {

            $EmailDictionary.Add($_.filename, $email.Name)
                    
            if(-not(Test-Path (Join-Path $currentDirectory $_.filename)))
            {
                $_.saveasfile((Join-Path $currentDirectory $_.filename))
                $RemoveEmail = 1
            }
            Else
            {
                Write-Host 'A duplicate file name has been detected in message' + $_.filename
                $RemoveEmail = 0
            }
	    }
	
    }   
    
   #if($RemoveEmail -eq 1){Remove-Item $email.FullName}   Disabling deleting the emails

}

}

Extract-GraebelUploadData

$dirLocation = Get-Location
$ExcelFiles = Get-ChildItem $dirLocation -Filter *.xl*

#Excel Conn String
$strFileName = "C:\Data\scriptingGuys\Servers.xls;"
$strSheetName = 'ServerList$'
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"

$strExtend = ';Extended Properties="Excel 12.0; HDR=NO;IMEX=1;"'
                                                               #HDR=NO tells the conn string that there is no header rows avaible, it should use excel column names instead.
$strQuery = "Select * from [Sheet1$]"                             #IMEX=1 tells the conn string to import intermixed data. this means it will not auto guess at the columns.

#Build Temporary table to be used below to store data from Graebel Files

$tmpTable = New-Object System.Data.DataTable
$tmpDataColumn = New-Object System.Data.DataColumn

$col1 = New-Object System.Data.DataColumn 'VendorName', ([string])
$col2 = New-Object System.Data.DataColumn 'VendorNum', ([string])
$col3 = New-Object System.Data.DataColumn 'VendorSiteCode', ([string])
$col4 = New-Object System.Data.DataColumn 'InvoiceNum', ([string])
$col5 = New-Object System.Data.DataColumn 'InvoiceDate', ([string])
$col6 = New-Object System.Data.DataColumn 'Description', ([string])
$col7 = New-Object System.Data.DataColumn 'InvoiceType', ([string])
$col8 = New-Object System.Data.DataColumn 'InvoiceAmount', ([string])
$col9 = New-Object System.Data.DataColumn 'CurrCode', ([string])
$col10 = New-Object System.Data.DataColumn 'Paygroup', ([string])
$col11 = New-Object System.Data.DataColumn 'LineType', ([string])
$col12 = New-Object System.Data.DataColumn 'GLAccount', ([string])
$col13 = New-Object System.Data.DataColumn 'LineDesc', ([string])
$col14 = New-Object System.Data.DataColumn 'TermsName', ([string])
$col15 = New-Object System.Data.DataColumn 'LineAmount', ([string])

$tmpTable.Columns.Add($col1)
$tmpTable.Columns.Add($col2)
$tmpTable.Columns.Add($col3)
$tmpTable.Columns.Add($col4)
$tmpTable.Columns.Add($col5)
$tmpTable.Columns.Add($col6)
$tmpTable.Columns.Add($col7)
$tmpTable.Columns.Add($col8)
$tmpTable.Columns.Add($col9)
$tmpTable.Columns.Add($col10)
$tmpTable.Columns.Add($col11)
$tmpTable.Columns.Add($col12)
$tmpTable.Columns.Add($col13)
$tmpTable.Columns.Add($col14)
$tmpTable.Columns.Add($col15)

$dirLocation = Get-Location

ForEach($ExcelDoc in $ExcelFiles)
{

    Write-Host 'Starting work on - ' $ExcelDoc 

    $strDataSource = "Data Source = " + $ExcelDoc.FullName
    $fullConnString = ($strProvider + $strDataSource + $strExtend)

    $objConn = New-Object System.Data.OleDb.OleDbConnection($fullConnString)
    $objConn.open()

    $sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
    #create connection to file
    $sqlCommand.Connection = $objConn

   
    $DataReader = $sqlCommand.ExecuteReader()

    Try
    {
    
                          
            While($DataReader.Read())
            {
                if($DataReader[0].ToString() -eq 'Expense')
                {
                    $DataRow = $tmpTable.NewRow()

                    #$DataRow['Source'] = $DataReader[0].ToString()
                    #$DataRow['Org'] = $DataReader[1].ToString()
                    
                    #Build in Logic to change Vendor Name, Vendor Number, Sitecode and Line Description if Company Code = China or Morocco
                    
                    $DataRow['VendorName'] = $DataReader[2].ToString()
                    #$DataRow['PubCode'] = $DataReader[3].ToString()
                    $DataRow['VendorNum'] = $DataReader[4].ToString()
                    $DataRow['VendorSiteCode'] = $DataReader[5].ToString()
                    
                    
                    
                    
                    
                    $DataRow['InvoiceNum'] = $DataReader[6].ToString()   
                    $DataRow['InvoiceDate'] = $DataReader[7]
                    $DataRow['Description'] = $DataReader[8].ToString()
                    $DataRow['InvoiceType'] = $DataReader[9].ToString()
                    $DataRow['InvoiceAmount'] = (ConvertFrom-CurrencyString $DataReader[10].ToString())
                    $DataRow['CurrCode'] = $DataReader[11].ToString()
                    $DataRow['Paygroup'] = $DataReader[12].ToString()
                    #$DataRow['TermsName'] = $DataReader[13].ToString() 
                    #$DataRow['ImageNumber'] = $DataReader[14].ToString()
                    #$DataRow['ReturnId'] = $DataReader[15].ToString()
                    $DataRow['LineType'] = $DataReader[16].ToString()
                    $DataRow['LineAmount'] = (ConvertFrom-CurrencyString $DataReader[17].ToString())
                    #$DataRow['TaxCode'] = $DataReader[18].ToString()
                    #$DataRow['TaxRecoveryRate'] = $DataReader[19].ToString()
                    $tmpGl = $DataReader[20].ToString()
                    if($tmpGl.Substring($tmpGl.Length - 4,4) -eq '3210')
                    {
                        $DataRow['GlAccount'] = $tmpGl
                    }
                    Else
                    {
                        $DataRow['GlAccount'] = $tmpGl.Substring(0, ($tmpGl.Length - 4)).ToString() + '3210'
                    }
                    
                                       
                    $DataRow['LineDesc'] = $DataReader[21].ToString()
                    #$DataRow['PONumber'] = $DataReader[22].ToString()
                    #$DataRow['POLineNumber'] = $DataReader[23].ToString()
                    #$DataRow['POShipNumber'] = $DataReader[24].ToString()
                    #$DataRow['PODistNumber'] = $DataReader[25].ToString()
                    #$DataRow['POReleaseNumber'] = $DataReader[26].ToString()
                    #$DataRow['QtyInvoiced'] = $DataReader[27].ToString()
                    #$DataRow['NetUnitPrice'] = $DataReader[28].ToString()
                    #$DataRow['GLDate'] = $DataReader[29].ToString()
                    
                    
                    #$DataRow['EmployeeName'] = $DataReader[30].ToString()
               
                    #$DataRow['EID'] = FormatID $DataReader[31].ToString()
                    #$DataRow['CID'] = FormatID $DataReader[32].ToString()
        
                    #$DataRow['ShortDesc'] = $DataReader[33].ToString()
                    #$dataRow['FileName'] = $ExcelDoc
                    #$DataRow['EntryID'] = $DataReader[34].ToString()
                    #$DataRow['AcctCode'] = $DataReader[35].ToString()
                    #$DataRow['APROmaster'] = $DataReader[36].ToString()
                    #$DataRow['APROassignment'] = $DataReader[37].ToString()
                    #$DataRow['ExceptionID'] = $DataReader[38]

                                        

                    #$table.Rows.Add($DataRow)
                    $tmpTable.Rows.Add($DataRow)

                }
                Else
                {
                    #do not add record to table
                    
                }
            
            
            }

            #Close Reader
            $DataReader.Close()
            $objConn.Close()

           
           
           

           
                     
           #checks for a "UploadFiles" folder, if it does not currently exists this will create it.
           if(Test-Path .\UploadFiles)
           {
                      
           }
           Else
           {
                New-Item .\UploadFiles -ItemType Directory
           }
           

           #Create Directory for Uploadfile and. Emails

                     
            
            
           if(Test-Path ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString()) )
           {
                      
           }
           Else
           {
           New-Item ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString()) -ItemType Directory
           }           

         

           

           Copy-Item -Path ($dirLocation.ToString() + '\CreatureUploadTemplate\creatureuploadtemplate.xlsx') -Destination ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString() + '\' + $ExcelDoc.BaseName.ToString() + '.xlsx')

           #$tmpTable to write to Creature Upload File
           #Open newly created Creature file

           $ExcelObject = New-Object -comobject Excel.Application
           $strPath = ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString() + '\' + $ExcelDoc.BaseName.ToString() + '.xlsx')
           $ActiveWorkbook = $ExcelObject.Workbooks.Open($strPath)
           $ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
           
          
          #Input on creature 2.0 template starts on row 3
           $ExceliRow = 3

          

            

           ForEach($tmpRow in $tmpTable.Rows)
           {
                $ActiveWorksheet.Cells($ExceliRow,1) = $tmpRow['VendorName']
                $ActiveWorksheet.Cells($ExceliRow,3) = $tmpRow['VendorNum']
                $ActiveWorksheet.Cells($ExceliRow,4) = $tmpRow['VendorSiteCode']
                $ActiveWorksheet.Cells($ExceliRow,5) = $tmpRow['InvoiceNum']
                $ActiveWorksheet.Cells($ExceliRow,6) = $tmpRow['InvoiceDate']
                $ActiveWorksheet.Cells($ExceliRow,7) = $tmpRow['Description']
                $ActiveWorksheet.Cells($ExceliRow,8) = $tmpRow['InvoiceType']
                $ActiveWorksheet.Cells($ExceliRow,9) = $tmpRow['InvoiceAmount']
                $ActiveWorksheet.Cells($ExceliRow,10) = $tmpRow['CurrCode']
                $ActiveWorksheet.Cells($ExceliRow,11) = $tmpRow['Paygroup']
                $ActiveWorksheet.Cells($ExceliRow,12) = $tmpRow['TermsName']
                $ActiveWorksheet.Cells($ExceliRow,15) = $tmpRow['LineType']
                $ActiveWorksheet.Cells($ExceliRow,16) = $tmpRow['LineAmount']
                $ActiveWorksheet.Cells($ExceliRow,19) = $tmpRow['GLAccount']
                $ActiveWorksheet.Cells($ExceliRow,20) = $tmpRow['LineDesc']

                

                $ExceliRow ++

           }

           #File write complete, save and close the conneciton
           $ActiveWorkbook.Save()
           $ActiveWorkbook.Close()
           $ExcelObject.Quit()

           $tmpTable.Clear()

            
            Write-Host $ExcelDoc

        
        #Else
        #{
            #Write-Host $ExcelDoc " (Error: Not 40 columns): Current Count = " $DataReader.FieldCount
            $DataReader.Close()
            $objConn.Close()
            $ExcelObject.Quit()
        #}

        Move-Item ($dirLocation.ToString() + '\' + $EmailDictionary[$ExcelDoc.Name]) ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString()) 
        #Move-Item ($dirLocation.ToString() + '\RE  ' + $EmailDictionary[$ExcelDoc.Name].ToString()) ($dirLocation.ToString() + '\UploadFiles\' + $ExcelDoc.BaseName.ToString())
        Remove-Item ($dirLocation.ToString() + '\' + $ExcelDoc.Name.ToString())

    }
    Catch [System.Exception]
    {
        Write-Host $ExcelDoc  $_
        $tmpTable.Clear() #On error clear out temp table so that no records are imported.
        $ExcelObject.Quit()

    }

    

    Write-Host 'Ending work on - ' $ExcelDoc

}

Neally · 2021-12-07T21:58:38.000Z

This is where you call the OLEDB stuff

$objConn    = New-Object System.Data.OleDb.OleDbConnection($fullConnString)
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

Make sure you have the right version (32 bit vs 64 bit) of the redistributable installed and rebooted.

if it still does not work, as said, use the ImportExcel module.