EXCEL VBA : Loop is working but not refreshing before printing to PDF file

Question

Good Morning everybody!

I have an issue concerning my VBA Code. I actualy just want to create a loop that prints PDFs that are based on the same background template (which is in the sheet called AFFIDAVIT CREATOR) replacing some 4 boxes (labels and images) from the INPUT Sheet.

So far, the loop is working properly. Only problem: It generates the PDF files according to the given name (variable r) but refreshes the sheet AFTER exporting to PDF. Result: Mutliple files with different names but they all display the same :(

Any ideas?

That's my code:

Private Sub TryMe()
Dim r As Long
Dim strCap As String
Dim strCap2 As String
r = 4

    Do Until Sheets("INPUT").Cells(r, 3).Value = ""

    strCap = Sheets("INPUT").Cells(r, 3).Value
    Sheets("AFFIDAVIT CREATOR").Label1.Caption = strCap

strCap2 = Sheets("INPUT").Cells(r, 5).Value
Sheets("AFFIDAVIT CREATOR").Label2.Caption = strCap2

If Sheets("INPUT").Cells(r, 4) = "OE" Then
    Sheets("AFFIDAVIT CREATOR").Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\OE_Logo.jpg")
Else
    Sheets("AFFIDAVIT CREATOR").Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\SF_Logo.jpg")
End If

If Sheets("INPUT").Cells(r, 6) = "OE" Then
    Sheets("AFFIDAVIT CREATOR").Image2.Picture = LoadPicture(ActiveWorkbook.Path & "\OE_Logo.jpg")
Else
    Sheets("AFFIDAVIT CREATOR").Image2.Picture = LoadPicture(ActiveWorkbook.Path & "\SF_Logo.jpg")
End If


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, From:=1, To:=1, FileName:=ThisWorkbook.Path & "\" & Sheets("INPUT").Cells(r, 3) & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

Sheets("AFFIDAVIT CREATOR").Calculate

r = r + 1

Loop

End Sub

what for you have this line Sheets("AFFIDAVIT CREATOR").Calculate and why it isn't before .ExportAsFixedFormat ...?? — Kazimierz Jawor
– Kazimierz Jawor, Commented Jul 4, 2013 at 9:27
It actually is/was. I even did a childish thing and copy-pasted it 20 times all over the code and it is not working. It is still refreshing after printing all the pages! Even tried that: If Sheets("AFFIDAVIT CREATOR").Calculate Then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, From:=1, To:=1, FileName:=ThisWorkbook.Path & "\" & Sheets("INPUT").Cells(r, 3) & ".pdf" _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=False Else Sheets("AFFIDAVIT CREATOR").Calculate End If — YannickHelmut
– YannickHelmut, Commented Jul 4, 2013 at 9:34
I don't know why it's happening but try the following tests: B) insert Stop before you .ExportAsFixedFormat to check if pictures are loaded after a few sec, if so run the sub further by pressing F5 in IDE. B) what if you move .ExportAsFixedFormat to separate Sub onlyExport() and call it from current one in the same place like Call onlyExport... — Kazimierz Jawor
– Kazimierz Jawor, Commented Jul 4, 2013 at 9:42
When I stop it is refreshing after 3 seconds or so. Isolating it to a seperated sub is not working, I wlready tried that :( Any idea of what I could to except call a wait function (that solution didn't work) — YannickHelmut
– YannickHelmut, Commented Jul 4, 2013 at 10:08
I'll give you temporary answer... it's to long to put in comments... — Kazimierz Jawor
– Kazimierz Jawor, Commented Jul 4, 2013 at 10:12

Kazimierz Jawor · Accepted Answer · 2013-07-04 10:15:12Z

5

According to comments, if Stop worked in the way that you need approx. 3 sec to get your picture loaded you could try to use a wait workaround which looks as follow:

Dim Start As Single
Start = Timer
'wait 5 sec...
Do While Start + 5 > Timer
    DoEvents
Loop

Add the code just before .ExportAsFixedFormat. Above I set waiting time to 5 sec. which you could change after some tests.

answered Jul 4, 2013 at 10:15

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

YannickHelmut Over a year ago

Works perfectly with a delay of 0.5 seconds. It slows down the process but it's better than no process at all!

Floris Over a year ago

Nice solution! This suggests the Loadpicture command runs in its own thread... I wonder if there is a way to "wait for it to finish" rather than wait a fixed time. @KazJaw - do you have any ideas?

Kazimierz Jawor Over a year ago

@Floris, unfortunately, if I knew I'd post it here as answer... But I can admit, I didn't make max effort to search for something which you described.

YannickHelmut Over a year ago

@Floris Yes it works to generate PDFs. Unfortunately it doesn't work for a regular printing method (crashed after the first page...)

Floris Over a year ago

@YannickHelmut can you update your question to show "a regular printing method"? It would be good if you made the code as simple as possible. Change one cell, print (rather than the whole affidavit formatting thing). Usually when you reduce the code to the minimum that reproduces the error, the solution presents itself...

|

Collectives™ on Stack Overflow

EXCEL VBA : Loop is working but not refreshing before printing to PDF file

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related