Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells not refreshing...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells not refreshing...
post yr code plz. pretty hard to tell what's going on otherwise.
"Jeff" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells not refreshing...
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;Databas e=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 "B Lynn B" wrote: post yr code plz. pretty hard to tell what's going on otherwise. "Jeff" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cells not refreshing...
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? "B Lynn B" wrote: post yr code plz. pretty hard to tell what's going on otherwise. "Jeff" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells not refreshing | Excel Worksheet Functions | |||
refreshing cells? | Excel Discussion (Misc queries) | |||
refreshing formulas in cells | Excel Discussion (Misc queries) | |||
Why are the cells of my workbook not refreshing? | Excel Worksheet Functions | |||
refreshing formulas in cells | Excel Discussion (Misc queries) |