Macro doesn't refresh

[JeffR]

I have a spreadsheet that I'm trying to wakthrough a list of customers and
print a letter and statement for each.

I have a range named 'RowIndex' which is used by VLOOKUP statements and a MS
SQLQuery to merge some information.

The problem is that it loops through the list just fine but even though I
have auto calc on it doesn't seem to refresh the spreadsheet between loops.

Any ideas?

Code I'm using is below.



Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer

Sheets("PastDueList").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")

If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the
ending row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow
Range("RowIndex") = i
Worksheets("Letter").Activate
ActiveWorkbook.RefreshAll
ActiveSheet.PrintPreview
Worksheets("Statement").Activate
ActiveSheet.PrintOut
Next i
Worksheets("PastDueList").Activate
Range("RowIndex").Select
End Sub

 

[Joel]

I don't know hwat the statement below really means. I go it from the remarks
in Refreshall

Objects that have the BackgroundQuery property set to True are refreshed in
the background.

I think it mean if TRUE then it is refresh in background mode which means it
will wait until the macro stops. Try adding the line DoEvents to the code
which will let the background events to run. Not sure if DoEvents will allow
background events to run. Let me know.

ActiveWorkbook.RefreshAll
DoEvents

 

[Don Guillett]

Try it this way
Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer
'==============
'new code
application.enableevents=false
ActiveWorkbook.RefreshAll
application.enableevents=false
'===========

'I don't understand a lot of the rest. What are you trying to do?
'You do NOT need to select a sheet to print it.

Sheets("PastDueList").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")

If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the
ending row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow
Range("RowIndex") = i
Worksheets("Letter").Activate
'ActiveWorkbook.RefreshAll
ActiveSheet.PrintPreview
Worksheets("Statement").Activate
ActiveSheet.PrintOut
Next i
Worksheets("PastDueList").Activate
Range("RowIndex").Select
End Sub

 

[FSt1]

hi
you said that you have a sql query(meaning one?). if that is true then you
really don't need the refreshall command although it would work.
i would just use a refresh command on the query range like....

sheets("yoursheet").range("A1").queyrtable.refresh backgroundquery = false

setting the background property to false forces the query to refresh before
any other code can excute. depending on the size of the query, there could be
a small pause as the refresh occures but usually it's not noticable.
I have found that allowing a query to refresh in the background while other
code is excuting can cause problems if the code is using the data from the
refresh. the code may use unrefreshed data depending. so as a rule, if i am
going to use the refresh data in my macro, i don't allow a background
refresh....just in case.

regards
FSt1

 

[JeffR]

Actually once I unchecked the "Enable Background Refresh" then my existing
code worked as expected and the calculations and queries refreshed as
changes were made.

Thanks