Jump to content

Copy name from Excel Cell, Re arrange to be pasted elsewhere

qwiqshot
 Share

Recommended Posts

water Universalist

water

Posted
Posted 
$sFilePath = "\\MYCLOUDEX2ULTRA\Bids\2017 Bids\Mark\" & $fName & " " & "(" & $oWorkbook.ActiveSheet.Name & ")" & $cAddress

Remove all unneeded parenthesis.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites
qwiqshot Wayfarer

qwiqshot

Posted
  • Author
Posted (edited)

Yes, it is the whole script. I will test it out as saving locally instead of a network drive and see if that changes things.

Edit:

Removing the  parenthesis did not speed it up.

Took just as long saving locally as it does across the network :blink:

Edited by qwiqshot
Link to comment
Share on other sites
qwiqshot Wayfarer

qwiqshot

Posted
  • Author
Posted

I know it's still very poor coding. I'm still learning as I go. This is the entire code top to bottom. 

; ------------------ALL start with ***Control + Alt + Shift*** -------------------
; ################################################################################
; ---------------------------------------Covers--------------------------------------
; --------------------------------------On Paper-------------------------------------
; Z   "PavingCover"         ; Print only cover page                        <<-- Works
; D   "DrainCover"          ; Print only cover page                        <<-- Works
; ---------------------------------------Covers--------------------------------------
; ---------------------------------------In PDF--------------------------------------
; M   "PDFCover"            ; Print contract to PDF                        <<-- Works
; --------------------------------------Contracts------------------------------------
; --------------------------------------On Paper-------------------------------------
; A   "PrintDocs"           ; Print ALL above pages                        <<-- Works
; R   "SealDocs"            ; Print ALL paes But Numbers                   <<-- Works
; O   "OfficeCopy"          ; Print ony 1 office copy                      <<-- Works
; L   "LegalCopies"         ; Print only legal copies                      <<-- Works
; N   "BidNumbers"          ; Print only proposal numbers                  <<-- Works
; --------------------------------------Contracts------------------------------------
; ---------------------------------------In PDF--------------------------------------
; C   "ContractPDF"         ; Send contract to PDF                         <<-- Works
; T   "Terms"               ; Insert Terms into PDF                        <<-- Works
; -------------------------------------Bookmarking-----------------------------------
; 3   "Bookmarks3"          ; Bookmarks (No Diagram)                       <<-- Works
; 4   "Bookmarks4"          ; Bookmarks (1 Contracts)                      <<-- Works
; 5   "Bookmarks5"          ; Bookmarks (2 Contracts)                      <<-- Works
; 6   "Bookmarks6"          ; Bookmarks (2 Contracts, 2 Diagrams)          <<-- Works
; ----------------------------------------E-Mail-------------------------------------
; E   "WriteEmail"          ; Write all email                              <<-- Works
; W   "Address"             ; Enter address                                <<-- Works
; -----------------------------------Imagry & Cropping-------------------------------
; G   "GoogleEarth"         ; Google Earth Snapshot                        <<-- Works
; X   "Crop"                ; Crop snapshot in PDF                         <<-- Works
; P   "CropPrint"           ; Crop snapshot in PDF and print 2 copies      <<-- Works
; .   "Print"               ; Print the currently visible PDF Page/Image   <<-- Works
; --------------------------------------Exit/Save------------------------------------
; Q   "Quit"                ; Exits this hotkey script                     <<-- Works
; S   "Save"                ; Saves the active Excel Workbook to server    <<-- Works


