Nickodemis Posted July 10, 2018 Share Posted July 10, 2018 Hey everyone, Everything before and after this line of code, "_Excel_BookSaveAs ($oExcel, "C:\Users\3020736\Downloads\DSW" & $oDate)", works, but it doesn't save the spreadsheet. It was working previously to adding this line, "Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\3020736\Downloads\daily service worksheet.xls")." Which leads me to believe the issue is related to the $oWorkbook variable, but I just can't figure out what I'm missing. Any help would be greatly appreciated. Thanks. Sorry if the code is not the greatest. I did provide comments on what I was attempting to do throughout the whole thing. Hopefully it helps. ;\\EDIT EXCEL SPREADSHEET// ;Open Excel Spreadsheet Local $oExcel = _Excel_Open() ;Defines $oExcel variable to = the excel workbook that's open. Sleep (500) Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\Username\Downloads\daily service worksheet.xls") ;Opens the Excel workbook. Sleep (4000) ;Delete Columns and Rows $oWorkbook.ActiveSheet.Rows("1:3").EntireRow.Delete ;Deletes specified rows. $oWorkbook.ActiveSheet.Columns("AH:AK").EntireColumn.Delete ;Deletes specified columns. $oWorkbook.ActiveSheet.Columns("G:G").SpecialCells($xlCellTypeBlanks).EntireRow.Delete ;Deletes any blank row in specified column. ;Add Date Field Local $oRange1 = $oExcel.Activesheet.Range("AH1") ;Sets the range for the active sheet. $oExcel.ActiveCell = $oRange1.Select ;Changes the active cell to the range. Send ("Date") ;Inputs data into active cell. Sleep (500) Send ("{Enter}") ;Set Active Cell Sleep (1000) Local $oRange = $oExcel.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell) ;Moves range to the last cell used in spreadsheet. $oExcel.ActiveCell = $oRange.Select ;Makes range active cell Sleep (500) $oExcel.ActiveCell.Clear ;Clears active cell after AutoIT checks it. Sleep (500) ;Add Date Variable Do $oExcel.ActiveCell.Value = ($oDate) ;Changes value of active cell to = the variable $oDate. $oExcel.ActiveCell.Offset(-1,0).Select ;Offsets active cell by provided coordinates. Until $oExcel.ActiveCell.Value = "Date" ;Repeats process until it reaches a cell that contains "Date". ;Save DSW with date Sleep (500) _Excel_BookSaveAs ($oExcel, "C:\Users\Username\Downloads\DSW" & $oDate) ;Saves and names workbook to specified location, and adds $oDate. Sleep (3000) ;Close Excel _Excel_Close ($oExcel) ;Delete original spreadsheet FileDelete ("C:\Users\Username\Downloads\daily service worksheet.xls") Link to comment Share on other sites More sharing options...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 Oh I have also included these: #include <Date.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <Excel.au3> #include <GUIConstantsEx.au3> #include <ExcelConstants.au3> Before anyone asks. Link to comment Share on other sites More sharing options...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 I have also tried replacing the $oExcel variable with $oWorkbook in this portion: "_Excel_BookSaveAs ($oExcel, "C:\Users\3020736\Downloads\DSW" & $oDate. It did not work either. Still didn't end the script, but it just didn't save. Link to comment Share on other sites More sharing options...
water Posted July 10, 2018 Share Posted July 10, 2018 (edited) _Excel_BooksSaveAs needs the workbook object as first parameter. You need to provide the extension in parameter 2 and the fileformat in parameter 3 (depending on the version of Excel you run). _Excel_BookSaveAs($oWorkbook, "C:\Users\3020736\Downloads\DSW" & $oDate & ".xls", $xlExcel8) BTW: Where do you set variable $oDate? Edited July 10, 2018 by water 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 July 10, 2018 Share Posted July 10, 2018 Global $sPathExcel = "C:\Users\3020736\Downloads\DSW" & $oDate &".xls" _Excel_BookSaveAs($oWorkbook, $sPathExcel, $xlExcel8, True) Try this. Works in my scripts Link to comment Share on other sites More sharing options...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 Thanks for the replies. I tried both of your suggestions, but neither worked for me. However, Water's comment about the date made me think. So I removed the $oDate variable from the file path and it worked. $oDate is defined at the beginning of the script through an input box. Excel has no problem calling it later on, but it's apparently not working when it goes to save the spreadsheet. Here's the code for the input box: ;\\SET DATE OF DSW PULLED// $oDate = InputBox("Date", "Type the date for the DSW you would like to add. Example: 01/01/2000", "", " M", -1, -1);Creates an imput box for entering date. The " M" (The space before the M is important) forces input. If @error = 1 Then ;If cancel is clicked. MsgBox(0,"","Cancel was clicked") ;Then displays this message box. Exit ;And closes message box and ends script. EndIf ;Else, proceed with script. MsgBox(1,"DSW Date",$oDate) ;Displays message box to verify date entered. Link to comment Share on other sites More sharing options...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 Do I need to define this as a global variable? Link to comment Share on other sites More sharing options...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 Well it still didn't work as global. Any idea why it would be able to reference the variable for excel, but fail when saving? Link to comment Share on other sites More sharing options...
water Posted July 10, 2018 Share Posted July 10, 2018 It doesn't work because "/" is an invalid character in a filename. Try manually to create such a file. 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...
Nickodemis Posted July 10, 2018 Author Share Posted July 10, 2018 Yep. That's it. Thank you! Originally the date variable pulled the previous days date, and then was reformatted. Link to comment Share on other sites More sharing options...
water Posted July 10, 2018 Share Posted July 10, 2018 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