qwiqshot Posted October 24, 2016 Share Posted October 24, 2016 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? Link to comment Share on other sites More sharing options...
water Posted October 24, 2016 Share Posted October 24, 2016 Will check after my vacation end of this week. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
qwiqshot Posted October 26, 2016 Author Share Posted October 26, 2016 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 Link to comment Share on other sites More sharing options...
l3ill Posted October 26, 2016 Share Posted October 26, 2016 The answer to your first question is to use the FileOpenDialog to open any xls xlsx file. No rewrite necessary... My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
qwiqshot Posted October 27, 2016 Author Share Posted October 27, 2016 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 Link to comment Share on other sites More sharing options...
qwiqshot Posted October 27, 2016 Author Share Posted October 27, 2016 Modified email function for reference too, for use with GMail layout, regardless of newly created or existing file names.... expandcollapse popupFunc 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now