Allow conditional formatting to update before continuing with rest of code?

[Warwiz]

I have code that runs multiple query's from the same Excel table and after each query is ran it saves it as a PDF file.
The code works great but on the larger query's it doesn't allow the conditional formatting to update before creating the PDF.
I have tried may ways of getting it to update the formatting using calculate, scroll off screen and back, wait function, doEvents etc with no success.
DoEvents allowed the smaller query's to update before saving but not the larger ones (even though I am probably not using it right)

I tried searching for a solution but there isn't much on this topic that I could find.
Maybe someone here can help?(oh and I am a very novice VBA'er .. trial, error, n google)
Using office 365 pro

After the query is ran, How do I allow time for any conditional formatting to update before performing the PDF save?

'Query1
Range("MATER_DISPO[#All]").AdvancedFilter Action _
:=xlFilterInPlace, CriteriaRange:=Range("AB1:AB2"), Unique:=False
Worksheets("Pending").Calculate
DoEvents
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="H:\Development" & Format(Date, "yyyy-mmm-dd") & Range("AB3").Value
'Query2
Range("MATER_DISPO[#All]").AdvancedFilter Action _
:=xlFilterInPlace, CriteriaRange:=Range("AC1:AC2"), Unique:=False
Worksheets("Pending").Calculate
DoEvents
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="H:\Development" & Format(Date, "yyyy-mmm-dd") & Range("AB3").Value

 

[offthelip]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

Have you tried:

Worksheets("Sheet1").EnableCalculation = False
Worksheets("Sheet1").EnableCalculation = True

which forces excel to do a recalculattion

 

[Warwiz]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

Hi offthelip,

Yes, I did try enableCalculation false/true (and I tried it again to be sure)
If I run the query manually and then recalculate it updates conditional formatting just fine, or scroll down and back up etc.

 

[Fazza]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

did you set application.screenupdating = false

 

[Warwiz]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

Hi Fazza,

Yes, in various different placements and in combination with calculations. Screen updating=False does not allow any conditional formats to update before saving the PDF file

 

[Fazza]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

Does setting 'application.screenupdating = true' before creating the pdf files fix the problem?

 

[Warwiz]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

No, nothing seems to have influenced allowing the formatting to update except when I threw in DoEvents. Then the shorter Query's began to work.
With the large one it only updates about half the column before it saves.
Its not like the larger one is much bigger, its 166 rows.

Sub Saveall()
    Range("MATER_DISPO[#All]").AdvancedFilter Action _
        :=xlFilterInPlace, CriteriaRange:=Range("AA1:AA2"), Unique:=False
        Worksheets("InRepair").Calculate
        DoEvents
        Application.ScreenUpdating = True
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="H:\Development\" & Format(Date, "yyyy-mmm-dd") & Range("AA3").Value

 

[offthelip]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

what about exporting the same pdf twice? Delete the first one before exporting the second one.

 

[Warwiz]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

I just tried that, deleting the 1st one then trying another.
Tried running it 3 times in a row and overwriting the file.
Part of the sort criteria is by the conditional format colors so would like to figure this out if possible before seeing if I can make due without the formatting.

 

[Fazza]

Re: Excel - How do I allow conditional formatting to update before continuing with rest of code?

If it isn't already, what if the conditional format is applied by the code?