HotKeySet ("^!+{Z}", "PavingCover")     ; Print only cover page                        <<-- Works
HotKeySet ("^!+{D}", "DrainCover")      ; Print only cover page                        <<-- Works
HotKeySet ("^!+{O}", "OfficeCopy")      ; Print ony 1 office copy                      <<-- Works
HotKeySet ("^!+{L}", "LegalCopies")     ; Print only legal copies                      <<-- Works
HotKeySet ("^!+{N}", "BidNumbers")      ; Print only proposal numbers                  <<-- Works
HotKeySet ("^!+{A}", "PrintDocs")       ; Print ALL above pages                        <<-- Works
HotKeySet ("^!+{R}", "SealDocs")        ; Print ALL above pages                        <<-- Works
HotKeySet ("^!+{M}", "PDFCover")        ; Print contract to PDF                        <<-- Works
HotKeySet ("^!+{G}", "GoogleEarth")     ; Capture Google Earth Snapshot                <<-- Works
HotKeySet ("^!+{X}", "Crop")            ; Crop & Print Snapshot                        <<-- Works
HotKeySet ("^!+{P}", "CropPrint")       ; Crop & Print Snapshot                        <<-- Works
HotKeySet ("^!+{C}", "ContractPDF")     ; Send contract to PDF                         <<-- Works
HotKeySet ("^!+{T}", "Terms")           ; Insert Terms into PDF                        <<-- Works
HotKeySet ("^!+{6}", "Bookmarks6")      ; (2 Contracts, 2 Diagrams)                    <<-- Works
HotKeySet ("^!+{5}", "Bookmarks5")      ; (2 Contracts)                                <<-- Works
HotKeySet ("^!+{4}", "Bookmarks4")      ; (1 Contracts)                                <<-- Works
HotKeySet ("^!+{3}", "Bookmarks3")      ; (No Diagrams)                                <<-- Works
HotKeySet ("^!+{E}", "WriteEmail")      ; Write Entire Email                           <<-- Works
HotKeySet ("^!+{Q}", "Quit")            ; Exits this hotkey script                     <<-- Works
HotKeySet ("^!+{W}", "Address")         ; Places the address from Excel into any field <<-- Works
HotKeySet ("^!+{S}", "Save")            ; Saves the active Excel Workbook to server    <<-- Works
HotKeySet ("^!+{.}", "Print")            ; Saves the active Excel Workbook to server    <<-- Works


; *******************************
; -----------Includes------------
; *******************************
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Clipboard.au3>


While 1
   Sleep (50)

WEnd


Func PavingCover()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out 1 copy of cover sheet for mailing on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Cover").Range("A1:K51"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (500)

EndFunc



Func DrainCover()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out 1 copy of numbers on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Drainage Cover").Range("A1:L55"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (500)

EndFunc



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

EndFunc


Func OfficeCopy()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print 1 copy of conttract for office on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (500)

EndFunc



Func BidNumbers()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out 1 copy of numbers on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Bid Number").Range("A1:H30"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (500)

EndFunc


Func PrintDocs()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out 1 copy of cover sheet for mailing on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Cover").Range("A1:K51"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (750)

      ; *****************************************************************************
      ; print 2 copies of contracts on contract paper for mailing
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 2, "Xerox WorkCentre 7556 PS Contract Paper")
      Sleep (750)

      ; *****************************************************************************
      ; print 1 copy of conttract for office on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (750)

      ; *****************************************************************************
      ; print out 1 copy of numbers on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Bid Number").Range("A1:H30"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (750)

EndFunc



Func SealDocs()
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
          Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out 1 copy of cover sheet for mailing on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Cover").Range("A1:K51"), 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (750)

      ; *****************************************************************************
      ; print 2 copies of contracts on contract paper for mailing
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 2, "Xerox WorkCentre 7556 PS Contract Paper")
      Sleep (750)

      ; *****************************************************************************
      ; print 1 copy of conttract for office on plain paper
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 1, "Xerox WorkCentre 7556 PS Plain Paper")
      Sleep (750)

EndFunc


Func Bookmarks6()
      Sleep (750)
      ; *****************************************************************************
      ; Now creating bookmarks for all pages correctly ordered in PDF Converter Professional
      ; *****************************************************************************
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (750)
      ; *****************************************************************************
      ; ensure 1st page is in front
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate bookmark panel
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; finalise created bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ESC}")
      Sleep (500)
      ; *****************************************************************************
      ; now delete remnant bookmarks to ensure properly assigned once done
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{Del 25}")
      Sleep (250)
      ; *****************************************************************************
      ; ensure again page one is to begining by flipping back
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ; *****************************************************************************
      ; now start creating real bookmarks
      ; *****************************************************************************
      ; initiate 1st bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Cover")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 2nd bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Diagram")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate 3rd bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate 4th bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Diagram")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate 5th bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate 6th bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Terms")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; go to 1st bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{UP 5}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^s")
      Sleep (250)
      WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

   EndFunc



Func Bookmarks5()
      Sleep (750)
      ; *****************************************************************************
      ; Now creating bookmarks for all pages correctly ordered in PDF Converter Professional
      ; *****************************************************************************
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (250)
      ; *****************************************************************************
      ; ensure 1st page is in front
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate bookmark panel
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; finalise created bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ESC}")
      Sleep (500)
      ; *****************************************************************************
      ; now delete remnant bookmarks to ensure properly assigned once done
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{Del 25}")
      Sleep (250)
      ; *****************************************************************************
      ; ensure again page one is to begining by flipping back
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ; *****************************************************************************
      ; now start creating real bookmarks
      ; *****************************************************************************
      ; initiate 1st bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Cover")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 2nd bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Diagram")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 3rd bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 4th bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 5th bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Terms")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; go to 1st bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{UP 5}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^s")
      Sleep (250)
      WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

   EndFunc



