xcaliber13 Posted May 31, 2016 Share Posted May 31, 2016 Can anyone tell me why this script works perfectly on a Windows 7 machine running Office 2013. But does not work on a Windows 7 machine running Office 2003? Local $oExcel = ObjCreate("Excel.Application") Const $xlToRight = -4161 Global $sPathExcel = "X:\Guarantor_Compare\SRA\SMH_Demographics_"&($Date)&".xls" Global $oExcel = _Excel_Open() Global $sTextFile = "X:\DEMOS\SRA\Temp\SMH_Guarantor.txt" Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, 1, Default, $xlTextQualifierDoubleQuote, False, @Tab, Default, Default, Default, True) $oWorkbook.ActiveSheet.Range("A:C").EntireColumn.Delete $oWorkbook.ActiveSheet.Range("F:K").EntireColumn.Delete $oWorkbook.ActiveSheet.Range("K:L").EntireColumn.Delete For $i = 200 To 1 Step -1 If _Excel_RangeRead($oWorkbook, Default, "C" & $i) = _Excel_RangeRead($oWorkbook, Default, "H" & $i) Then $oExcel.Rows($i).Delete EndIf Next Sleep (2000) $oWorkbook.ActiveSheet.Range("A:H").Columns.AutoFit Sleep (3000) _Excel_BookSaveAs($oWorkbook, $sPathExcel, $xlExcel8, True) Sleep (5000) $oWorkbook.Close _Excel_Close($oExcel, Default, True) When I run this on the PC with Excel 2003 the file does not get saved. No errors, script completes but no saved file. Not a big problem as the script works perfectly on my PC that runs Excel 2013. Just wondering. Thanks Link to comment Share on other sites More sharing options...
water Posted May 31, 2016 Share Posted May 31, 2016 What is the value of @error after _Excel_BookSaveAs when used with Office 2003? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted May 31, 2016 Author Share Posted May 31, 2016 water, I ran the script right from within SciTE on the PC that is running Excel 2003. Script completes with Exit code: 0 It is now saving the file (Forgot I made some changes while testing on the PC with Excel 2013) but when I try to open with Excel 2003 I am getting The file is not in a recognizable format and then click OK and the file is just garbage. So Here it is: On the PC with Excel 2013 runs fine. Can open the file on the PC with Excel 2003 no problems. On the PC with Excel 2003 runs fine. Script completes But cannot open the file on the PC with Excel 2003. The file is not in a recognizable format Link to comment Share on other sites More sharing options...
water Posted May 31, 2016 Share Posted May 31, 2016 Could you please try: _Excel_BookSaveAs($oWorkbook, $sPathExcel, Default, True) so the file gets saved as .xls for Excel < 2007, .xlsx for Excel 2007 and later. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted June 1, 2016 Author Share Posted June 1, 2016 water, Changed the _Excel_BookSaveAs to what you wanted me to try. Made no difference. Still The file is not in a recognizable format when running the script from the PC using Excel 2003 and than trying to open the Excel file using Excel 2003. Again it is not a big issue, just wondering why it is doing this. Thank you for the replies. Link to comment Share on other sites More sharing options...
water Posted June 1, 2016 Share Posted June 1, 2016 Why do you still need to run Excel 2003? End of Support (Mainstream) for Office 2003 was 14.04.2009 and Extended Support ended 08.04.2014. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
xcaliber13 Posted June 1, 2016 Author Share Posted June 1, 2016 one hundred plus workstations that not all have been upgraded to Office 2013. As per the usual upper management doesn't think it is a big deal and/or does not want to spend the money. Link to comment Share on other sites More sharing options...
water Posted June 1, 2016 Share Posted June 1, 2016 May I suggest to run the following modified version of your script? Const $xlToRight = -4161 Global $sPathExcel = "X:\Guarantor_Compare\SRA\SMH_Demographics_"&($Date)&".xls" Global $oExcel = _Excel_Open() Global $sTextFile = "X:\DEMOS\SRA\Temp\SMH_Guarantor.txt" Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, 1, Default, $xlTextQualifierDoubleQuote, False, @Tab, Default, Default, Default, True) $oWorkbook.ActiveSheet.Range("A:C").EntireColumn.Delete $oWorkbook.ActiveSheet.Range("F:K").EntireColumn.Delete $oWorkbook.ActiveSheet.Range("K:L").EntireColumn.Delete For $i = 200 To 1 Step -1 If _Excel_RangeRead($oWorkbook, Default, "C" & $i) = _Excel_RangeRead($oWorkbook, Default, "H" & $i) Then $oExcel.Rows($i).Delete EndIf Next $oWorkbook.ActiveSheet.Range("A:H").Columns.AutoFit _Excel_BookSaveAs($oWorkbook, $sPathExcel, Default, True) MsgBox(0, "", "Save returned: @error=" & @error & ", @extended=" & @extended) _Excel_Close($oExcel, False, True) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted June 1, 2016 Share Posted June 1, 2016 5 minutes ago, xcaliber13 said: one hundred plus workstations that not all have been upgraded to Office 2013. As per the usual upper management doesn't think it is a big deal and/or does not want to spend the money. Depending on the country you are located management is responsible for any security problem that arises because non supported software is being used. That might cost a lot of money and have bad impact on the company's reputation. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now