Attach to Existing Excel file

[qwiqshot]

Hi

I have this script as part of my hotkey compilation (see below), that auto enters the customers address into what ever field I have selected, be it Google Earth or Google Maps etc, it works flawlessly, but I would like to amend the code to be useful regardless of the name of the excel file.

Func WhereTo()
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm" ; Create application object and open an example workbook
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      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)
      Send ($oWorkbook.Worksheets(4).Range("I30").Value)
      Sleep (250)
      Send ("{ENTER}")

EndFunc

You see, the file starts out named AsphaltEstimating.xlsm, the script attaches fine now but I later run another script (see below) to save the file into a directory and name the file according to the customers details, after the file is saved, the top script breaks as it is no longer named AsphaltEstimating.xlsm.

Func SaveAsphalt()
      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, "Error", "Something went wrong, start the script over and 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\2016 Bids\Marks Bids\" & $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!", "Case file has successfully been saved as '" & $mFolder & "'.")

EndFunc

Occasionally I have the need to re view the address, so i'm trying to set the top script to be useful regardless of the file name so I have no more hotkeys then currently. I have tried to use WinGetProcess but i'm getting nowhere. Is there an easy way to modify the top script to do what I want or am I looking at a re write?

[water]

Will check after my vacation end of this week. 

[qwiqshot]

I found another need to figure this out. After I have saved my excel file I also save my PDF file I have created along side the excel file, as the exact same name, into the exact same folder, but it will also require accessing the file *after* being saved. This is what I have now, to name the PDF and it too works, but only saves the file to the root of where the folder would be created (\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\) and not in the case folder like the one that Func SaveAsphalt() creates.

Func SavePDF()
      Sleep (750)
      send ("{ESC}")
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm" ; Connect to AsphaltEstimating sheet
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      $sDir = "\\MYCLOUDEX2ULTRA\Bids\2016 Bids\Marks Bids\"
      $fName = ($oWorkbook.Worksheets(4).Range("J20").Value) ; Customers name
      $cAddress = ($oWorkbook.Worksheets(4).Range("I30").Value) ; customers address
      $sFilePath = $sDir & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & " " & $cAddress
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Something went wrong, start the script over and try again" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{S}")
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", $sFilePath)
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{ENTER}")
      Sleep (500)

EndFunc

 

[l3ill]

The answer to your first question is to use the FileOpenDialog to open any xls xlsx file. No rewrite necessary...

[qwiqshot]

Easy change. This does the trick.

Func test()
      Local $oExcel = _Excel_Open() ; Connect to already running Excel instance
      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)
      Send ($oExcel.Worksheets(4).Range("I30").Value)
      Sleep (250)
      Send ("{ENTER}")
      Sleep (500)

EndFunc

Took the inspiration from one of my many printing functions....

Func LegalCopies()
      Local $oExcel = _Excel_Open() ; Connect to already running Excel instance
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      _Excel_Print($oExcel, "A1:M55", 2, "Xerox WorkCentre 7556 PS Contract Paper") ; print 2 copies of contract on contract paper for mailing
      Sleep (500)

EndFunc

Now works regardless if it's a newly created (not saved yet) excel file or one that has been saved and renamed etc etc.

Thanks again

[qwiqshot]

Modified email function for reference too, for use with GMail layout, regardless of newly created or existing file names....

expandcollapse popup

Func WriteEmail()
      Sleep (750)
      Local $oExcel = _Excel_Open() ; Connect to already running Excel instance
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error writing email", "Something went wrong, cannot continue!" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      send ($oExcel.Worksheets(4).Range("E3").Value) ; email address
      Sleep (500)
      Send ("{TAB}")
      Sleep (125)
      Send ("{TAB}")
      send ($oExcel.Worksheets(4).Range("E4").Value) ; customer address
      Send ("{TAB}")
      Sleep (125)
      Send ("Hello ")
      Sleep (125)
      send ($oExcel.Worksheets(4).Range("A6").Value) ; customers name
      Sleep (75)
      Send ("{ENTER}")
      Send ("{ENTER}")
      Sleep (75)
      send ("Attached you will find our proposal for ")
      Sleep (125)
      send ($oExcel.Worksheets(4).Range("E4").Value); customer address
      Send ("{ENTER}")
      sleep (75)
      $File = "C:\Excel\z-eMail3.txt"
      $Open = FileOpen($File, 0)
      $Read = FileRead($Open)
      sleep (75)
      ClipPut($Read)
      sleep (75)
      FileClose($Open)
      Sleep (150)
      Send ("^v")

EndFunc