Func Bookmarks4()
      Sleep (750)
      ; *****************************************************************************
      ; Now creating bookmarks for all pages correctly ordered in PDF Converter Professional
      ; *****************************************************************************
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (750)
      ; *****************************************************************************
      ; ensure 1st page is in front
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (500)
      ; *****************************************************************************
      ; initiate bookmark panel
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (500)
      ; *****************************************************************************
      ; finalise created bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ESC}")
      Sleep (500)
      ; *****************************************************************************
      ; now delete remnant bookmarks to ensure properly assigned once done
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{Del 25}")
      Sleep (250)
      ; *****************************************************************************
      ; ensure again page one is to begining by flipping back
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ; *****************************************************************************
      ; now start creating real bookmarks
      ; *****************************************************************************
      ; initiate 1st bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Cover")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 2nd bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Diagram")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 3rd bookmark process
      ; *****************************************************************************
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      Sleep (250)
      ; *****************************************************************************
      ; initiate 4th bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (250)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Terms")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ; *****************************************************************************
      ; go to 1st bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{UP 5}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^s")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

EndFunc



Func Bookmarks3()
   Sleep (750)
      ; *****************************************************************************
      ; Now creating bookmarks for all pages correctly ordered in PDF Converter Professional
      ; *****************************************************************************
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (75)
      ; *****************************************************************************
      ; ensure 1st page is in front
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (75)
      ; *****************************************************************************
      ; initiate bookmark panel
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (75)
      ; *****************************************************************************
      ; finalise created bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (75)
      ; *****************************************************************************
      ; now delete remnant bookmarks to ensure properly assigned once done
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{Del 25}")
      Sleep (75)
      ; *****************************************************************************
      ; ensure again page one is to begining by flipping back
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (75)
      ; *****************************************************************************
      ; now start creating real bookmarks
      ; *****************************************************************************
      ; initiate 1st bookmark process
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (75)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Cover")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (75)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 2nd bookmark process
      ; *****************************************************************************
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (75)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Proposal")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (75)
      ; *****************************************************************************
      ; proceed to next page to bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{PGDN}")
      ; *****************************************************************************
      ; initiate 3rd bookmark process
      ; *****************************************************************************
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{b}")
      Sleep (75)
      ; *****************************************************************************
      ; enter bookmark name & confirm
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "Terms")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (75)
      ; *****************************************************************************
      ; go to 1st bookmark
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{HOME}")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{UP 5}")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      Sleep (75)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^s")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

   EndFunc



Func PDFCover()
      ; *****************************************************************************
      ; begin by ensuring no previous PDF is on desktop
      ; *****************************************************************************
      FileDelete("C:\Users\Mark\Desktop\AsphaltEstimating.pdf")
      ; *****************************************************************************
      ; Create application object or connect to an already running Excel instance
      ; *****************************************************************************
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
       Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print "Cover" to PDF Converter Professional 7.2
      ; *****************************************************************************
      _Excel_Print($oExcel,$oExcel.Worksheets("Cover").Range("A1:K51"), 1, "Nuance PDF")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)
EndFunc


Func GoogleEarth()
      sleep (500)
      WinActivate ("Google Earth Pro")
      WinWait ("Google Earth Pro")
      WinWaitActive ("Google Earth Pro")
      sleep (500)
      Send ("^+{F12}")
      Sleep (500)

EndFunc



Func Crop()
      Sleep (500)
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (750)
      ; *****************************************************************************
      ; ensure last page is in front
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{END}")
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{C}") ; Crop
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{W}") ; Remove White Margins
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{C}") ; select current page
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}") ; confirm
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)
EndFunc



Func CropPrint()
      Sleep (500)
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (750)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{END}") ; ensure last page is in front
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{C}") ; Crop
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{W}") ; Remove White Margins
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{C}") ; select current page
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}") ; confirm
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{p}") ; Initiate Print
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{END}") ; choose last printer in lineup
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{w}") ; choose current view
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{TAB}") ; tab to enter desired copies
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "2") ; enter amount of copies to print
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}") ; confirm selections
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{s}") ;  save file
      Sleep (250)
      WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      Sleep (250)
      WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

EndFunc



