Jump to content
Nickodemis

Excel BookSaveAs not working.

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
water
Posted (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 by water

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
Global $sPathExcel = "C:\Users\3020736\Downloads\DSW" & $oDate &".xls"
_Excel_BookSaveAs($oWorkbook, $sPathExcel, $xlExcel8, True)

Try this.  Works in my scripts

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Nickodemis

Do I need to define this as a global variable?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
water

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 (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
Nickodemis

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

Share this post


Link to post
Share on other sites
water

:)


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

×