0

I have a excel (connection.xlsx) which imports data from a Old.csv using OLEDB connection.

Connection String looks like below in excel: Connection String: Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Old;Extended Properties="" Connection Text: SELECT * FROM [Old]

I want to change this connection string programmatically using powershell to load data from other excel file New.csv

Connection String: Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=New;Extended Properties="" Connection Text: SELECT * FROM [New]

I am trying like this:

clear
# Powershell Code
$xlsx = "C:\Users\umashankar.jedgule\Desktop\Terracon\BnMPOC\Connection.xlsx"
$Excel = New-Object -comobject Excel.Application
$ExcelWorkbook = $Excel.workbooks.open($xlsx) 
$conn = $ExcelWorkbook.Connections
echo $conn
$i=1 
foreach($con in $conn)
{
$con.OLEDBConnection.Connection = 'OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=New;Extended Properties=""'
$con.OLEDBConnection.CommandText = 'SELECT * FROM [New]'
$flag=$con.OLEDBConnection.BackgroundQuery
$con.OLEDBConnection.BackgroundQuery=$false
$con.refresh()
$con.OLEDBConnection.BackgroundQuery=$flag
echo $con.OLEDBConnection.Connection
echo $con.OLEDBConnection.CommandTex
echo $test
$i = $i + 1
 }
$ExcelWorkbook.refreshall()
$ExcelWorkbook.Save()
$ExcelWorkbook.Close()
$ExcelWorkbook.Quit()

But prompt is not moving ahead. I am not sure whether above code is correct or not. Please suggest.

enter image description here

Umashankar
  • 1

0 Answers0