qwiqshot

Attach to Existing Excel file

6 posts in this topic

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?

Share this post


Link to post
Share on other sites



Will check after my vacation end of this week. 


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

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

 

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