EXCEL VBA : Loop is working but not refreshing bef

问题:

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

回答1:

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.