Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't refresh
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't refresh
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 "JeffR" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't refresh
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't refresh
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 "Joel" wrote in message ... 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 "JeffR" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I refresh a macro? | Excel Discussion (Misc queries) | |||
refresh macro | Excel Discussion (Misc queries) | |||
refresh macro | Excel Worksheet Functions | |||
Refresh macro | Excel Discussion (Misc queries) | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming |