Excel BookSaveAs not working.

[Nickodemis]

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")

[Nickodemis]

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.

[Nickodemis]

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.

[water]

_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

[xcaliber13]

Global $sPathExcel = "C:\Users\3020736\Downloads\DSW" & $oDate &".xls"
_Excel_BookSaveAs($oWorkbook, $sPathExcel, $xlExcel8, True)

Try this.  Works in my scripts

[Nickodemis]

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.

[Nickodemis]

Do I need to define this as a global variable?

[Nickodemis]

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?

[water]

It doesn't work because "/" is an invalid character in a filename. Try manually to create such a file.

[Nickodemis]

Yep. That's it. Thank you! Originally the date variable pulled the previous days date, and then was reformatted.

[water]