Jump to content
xcaliber13

Excel BookSaveAs

Recommended Posts

xcaliber13

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

Share this post


Link to post
Share on other sites
water

What is the value of @error after _Excel_BookSaveAs when used with Office 2003?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

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

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

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.

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

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.

Share this post


Link to post
Share on other sites
water

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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water
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 (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×