[AccessD] EXCEL OT

Martin Reid mwp.reid at qub.ac.uk
Mon Mar 21 10:37:13 CDT 2016


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
>
>
>
>Martin W Reid
>Business Services Team Leader
>Queen's University Belfast
>Information Services
>50 Elmwood Avenue
>Belfast BT9 6AZ
>
>Tel: +44(0)28 9097 6174
>Email:  mwp.reid at qub.ac.uk
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website:  http://www.databaseadvisors.com

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



More information about the AccessD mailing list