DirCreate & _Excel_BookSaveAs with Variable

[water]

I just noticed that the "If @error ..." line should immediately follow _Excel_BookAttach.
And you should call _Excel_Close in function MakeDirSaveFile because you open Excel in this function.

[qwiqshot]

Hmm, looking at  _Excel_Close  it seems maybe my approach is all screwed up. 

Quote

$bSaveChanges [optional] Specifies whether changed worksheets should be saved before closing (default = True)

Does this mean I should just remove the _Excel_BookSaveAs in my case? very confusing for a beginner, lol

 

I also see this should apply?

Quote

If _Excel_Open() connected to an already running instance of Excel then you have to set $bForceClose to True to do the same.

 

Edited October 13, 2016 by qwiqshot

[l3ill]

At this point I would suggest taking the _Excel_BookSaveAs example from the help file and changing it step by step to to your needs, testing along the way, until you find what part of this is causing the trouble.

Now that you have some experience with troubleshooting, the process will make more sense to you.

Please post your progress. I would be interested to see what the issue is/was... 

Bill

[water]

_Excel_BookSaveAs should be used when you have finished your Excel processing with the specific workbook.
_Excel_Close just asks what to do with changes you haven't saved until then before they alre lost forever.

[qwiqshot]

So the way I use the script to this point is, I enter all the details into the spreadsheet as I always have, once I have adjusted all aspects I then run the  _Excel_BookSaveAs (seen below) to archive the customers details.

Func SaveAsphalt()
      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

The project we are discussing is my attempt to do exactly the same thing but to organize each customers details per job instead of having a massive pile of excel files jumbled together, regardless of customer or job. After I complete the excel file I also create a PDF version for emailing which also gets named the same. This PDF also gets placed in the folder I create for the particular job I save the excel file for, keeping each case separated. We also place photos taken of each case and/or work order photos into the folder. So as you can see, just saving to a general folder (i.e. \\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\) is chaotic.

I do not believe at this point that  _Excel_Close applies to me.  I will keep trying to figure this one out as it's an integral part of the overall project. I'm sure it's something simple we are all just overlooking.

[water]

I'm sure the problem is caused by the fact that the target directory does not exist.
This script creates

#include <Excel.au3>

SaveAsphalt()

Func SaveAsphalt()
      Local $sWorkbook = "C:\temp\test.xlsx" ; "c:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      $fName = ($oWorkbook.Worksheets(1).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(1).Range("I30").Value) ; customers address
      $sPath = "C:\temp\Bids\2016 Bids\Marks Bids\" & $fName
      $sFilePath = $sPath & "\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      DirCreate($sPath)
      _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

a directory "C:\Temp\Bids\2016 Bids\Marks Bids\Jon Doe" and saves the workbook as "Jon Doe (Sheetname) Customer Address" in this directory for the customer "Jon Doe".

 

[qwiqshot]

Much more eloquent way of saying what I am thinking. That is why I tested the script with a massive Sleep (60000) before asking for help. I suppose I can still use both scripts separately and then just manually move all the files into the folder the script creates, but then that's just conceding. There is a way, I just need to keep at it, though it will likely be clumsy code, lol

[water]

My script is just a proof of concept. If it works for you then replace "C:\Temp" with "\\MYCLOUDEX2ULTRA" and copy function SaveAsphalt to your script replacing your function. No need for two scripts or a Sleep statement.

[qwiqshot]

This is how I altered your version to reflect my scenario

Func MakeDirSaveFile()
      Local $sWorkbook = "C:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Fail", "Error Try Again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      $fName = ($oWorkbook.Worksheets(1).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(1).Range("I30").Value) ; customers address
      $sPath = "\\MYCLOUDEX2ULTRA\Bids\2017 Bids\Mark\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      $sFilePath = $sPath & "\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      DirCreate($sPath)
      _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 did create the directory. This is the message returned by the dialog box (see picture attachment)

Edited October 14, 2016 by qwiqshot

[qwiqshot]

According to a post by Water, Error: -2147352567 (hex: 80020009: DISP_E_EXCEPTION - Unanticipated error occurred) usually means you have no write access to the AD.

So I tested the script from 3 different PC's on network, same results.

Also, just to eliminate the possibilities. I broke the script into 2 parts. Creating the directory & saving the file. Same results this way too.

 

Do you think it may be an issue with the Western Digital Cloud? I doubt it, but at this point I am willing to consider any possibilities.

Edited October 14, 2016 by qwiqshot

[water]

Did you check that the directory has been created successfully? I'm not sure you can create a path in the Cloud this way.

0x80020009: DISP_E_EXCEPTION - Unanticipated error occurred - simply means that an unexpected error occurred.

[qwiqshot]

Yes, the post 2 above yours has a screenshot showing the error and just above it shows the actual folder and our cloud address. Mind you, this is a Local cloud, not something like Microsoft Cloud or some other hosting service.

[qwiqshot]

Water, This works (I changed some of the naming a bit to make more sense of it in my head), could you tell me if I can refine the code any please and by the way THANK YOU for the help, I greatly appreciate the guidance.

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

 

[water]

Looks good. As it works I would not change a bit  

[qwiqshot]

Ty Ty Ty TY

[qwiqshot]

Here is some amended code for saving aPowerPDF file the same name into the same directory as the saved Excel file, after it was saved from using _Excel_BookAttach

Func SavePDF()
      Sleep (750)
      send ("{ESC}")
      Local $oExcel = _Excel_Open() ; Connect to already running Excel instance
      $cName = ($oExcel.Worksheets(4).Range("J20").Value) ; Customers name
      $cAddress = ($oExcel.Worksheets(4).Range("I30").Value) ; customers address
      $string = $cName & " " & "(" & $oExcel.ActiveSheet.Name & ")" & " " & $cAddress
      $networkFolder = "\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\" & $cName & " " & "(" & $oExcel.ActiveSheet.Name & ")" & " " & $cAddress
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "You have no excel file open or you have already saved and renamed the file" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{S}")
      Sleep (500)
      Send ($networkFolder & "\" & $string)
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{ENTER}")
      Sleep (500)

EndFunc