Func Print()
      Sleep (750)
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard", "", 8)
      WinSetState("AsphaltEstimating - Nuance Power PDF Standard", "", @SW_MAXIMIZE)
      Sleep (750)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{END}")
      Sleep (500)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{p}") ; Initiate Print
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{END}") ; choose last printer in lineup
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!{w}") ; choose current view
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{TAB}") ; tab to enter desired copies
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "2") ; enter amount of copies to print
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}") ; confirm selections
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

EndFunc



Func ContractPDF()
      ; *****************************************************************************
      ; Create application object or connect to an already running Excel instance
      ; *****************************************************************************
      Local $oExcel = _Excel_Open()
      If Not IsObj($oExcel) Then
       Exit MsgBox(0, "Error", "_Excel_Open()")
      EndIf
      ; *****************************************************************************
      ; print out Contract page to PDF (PDF Converter Professional)
      ; *****************************************************************************
      _Excel_Print($oExcel, "A1:M55", 1, "Nuance PDF")
      WinWaitActive ("AsphaltEstimating - Nuance Power PDF Standard")
      Sleep (250)
      ; *****************************************************************************
      ; save work and exit
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{s}")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

   EndFunc


Func Terms()
      Sleep (750)
      ; *****************************************************************************
      ; insert Terms & set to last page, then save
      ; *****************************************************************************
      WinActivate ("AsphaltEstimating - Nuance Power PDF Standard")
      sleep (125)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^+{I}")
      Sleep (1500)
      ; *****************************************************************************
      ; enters document location and file name
      ; *****************************************************************************
      Send ("C:\Users\Mark\Desktop\z-Terms 2016.pdf")
      Sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "!g")
      sleep (250)
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "{ENTER}")
      sleep (250)
      ; *****************************************************************************
      ; save work and exit
      ; *****************************************************************************
      ControlSend ("AsphaltEstimating - Nuance Power PDF Standard", "", "", "^{s}")
      ; Sleep (250)
      ; WinClose ("AsphaltEstimating - Nuance Power PDF Standard")
      ; WinWaitClose ("AsphaltEstimating - Nuance Power PDF Standard", "", 500)

EndFunc



Func WriteEmail()
      ; Create application object and open an example workbook
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm"
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook) ; Attach to Workbook
      send ($oWorkbook.Worksheets(4).Range("E3").Value) ; email address
      Sleep (1000)
      Send ("{TAB}")
      Sleep (500)
      Send ("{TAB}")
      send ($oWorkbook.Worksheets(4).Range("E4").Value) ; customer address
      Send ("{TAB}")
      Sleep (500)
      Send ("Hello ")
      Sleep (250)
      send ($oWorkbook.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 ($oWorkbook.Worksheets(4).Range("E4").Value); customer address
      Send ("{ENTER}")
      sleep (75)
      $File = @DesktopDir & ".\z-eMail3.txt"
      $Open = FileOpen($File, 0)
      $Read = FileRead($Open)
      sleep (75)
      ClipPut($Read)
      sleep (75)
      FileClose($Open)
      Sleep (150)
      Send ("^v")
      Sleep (750)

EndFunc


Func Address()
   ; Create application object and open an example workbook
      Local $sWorkbook = "c:\Excel\AsphaltEstimating.xlsm"
      Local $oExcel = _Excel_Open()
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Send ($oWorkbook.Worksheets(4).Range("I30").Value)
      Sleep (500)
      Send ("{ENTER}")

EndFunc



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


Func Quit()
      Exit

EndFunc

Very bottom is the one I am experiencing the slow saving issue on.

Link to comment
Share on other sites
  • 2 weeks later...
qwiqshot Wayfarer

qwiqshot

Posted
  • Author
Posted (edited)

Found out why this was taking soooo long to save.

Changed my code from 

_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlWorkbookDefault, True)

to this 

_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbookMacroEnabled, True)

now takes even less time then if I manually saved. I noticed that my button macros in my excel files were all broken after returning to a file a few days after making it and researched what Water told me in another post and discovered that the reason it was taking so long to save was that it was changing ever single macro for every button on 70+ pages to all end in xlsx instead of xlsm. So after I change the code I recalled the same buttons and they now function.

 

Example of how they did look vs how they now look

Incorrectly named macros
'Reed, Jennifer (Res Seal) 17860 Suzanne Ride Dr Wildwood, MO 63038.xlsx'!Home

Correctly named macros
'Reed, Jennifer (Res Seal) 17860 Suzanne Ride Dr Wildwood, MO 63038.xlsm'!Home

Edited by qwiqshot
Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...