Calling Subroutines from sub-routine
 

I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned to
the CLICK event of their own button.

The first subroutine performs a pass-thru extraction from a SQL database.
There is a SUMMARY sheet in the workbook that then does some calculations on
the data that is extracted using the DCount and DCountA functions into
specific cells on the SUMMARY sheet.

The second sub-routine copies the resultant values calculated using the
DCount and DCountA functions onto another workbook that I'm going to use to
generate a trend chart.

If I click the first button and then the second button, every thing works as
I'd expect. What I want to do is call the second sub-routine as the last
line in the first sub-routine. When I do this the first sub-routine works
the same but the second sub-routine puts zeroes instead of the values from
the SUMMARY sheet.

Help...

Calling Subroutines from sub-routine
 

Post the before and after code, what works run independently, and how you
merged them.

--
__________________________________
HTH

Bob

"Jeff" wrote in message
...
I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned
to
the CLICK event of their own button.

The first subroutine performs a pass-thru extraction from a SQL database.
There is a SUMMARY sheet in the workbook that then does some calculations
on
the data that is extracted using the DCount and DCountA functions into
specific cells on the SUMMARY sheet.

The second sub-routine copies the resultant values calculated using the
DCount and DCountA functions onto another workbook that I'm going to use
to
generate a trend chart.

If I click the first button and then the second button, every thing works
as
I'd expect. What I want to do is call the second sub-routine as the last
line in the first sub-routine. When I do this the first sub-routine works
the same but the second sub-routine puts zeroes instead of the values from
the SUMMARY sheet.

Help...

Calling Subroutines from sub-routine
 

It works when I run them independently but not this way.

Sub Get_Data()

Worksheets("PlaterMetricsData").Range("A1").Select
With ActiveSheet.QueryTables.Add(insert args here)
.CommandText = "EXEC ...."
.Name = "PlaterData"
.Refresh
End With

Populate_CurrentDataTable

Worksheets("Summary").Select

End Sub

Sub Populate_CurrentDataTable()
Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer

dteDate = Worksheets("Summary").Range("C1").Value
Worksheets("Summary").Select
iShift3Bars = Worksheets("Summary").Range("M5").Value
iShift3Cycles = Worksheets("Summary").Range("M6").Value

If Worksheets("Summary").Range("F1").Value = 1 Then
Worksheets("CycleInfoSQL").Select
ActiveSheet.Range("A3").Select
Do While IsEmpty(ActiveCell) = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = dteDate
ActiveCell.Offset(0, 1).Value = iShift3Bars
ActiveCell.Offset(0, 2).Value = iShift3Cycles

End If

Worksheets("Summary").Select

End Sub

"Bob Phillips" wrote:

Post the before and after code, what works run independently, and how you
merged them.

--
__________________________________
HTH

Bob

"Jeff" wrote in message
...
I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned
to
the CLICK event of their own button.

The first subroutine performs a pass-thru extraction from a SQL database.
There is a SUMMARY sheet in the workbook that then does some calculations
on
the data that is extracted using the DCount and DCountA functions into
specific cells on the SUMMARY sheet.

The second sub-routine copies the resultant values calculated using the
DCount and DCountA functions onto another workbook that I'm going to use
to
generate a trend chart.

If I click the first button and then the second button, every thing works
as
I'd expect. What I want to do is call the second sub-routine as the last
line in the first sub-routine. When I do this the first sub-routine works
the same but the second sub-routine puts zeroes instead of the values from
the SUMMARY sheet.

Help...




.

Calling Subroutines from sub-routine
 

step into Get_Data
set the next step at the line Populate_CurrentDataTable then step (F8) ...is
there an error message?
Are these two procedures in a standard module? If they're on a sheet code
page, are they on the same page?


"Jeff" wrote:

It works when I run them independently but not this way.

Sub Get_Data()

Worksheets("PlaterMetricsData").Range("A1").Select
With ActiveSheet.QueryTables.Add(insert args here)
.CommandText = "EXEC ...."
.Name = "PlaterData"
.Refresh
End With

Populate_CurrentDataTable

Worksheets("Summary").Select

End Sub

Sub Populate_CurrentDataTable()
Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer

dteDate = Worksheets("Summary").Range("C1").Value
Worksheets("Summary").Select
iShift3Bars = Worksheets("Summary").Range("M5").Value
iShift3Cycles = Worksheets("Summary").Range("M6").Value

If Worksheets("Summary").Range("F1").Value = 1 Then
Worksheets("CycleInfoSQL").Select
ActiveSheet.Range("A3").Select
Do While IsEmpty(ActiveCell) = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = dteDate
ActiveCell.Offset(0, 1).Value = iShift3Bars
ActiveCell.Offset(0, 2).Value = iShift3Cycles

End If

Worksheets("Summary").Select

End Sub

"Bob Phillips" wrote:

Post the before and after code, what works run independently, and how you
merged them.

--
__________________________________
HTH

Bob

"Jeff" wrote in message
...
I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned
to
the CLICK event of their own button.

The first subroutine performs a pass-thru extraction from a SQL database.
There is a SUMMARY sheet in the workbook that then does some calculations
on
the data that is extracted using the DCount and DCountA functions into
specific cells on the SUMMARY sheet.

The second sub-routine copies the resultant values calculated using the
DCount and DCountA functions onto another workbook that I'm going to use
to
generate a trend chart.

If I click the first button and then the second button, every thing works
as
I'd expect. What I want to do is call the second sub-routine as the last
line in the first sub-routine. When I do this the first sub-routine works
the same but the second sub-routine puts zeroes instead of the values from
the SUMMARY sheet.

Help...




.

Calling Subroutines from sub-routine
 

When I put a Breakpoint in the code and then run it; subsequently, stepping
(F8) through the code - including following the call statement into the next
routine, everything works fine.

When I remove the breakpoint and let it run un-interrupted...it puts zeroes
in all the fields.

It's almost like the source data isn't getting updated or something and when
I manually cause the code to stop, it works.


"Patrick Molloy" wrote:

step into Get_Data
set the next step at the line Populate_CurrentDataTable then step (F8) ...is
there an error message?
Are these two procedures in a standard module? If they're on a sheet code
page, are they on the same page?


"Jeff" wrote:

It works when I run them independently but not this way.

Sub Get_Data()

Worksheets("PlaterMetricsData").Range("A1").Select
With ActiveSheet.QueryTables.Add(insert args here)
.CommandText = "EXEC ...."
.Name = "PlaterData"
.Refresh
End With

Populate_CurrentDataTable

Worksheets("Summary").Select

End Sub

Sub Populate_CurrentDataTable()
Dim dteDate As Date, iShift3Bars As Integer, iShift3Cycles As Integer

dteDate = Worksheets("Summary").Range("C1").Value
Worksheets("Summary").Select
iShift3Bars = Worksheets("Summary").Range("M5").Value
iShift3Cycles = Worksheets("Summary").Range("M6").Value

If Worksheets("Summary").Range("F1").Value = 1 Then
Worksheets("CycleInfoSQL").Select
ActiveSheet.Range("A3").Select
Do While IsEmpty(ActiveCell) = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = dteDate
ActiveCell.Offset(0, 1).Value = iShift3Bars
ActiveCell.Offset(0, 2).Value = iShift3Cycles

End If

Worksheets("Summary").Select

End Sub

"Bob Phillips" wrote:

Post the before and after code, what works run independently, and how you
merged them.

--
__________________________________
HTH

Bob

"Jeff" wrote in message
...
I have 2 subroutines in my EXCEL 2003 workbook and they are each assigned
to
the CLICK event of their own button.

The first subroutine performs a pass-thru extraction from a SQL database.
There is a SUMMARY sheet in the workbook that then does some calculations
on
the data that is extracted using the DCount and DCountA functions into
specific cells on the SUMMARY sheet.

The second sub-routine copies the resultant values calculated using the
DCount and DCountA functions onto another workbook that I'm going to use
to
generate a trend chart.

If I click the first button and then the second button, every thing works
as
I'd expect. What I want to do is call the second sub-routine as the last
line in the first sub-routine. When I do this the first sub-routine works
the same but the second sub-routine puts zeroes instead of the values from
the SUMMARY sheet.

Help...




.