wrote macro based on given jobnumber, finds path contract (excel workbook) is, open it, export sheet1 pdf, , close it. having been testing , work fine far.. can point more efficient way macro doing? perhaps without need open external workbook ?
public sub savemepdf() dim jobcat string, jobnumber string, jobnumbername string, mainpath string jobcat = sheet1.range("v2").text jobnumber = sheet1.range("u2").text jobnumbername = dir("c:\test\" & jobcat & "\" & jobnumber & "*", vbdirectory) mainpath = "c:\test\" & jobcat & "\" & jobnumbername 'set current active workbook (the source book) set wbthis = activeworkbook set wsthis = activesheet 'open workbook has same name sheet name set wbtarget = workbooks.open(mainpath & "\contracts\contract.xlsx") set wstarget = wbtarget.worksheets("sheet1") 'activate target worksheet wbtarget.activate 'export current active worksheet pathed pdf if jobnumber <> "" activesheet.exportasfixedformat type:=xltypepdf, filename:=mainpath & "\scheduling\estimatetest3.pdf" _ , quality:=xlqualitymedium, includedocproperties:=false, _ ignoreprintareas:=false, openafterpublish:=false 'close workbook wbtarget.close 'activate source book again wbthis.activate 'clear memory set wbtarget = nothing set wbthis = nothing end if end sub
for performance have 2 ideas you: 1. switch off screenupdating: @ start of code:
application.screenupdating = false before end sub @ end:
application.screenupdating = true - instead of activating opened workbook use assigned name like:
instead of activeworkbook use assigned workbook name , delete activate part.
in experience above 2 increase speed lot. please comment how worked if try out.
Comments
Post a Comment