Cells not refreshing...

[Jeff]

I am using EXCEL 2003 and I've got a workbook that has been working. Part of
the functionality is that it goes out to a SQL database and imports a subset
of data onto a sheet in the workbook. There is a summary sheet that I'm
using to organize the data subset.

I'm using a couple of Domain Aggregate functions (DCount and DCountA) to sum
the total rows of data and the total non-blanks rows of data. This is all
working great.

What I want to do now is - on a separate worksheet - capture the summaries
by day so that I can display a trend chart on a rolling 30 day window.

I've created 7 variables in my subroutine (one for date and 6 for the
individual data values I want to trend) as Var1, Var2, Var3, etc and I'm
setting these values equal to the cells in the summary sheet that it's
normally found.

My thoughts were that once I've set each of the variable to it's respective
cell on the Summary sheet, I could just right those values onto the sheet
that will store the data for the trend chart.

What I'm find is that when I run the macro it puts the date in the correct
spot and all other data fields are equal to zero. If you check the summary
page there are non-zero values there.

I accidentally discovered that if I put a break in the code, the Vars
eventually get populated with the right values and subsequently the storage
sheet gets the right values. It's almost like the subroutine gets done
running before the cells get updated on the summary sheet.

And FYI I'm doing this with 2 different sub-routines. The first routine
populates the fields on the summary page and the last line of that sub calls
the other sub which is supposed to populate the sheet for the trend chart.

I'm missing something simple I suspect.

Thanks

Jeff

 

[B Lynn B]

post yr code plz. pretty hard to tell what's going on otherwise.

 

[Jeff]

As you requested....

The first sub-routine gets the data from the SQL database and calls the
second sub-routine (works perfectly)

Sub CreatePlaterMetricsDataLink()

On Err GoTo Bail

Worksheets("PlaterMetricsData").Range("A1").Select
With ActiveSheet.QueryTables.Add("ODBC;Driver=SQL
Server;Server=PPI-AD82E255A57\SQLEXPRESS;UID=plater;PWD=1234;Database=Process_Manager_Hoist_z", Range("A1")) ', "Select * from parts;")
.CommandText = "EXEC sp_date_range_to_oee_values '" &
Worksheets("Summary").Range("Q1") & "', '" &
Worksheets("Summary").Range("C1") & "'"
.Name = "Query from Plater_SQL"
.FieldNames = True
.PreserveFormatting = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh 'BackgroundQuery:=False
End With

Populate_CurrentDataTable

Worksheets("Summary").Select

Exit Sub

Bail:
MsgBox Err.Number & " " & Err.Description

End Sub

The second sub routine (called at the end of the first)

Sub Populate_CurrentDataTable()
Dim dteDate As Date
Dim iShift3Bars As Integer, iShift1Bars As Integer, iShiftBars As Integer
Dim iShift3Cycles As Integer, iShift1Cycles As Integer, iShift2Cycles As
Integer

dteDate = Worksheets("Summary").Range("C1").Value
iShift3Bars = Worksheets("Summary").Range("M5").Value
iShift1Bars = Worksheets("Summary").Range("N5").Value
ishift2bars = Worksheets("Summary").Range("O5").Value
iShift3Cycles = Worksheets("Summary").Range("M6").Value
iShift1Cycles = Worksheets("Summary").Range("N6").Value
iShift2Cycles = Worksheets("Summary").Range("O6").Value

If Worksheets("Summary").Range("F1").Value = 1 Then
Worksheets("CycleInfoSQL").Select
ActiveSheet.Range("A3").Select
Do While IsEmpty(ActiveCell) = False
'Looks for an empty cell
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = dteDate
ActiveCell.Offset(0, 1).Value = iShift3Bars
ActiveCell.Offset(0, 2).Value = iShift1Bars
ActiveCell.Offset(0, 3).Value = ishift2bars
ActiveCell.Offset(0, 4).Value = iShift3Cycles
ActiveCell.Offset(0, 5).Value = iShift1Cycles
ActiveCell.Offset(0, 6).Value = iShift2Cycles

End If

 

[Jeff]

Ok...I've broken the 2 sub-routines and assigned the click event on 2
different buttons and everything works.

I'm thinking the cells aren't getting refreshed when I do it programically
and that's why it returns zeros. Is there some way to force the cells to
update?