[AccessD] EXCEL OT

[Martin Reid]

Hi Gustav

Saved our bacon!

Martin

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 21 March 2016 15:43
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] EXCEL OT

Hi Martin

You owe Shamil a large beer. While we think of doing it, Shamil does it.

By the way, the example shows that PowerShell does deserve its name.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Martin Reid
Sendt: 21. marts 2016 16:37
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: Re: [AccessD] EXCEL OT

Outstanding

Martin


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov Shamil
Sent: 21 March 2016 15:32
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] EXCEL OT

 Hi Martin --

Some googling + "quick & dirty" code cooking and testing - here it's (correct folder and file names according to your environment):

$sourceWorkbookFolder = "S:\Projects\Powershell\"
$sourceWorkbookFileName = "Test workbook.xlsx"
$convertedWorkbookFolder = $sourceWorkbookFolder $convertedWorkbookFileName = "'" + $sourceWorkbookFileName + "' is now in 97-2003 format.xls"
Add-Type -AssemblyName Microsoft.Office.Interop.Excel $xlApp = New-Object -COM "Excel.Application"
$xlApp.Visible = $true
$xlWbk = $xlApp.Workbooks.Open($sourceWorkbookFolder + $sourceWorkbookFileName) $xl97_2003Format = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlExcel8
$xlApp.DisplayAlerts = $false
$xlApp.ActiveWorkbook.SaveAs($convertedWorkbookFolder + $convertedWorkbookFileName, $xl97_2003Format) $xlApp.DisplayAlerts = $true

$xlWbk.Close()
$xlApp.Quit()
FYI: I'm not PowerShell scripts writer at all - the above actually is the first PowerShell script I have ever written.

Thank you.

-- Shamil

>Monday, March 21, 2016 12:08 PM +03:00 from Martin Reid <mwp.reid at qub.ac.uk>:
>
>Morning All
>
>Would anyone have an example of PowerShell opening an Excel workbook and then resaving it with the same name? We need to be 100% sure 2 workbooks we get are Excel 97-2003 format before we run some import code on them.
>
>There would be no user intervention as the script would run automatically. The code will error if they are in the incorrect formation and we want to avoid this happening and also avoid having to manually resave them. We have no control over the source who continually insist they are the correct type.
>
>Best Wishes
>Martin 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com