Excel Save As PDF And Overwrite Existing -


here problem have encountered.

i have created excel project. @ point in project save current workbook pdf , update existing workbook log information , hyperlink pdf (step1).

at point in project re-save project same pdf (to overwrite existing pdf). @ point try re-save pdf receive:

run-time error-1004

document not saved, may opened or error may have been encountered. here 2 scripts use.

i think maybe might have second part of step1 script (when opening workbook , updating log info, may not closing properly)

please appreciated, continue trouble shoot.

step1:

sub step1() dim rng range dim nwb workbook dim filename string dim var dim var1 dim var2 dim var3 dim var4 dim var5 dim var6 var1 = frmsetup.cmbauditor.text var2 = frmsetup.lblsequence.caption var3 = frmsetup.cmbtrimstyle.text var = "seq-" & frmsetup.lblsequence.caption & " " var4 = frmsetup.lbldate.caption filename = var & var4  sheets(array("end results", "driver seat", "passenger seat", "40% seat", "60% seat", "rsc seat")).select activesheet.exportasfixedformat type:=xltypepdf, filename:= _     "h:\applications\seat audit\query results\seat audit - pdf\" & filename & ".pdf", quality:=xlqualitystandard, _     includedocproperties:=true, ignoreprintareas:=false, openafterpublish:= _     false end application.screenupdating = false application.windowstate = xlmaximized

this next part of step1, opens existing workbook , adds log information including hyperlink pdf:

set nwb = workbooks.open("h:\applications\seat audit\log files\seat audit log.xlsm") sheets("seat audit log") nextrow = range("b" & rows.count).end(xlup).row + 1 cells(nextrow, 1).value = var1 cells(nextrow, 2).value = var2 cells(nextrow, 3).value = var3 cells(nextrow, 4).value = var4 set rng = .range("e" & nextrow) rng.parent.hyperlinks.add anchor:=rng, address:="h:\applications\seat audit\query results\seat audit - pdf\" & filename & ".pdf", texttodisplay:="click here!" end application.screenupdating = true activeworkbook.save activewindow.close end sub

this step2 re-save pdf overwrite existing pdf step1:

sub step2() dim filename string dim var dim var4 var = "seq-" & frmsetup.lblsequence.caption & " " var4 = frmsetup.lbldate.caption filename = var & var4  sheets(array("end results", "driver seat", "passenger seat", "40% seat", "60% seat", "rsc seat", "actions")).select activesheet.exportasfixedformat type:=xltypepdf, filename:= _     "h:\applications\seat audit\query results\seat audit - pdf\" & filename & ".pdf", quality:=xlqualitystandard, _     includedocproperties:=true, ignoreprintareas:=false, openafterpublish:= _     false end  end sub

most of with blocks appear poorly constructed , may contributing errors (or, maybe not, it's hard certain). generally, you'll qualify objects with object. in case, don't seem doing except 1 or 2 lines of code.

you don't need variables represent form controls, , doing way have done making code more complicated read. i've revised step1 accordingly.

however, main problem: when trying save file, first need check if same file exists, , if so, delete it.

sub step1() dim rng range dim nwb workbook dim filename string   filename = "seq-" & frmsetup.lblsequence.caption & " " & frmsetup.lbldate.caption '## add path , extension filename filename = "h:\applications\seat audit\query results\seat audit - pdf\" & filename & ".pdf"  sheets(array("end results", "driver seat", "passenger seat", "40% seat", "60% seat", "rsc seat")).select activesheet.exportasfixedformat type:=xltypepdf, filename:= _     filename, quality:=xlqualitystandard, _     includedocproperties:=true, ignoreprintareas:=false, openafterpublish:= _     false end application.screenupdating = false application.windowstate = xlmaximized  **'this next part of step1, opens existing workbook , adds log information including hyperlink pdf'**  set nwb = workbooks.open("h:\applications\seat audit\log files\seat audit log.xlsm") sheets("seat audit log")     nextrow = range("b" & .rows.count).end(xlup).row + 1     .cells(nextrow, 1).value = frmsetup.cmbauditor.text     .cells(nextrow, 2).value = frmsetup.lblsequence.caption     .cells(nextrow, 3).value = frmsetup.cmbtrimstyle.text     .cells(nextrow, 4).value = frmsetup.lbldate.caption     .set rng = .range("e" & nextrow)     rng.parent.hyperlinks.add anchor:=rng, address:=filename, texttodisplay:="click here!" end application.screenupdating = true activeworkbook.save activewindow.close end sub  sub step2() dim filename string  filename = "seq-" & frmsetup.lblsequence.caption & " " & frmsetup.lbldate.caption '## add path , extension filename filename = "h:\applications\seat audit\query results\seat audit - pdf\" & filename & ".pdf"  '## check see if file exists, , delete if if dir(filename) <> vbnullstring     kill filename end if  sheets(array("end results", "driver seat", "passenger seat", "40% seat", "60% seat", "rsc seat", "actions")).select activesheet.exportasfixedformat type:=xltypepdf, filename:= _     filename, quality:=xlqualitystandard, _     includedocproperties:=true, ignoreprintareas:=false, openafterpublish:= _     false end  end sub

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more