qwiqshot

DirCreate & _Excel_BookSaveAs with Variable

36 posts in this topic

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

 

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

 

error.png

It does however correctly make the directory.

Edited by qwiqshot

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

_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."


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

nofile.png

Edited by qwiqshot

Share this post


Link to post
Share on other sites

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"

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by qwiqshot

Share this post


Link to post
Share on other sites

#11 ·  Posted

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.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by l3ill

Share this post


Link to post
Share on other sites

#14 ·  Posted

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

layout.png

Share this post


Link to post
Share on other sites

#15 ·  Posted

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:

capture_13102016_233252.jpg

Share this post


Link to post
Share on other sites

#16 ·  Posted

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted

Same thing locally, see pic

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

 

 

local.png

Share this post


Link to post
Share on other sites

#19 ·  Posted

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#20 ·  Posted

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

same.png

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