DirCreate & _Excel_BookSaveAs with Variable

[qwiqshot]

Hi all. I'm close but I just can't figure out why its not saving the file. It's creating the directory correctly, but that is it.

#include <Excel.au3>
#include <MsgBoxConstants.au3>


While 1
   Sleep (50)

WEnd


Func MakeDirSaveFile()
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      $fName = ($oWorkbook.Worksheets(4).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(4).Range("I30").Value) ; customers address
      $dirName = $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      $sFilePath = "\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      DirCreate ("\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress)
      Sleep (750)
      _Excel_BookSaveAs($oWorkbook, $sFilePath, $xlWorkbookDefault, True)

EndFunc

My save script by itself works just fine (Slow as it may be).

Func Savefile()
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      $fName = ($oWorkbook.Worksheets(4).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(4).Range("I30").Value) ; customers address
      $sFilePath = "\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      _Excel_BookSaveAs($oWorkbook, $sFilePath, $xlWorkbookDefault, True)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail!", "Error saving workbook to '" & $sFilePath & "'." & @CRLF & "@error = " & @error & ", @extended = "       & @extended)
      MsgBox($MB_SYSTEMMODAL, "Success!", "Workbook successfully saved as '" & $sFilePath & "'.")

EndFunc

 

[qwiqshot]

By the way, I Am trying to create a direct and then save a file into that directory with the exact same name.

[water]

What's the value of @error and @extended after calling _Excel_BookSaveAs?

[qwiqshot]

This is the error I get and what the current code is getting the error

HotKeySet ("^!+{S}", "MakeDirSaveFile")     ; Saves the active Excel Asphalt Workbook to server

#include <Excel.au3>
#include <MsgBoxConstants.au3>

While 1
   Sleep (50)

WEnd

Func MakeDirSaveFile()
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      $fName = ($oWorkbook.Worksheets(4).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(4).Range("I30").Value) ; customers address
      $dirName = $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      $sFilePath = "\\MYCLOUDEX2ULTRA\Bids\2017 Bids\Mark\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      DirCreate ("\\MYCLOUDEX2ULTRA\Bids\2017 Bids\Mark\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress)
      Sleep (750)
      _Excel_BookSaveAs($oWorkbook, $sFilePath, $xlWorkbookDefault, True)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail!", "Error saving workbook to '" & $sFilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      MsgBox($MB_SYSTEMMODAL, "Success!", "Workbook successfully saved as '" & $sFilePath & "'.")

EndFunc

 

It does however correctly make the directory.

Edited October 13, 2016 by qwiqshot

[qwiqshot]

I tried putting a very long delay in prior to the _Excel_BookSaveAs of Sleep (60000) hoping it was a delay issue and the same result occurred.

[l3ill]

_Excel_BookSaveAs    @error #4    =    4 - File exists but could not be deleted

Try deleting the existing file by hand before running.

looks like having trouble overwriting existing file...

[water]

_Excel_BooksSaveAs uses FileDelete to remove an existing file. But this is not always possible according to the help file: "Some file attributes can make the deletion impossible, if this is the case look at FileSetAttrib() to change the attributes of a file."

[qwiqshot]

There is no existing file. I am creating the folder and then saving the file into said folder. Both being named identically.

Edited October 13, 2016 by qwiqshot

[l3ill]

Understood, but if you run it a second and third time ( as in testing ) the file from the last test should still be there, no?

And for some reason _Excel_BookSaveAs is giving you @error 4 which plainly states:    "File exists but could not be deleted"

[qwiqshot]

Correct. it Should still be there, but it never is/was. Every time I run it, I have waited different amounts of time to interact with the folder thinking maybe it's a windows thing. It has me baffled. Tho I'm still new to Autoit, so that's easy to do, lol

Edited October 13, 2016 by qwiqshot

[l3ill]

After trying a little and reading up on this I think the problem is that _ExcelBookSaveAs does not react like a normal Save As in Windows which normally means "save a new copy to a different place or under a different name" This seems to only save the file with a different extension.

If that's true the fix is relatively easy, after saving the excel file with new data and closing excel object, use FileMove or FileCopy to do the rest.

[l3ill]

After trying a little and reading up on this I think the problem is that _ExcelBookSaveAs does not react like a normal Save As in Windows which normally means "save a new copy to a different place or under a different name" This seems to only save the file with a different extension.

If that's true the fix is relatively easy, after saving the excel file with new data and closing excel object, use FileMove or FileCopy to do the rest.

Edit: this also explains the File Exists deleting problem, it was trying to delete the original file.

Edited October 13, 2016 by l3ill

[l3ill]

Never mind above...still playing...

[qwiqshot]

l3ill/Water I appreciate you both helping. Here is a behind the scene look at the spreadsheet to connect some dots.

[l3ill]

I think I'm on to something. I have it down to a COM error with this fix:

Local $sFilePath = "C:\Excel\Bids\2017Bids\Mark\"
    DirCreate($sFilePath)
    Local $sWorkbook = $sFilePath & "AsphaltEstimating.xlsm"
    ConsoleWrite($sWorkbook)

    _Excel_BookSaveAs($oWorkbook, $sWorkbook)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail!", "Error saving workbook to '" & $sFilePath & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Success!", "Workbook successfully saved as '" & $sFilePath & "'.")

play around with this and your structure.

Maybe water knows what this means:

[water]

Could you try to save the Workbook to a local drive e.g. C:\temp?

[water]

When saving xlsm files you need to provide parameter $iFormat as well if you do not use xls or xlsx.

[qwiqshot]

Same thing locally, see pic

Water, could you please explain the $ iFormat a bit more. OUr format is in fact xlsm

 

 

[water]

According to the help file and referenced in ExcelConstants.au3 I think it should be $xlOpenXMLWorkbookMacroEnabled,

[qwiqshot]

Pretty sure this is how you mean it should be now, same results, makes the directory just fine, but no file present.