amb2301 Posted January 14, 2020 Posted January 14, 2020 hi Friends, i am trying to close the particular excel file (bom_for_job.xlsx), while is already opened, i tried using following script, but it closing all the opened excel files, please suggest me.. WinWait("bom_for_job.xlsx - Excel","") sleep(2500) WinActivate("bom_for_job.xlsx - Excel") sleep(500) Winclose("bom_for_job.xlsx - Excel") sleep(1000)
Subz Posted January 14, 2020 Posted January 14, 2020 Use _Excel_BookAttach and _Excel_BookClose see help file more info
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 4 minutes ago, Subz said: Use _Excel_BookAttach and _Excel_BookClose see help file more info thank you so much, i try that & let u know
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 2 hours ago, Subz said: Use _Excel_BookAttach and _Excel_BookClose see help file more info i tried with below code, but its not closing any of the excel sheet, could you check & suggest me #include <Excel.au3> Local $Value1 = InputBox("File path#", "Enter Your FilePath Here:", "", "") Local $Value2 = "\bom_for_job" Local $sWorkbook = $Value1 & $Value2 Local $oWorkbook = _Excel_BookAttach($sWorkbook) WinWait("BOM_FOR_JOB.xlsx","") sleep(2500) WinActivate("BOM_FOR_JOB.xlsx") sleep(2500) _Excel_Close($oWorkbook, Default, True) ;_Excel_close($sWorkbook) sleep(1000)
Subz Posted January 14, 2020 Posted January 14, 2020 You need to use _Excel_BookClose example: #include <Excel.au3> Global $oWorkbook = _Excel_BookAttach("bom_for_job.xlsx", "FileName") If @error Then Exit MsgBox(4096, "Excel Book Attach Error", "Unable to attach to bom_for_job.xlsx") _Excel_BookClose($oWorkbook, True) SkysLastChance 1
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 13 minutes ago, Subz said: #include <Excel.au3> Global $oWorkbook = _Excel_BookAttach("bom_for_job.xlsx", "FileName") If @error Then Exit MsgBox(4096, "Excel Book Attach Error", "Unable to attach to bom_for_job.xlsx") _Excel_BookClose($oWorkbook, True) You need to use _Excel_BookClose example: #include <Excel.au3> Local $Value1 = InputBox("File path#", "Enter Your FilePath Here:", "", "") Local $Value2 = "\bom_for_job" Local $sWorkbook = $Value1 & $Value2 ;Global $oWorkbook = _Excel_BookAttach($sWorkbook) WinWait("BOM_FOR_JOB.xlsx","") sleep(2500) WinActivate("BOM_FOR_JOB.xlsx") sleep(2500) Global $oWorkbook = _Excel_BookAttach($sWorkbook) If @error Then Exit MsgBox(4096, "Excel Book Attach Error", "Unable to attach to bom_for_job.xlsx") _Excel_BookClose($oWorkbook,Default, True) i tried like above code, but its not happening, could you please check my error
Subz Posted January 14, 2020 Posted January 14, 2020 I actually tested my code creating a file named bom_for_job.xlsx and it worked fine. The issue with your code is either: a. Your file path is incorrect b. $Value2 doesn't have the extension ".xlsx" You don't need WinWait, WinActivate as I mentioned the code I posted will close any Excel workbook that has the filename "bom_for_job.xlsx"
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 (edited) 1 hour ago, Subz said: You need to use _Excel_BookClose example: #include <Excel.au3> Global $oWorkbook = _Excel_BookAttach("bom_for_job.xlsx", "FileName") If @error Then Exit MsgBox(4096, "Excel Book Attach Error", "Unable to attach to bom_for_job.xlsx") _Excel_BookClose($oWorkbook, True) instead of below line Global $oWorkbook = _Excel_BookAttach("bom_for_job.xlsx", "FileName") can i use this line ? Global $oWorkbook = _Excel_BookAttach("\\corp.ads\users\VDIUserDataKIDC1\x11234\Desktop\ITR 2020\BOM_FOR_JOB.xlsx") because i am getting Attach error Edited January 14, 2020 by amb2301
Subz Posted January 14, 2020 Posted January 14, 2020 Use _Excel_BookList and see that the path matches (see col 1 (filename) and col 2(full filepath)): #include <Excel.au3> Local $aWorkBooks = _Excel_BookList() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookList", "Error listing Workbooks." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _ArrayDisplay($aWorkBooks, "Excel UDF: _Excel_BookList - List of workbooks of all instances")
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 7 minutes ago, Subz said: Use _Excel_BookList and see that the path matches (see col 1 (filename) and col 2(full filepath)): #include <Excel.au3> Local $aWorkBooks = _Excel_BookList() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookList", "Error listing Workbooks." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _ArrayDisplay($aWorkBooks, "Excel UDF: _Excel_BookList - List of workbooks of all instances") i am getting error like this
Subz Posted January 14, 2020 Posted January 14, 2020 Can you tell me how many Excel workbooks you have opened? What is your version of Excel? Have these all been opened using your desktop Excel (in case you're using Citrix or RDP)? Have these all been opened with your user account?
amb2301 Posted January 14, 2020 Author Posted January 14, 2020 1 minute ago, Subz said: Can you tell me how many Excel workbooks you have opened? What is your version of Excel? Have these all been opened using your desktop Excel (in case you're using Citrix or RDP)? Have these all been opened with your user account? currently i have 2 excels opened book1.xlsx & BOM_FOR_JOB.xlsx i m using excel version 2013 yes both files are opened from my desktop folder yes its been opened from my user account
Subz Posted January 15, 2020 Posted January 15, 2020 The code should work on both 2013 and 2016, I've tested it on both, so not sure why you're getting different results maybe @water has this behaviour before
amb2301 Posted January 15, 2020 Author Posted January 15, 2020 @water could you please help me on this task
Nine Posted January 15, 2020 Posted January 15, 2020 Do you have the latest AutoIt version ? “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy
amb2301 Posted January 15, 2020 Author Posted January 15, 2020 1 minute ago, Nine said: Do you have the latest AutoIt version ? yes
amb2301 Posted January 15, 2020 Author Posted January 15, 2020 3 minutes ago, Nine said: Do you have the latest AutoIt version ? #include <Excel.au3> Local $sWorkbook = "\\corp.ads\users\VDIUserDataKIDC1\x11234\Desktop\ITR 2020\BOM_FOR_JOB.xlsx" Global $oWorkbook = _Excel_BookAttach("bom_for_job.xlsx", "FileName") If @error Then Exit MsgBox(4096, "Excel Book Attach Error", "Unable to attach to BOM_FOR_JOB.xlsx") _Excel_BookClose($oWorkbook, True) this is the code i am trying ...
Nine Posted January 15, 2020 Posted January 15, 2020 Could you start a single empty Excel (with no other instance of Excel) and run this code, so we know if it finds the object : #include <Constants.au3> Local $oWorkbook, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}" $oWorkbook = ObjGet("", $sCLSID_Workbook, 1) If @error Then MsgBox ($MB_SYSTEMMODAL,"","Object Error") Else MsgBox ($MB_SYSTEMMODAL,"","Object found") EndIf “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy
amb2301 Posted January 15, 2020 Author Posted January 15, 2020 5 hours ago, Nine said: Could you start a single empty Excel (with no other instance of Excel) and run this code, so we know if it finds the object : #include <Constants.au3> Local $oWorkbook, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}" $oWorkbook = ObjGet("", $sCLSID_Workbook, 1) If @error Then MsgBox ($MB_SYSTEMMODAL,"","Object Error") Else MsgBox ($MB_SYSTEMMODAL,"","Object found") EndIf hi Demain, i have tried with your code & as you said, i opened a blank excel file from the desktop, it shows "Object Error" so please suggest me
amb2301 Posted January 15, 2020 Author Posted January 15, 2020 5 hours ago, Nine said: Could you start a single empty Excel (with no other instance of Excel) and run this code, so we know if it finds the object : #include <Constants.au3> Local $oWorkbook, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}" $oWorkbook = ObjGet("", $sCLSID_Workbook, 1) If @error Then MsgBox ($MB_SYSTEMMODAL,"","Object Error") Else MsgBox ($MB_SYSTEMMODAL,"","Object found") EndIf
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