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
"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
|
Similar Threads
