VBA Excel - Sub is Printing old data

Question

I am encountering a problem when I am attempting to print an Excel worksheet. I have a sub that calls two other subs, see below:

    Sub AutoGen_NOW()

    Application.Calculation = xlCalculationAutomatic

    autoGen_refresh

    autoGen_invoiceStageGen

    End Sub

The subs called are

 1. autoGen_refresh
 2. autoGen_invoiceStageGen

The first sub called refreshes a workbook connection, while the second sub prints a worksheet that references the connection. My problem is, even though the printing sub occurs after the refresh sub, the printing sub prints the worksheet as it appeared before the refresh, not with the new data. When i look at the worksheet manually, after the print, it contains the new data.

Any help on this is much appreciated. I have added the two subs below:

Sub autoGen_refresh()

Dim LastAnnual As WorkbookConnection
Dim LastMonthly As WorkbookConnection
Dim oledbCn As OLEDBConnection

Set LastAnnual = ThisWorkbook.Connections("Staging_AnnualLastEntry")
Set LastMonthly = ThisWorkbook.Connections("Staging_MonthlyLastEntry")

'Refresh Annual Connection if Annual is selected - Refresh Monthly Connection if Monthly is selected
If MonthForm.OptionButton16.Value = True Then
    LastAnnual.Refresh
Else
    LastMonthly.Refresh
    MonthlyTemp
    MonthlyHide
End If

End Sub

Sub autoGen_invoiceStageGen()

Dim FolderPath As String
Dim sourceSheet As Worksheet    'This is the Worksheet where the data connection is
Dim i As Long


'Dim template worksheets
Dim annualRateWS    As Worksheet
Dim annualPSFWS     As Worksheet
Dim annualFlatWS    As Worksheet
Dim monthlyRateWS   As Worksheet

'Set template Worksheets
Set annualRateWS = Worksheets("00_ComRate_Template")
Set annualPSFWS = Worksheets("00_PSF_Template")
Set annualFlatWS = Worksheets("00_FlatAmount_Template")
Set monthlyRateWS = Worksheets("00_MONTHLYTEMPLATE")

'Set sourceSheet to Annual or Monthly table based on user input
Select Case MonthForm.OptionButton16.Value
    Case "True"
        Set sourceSheet = ThisWorkbook.Worksheets("Annual_InvoiceGen")
    Case "False"
        Set sourceSheet = ThisWorkbook.Worksheets("Monthly_InvoiceGen")
End Select



'Set path of directory all invoices are saved too
FolderPath = ("P:\Regis Profiles\00_MerchantDB\XX_TempDump\")




'Code used to stop screen flashing during macro operation -- This is set back to true after loop
Application.ScreenUpdating = False


'ANNUAL COMMISSION RATE - PDF GENERATION
If MonthForm.OptionButton16.Value = "True" And MonthForm.OptionButton7.Value = "True" Then
    annualRateWS.Visible = True
    annualRateWS.Activate
    ThisWorkbook.Connections("Staging_AnnualLastEntry").Refresh
    annualRateWS.Calculate
    Workbook_BeforePrint (False)
    'Application.Wait (Now + TimeValue("0:00:03"))

         For i = 32 To 43 'This Code Hides All Calculation Years that are out of range*****
                    ActiveSheet.Rows(i).Hidden = (ActiveSheet.Cells(i, 5).Value = 0)
         Next i



     annualRateWS.Activate
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & ThisWorkbook.Worksheets("REF_InvoiceGen").Range("$B$2").Value & Format(Now(), "yyyymmddhhmmss"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
             :=False, OpenAfterPublish:=True
         annualRateWS.Visible = False
 End If


'ANNUAL COMMISSION PSF - PDF GENERATION
If MonthForm.OptionButton16.Value = True And MonthForm.OptionButton8.Value = "True" Then
    annualPSFWS.Visible = True
    annualPSFWS.Activate
    ThisWorkbook.Connections("Staging_AnnualLastEntry").Refresh
    annualPSFWS.Calculate
    'Application.Wait (Now + TimeValue("0:00:03"))


    annualPSFWS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & ThisWorkbook.Worksheets("REF_InvoiceGen").Range("$B$2").Value & Format(Now(), "yyyymmddhhmmss"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
             :=False, OpenAfterPublish:=True
         annualPSFWS.Visible = False
End If


'ANNUAL FLAT AMOUNT = PDF GENERATION
If MonthForm.OptionButton16.Value = True And MonthForm.OptionButton9.Value = "True" Then
    annualFlatWS.Visible = True
    annualFlatWS.Activate
    ThisWorkbook.Connections("Staging_AnnualLastEntry").Refresh
    annualFlatWS.Calculate
    'Application.Wait (Now + TimeValue("0:00:03"))


    annualFlatWS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & ThisWorkbook.Worksheets("REF_InvoiceGen").Range("$B$2").Value & Format(Now(), "yyyymmddhhmmss"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
             :=False, OpenAfterPublish:=True
         annualFlatWS.Visible = False
End If

'MONTHLY COMMISSION RATE - PDF GENERATION
If MonthForm.OptionButton16.Value = "False" And MonthForm.OptionButton7.Value = "True" Then
    monthlyRateWS.Visible = True
    monthlyRateWS.Activate
    monthlyRateWS.Calculate



    MonthlyTemp
    MonthlyHide

    'Application.Wait (Now + TimeValue("0:00:03"))

    monthlyRateWS.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & ThisWorkbook.Worksheets("REF_InvoiceGen").Range("$B$2").Value & Format(Now(), "yyyymmddhhmmss"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
             :=False, OpenAfterPublish:=True
         monthlyRateWS.Visible = False
End If


sourceSheet.Visible = False


'Code used to set screen updating BACK TO True
Application.ScreenUpdating = True


End Sub

Can you post the code for the two subs in question? There is absolutely no way to answer this question without seeing that code. Your calling sub should have nothing to do with it. — Kyle
Are your connections set to refresh in the background? That may be the problem. — Tim Williams
That was it Tim! Thank you, I had background refresh enabled on the connections. — E. Monk

E. Monk E. Monk · Accepted Answer · 2016-08-25T18:09:39

Tim Williams solved the problem. Background refresh was enabled on the connections. After disabling this, everything worked properly.

VBA Excel - Sub is Printing old data

1 Answers