• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

Keep adding to same Excel Worksheet

I have a cmd that outputs my data to Excel.

I want to know how I can keep adding to the same sheet, for example, when the user clicks output once, it opens a new sheet and adds the first row, similarly, every time the user clicks output, I just want to add it the result to the next row.

I also graph a bar chart on another worksheet in the same work book, and I want the chart to add the new values as well.

My code is as follows:
Private Sub cmd_OutputKeelingPlot_Click()
 
'Call Corell
 
   Dim objApp As Excel.Application
   Dim objBook As Workbook
   Dim objSheet As Worksheet
   Dim objExcelCI As Excel.Chart
   Dim high As Integer
   
   On Error Resume Next
   Set objApp = GetObject("excel.application")
   If Err.Number <> 0 Then
      Set objApp = CreateObject("excel.application")
   End If
   Set objBook = objApp.Workbooks.Add
   Set objSheet = objApp.Sheets(1)
   
   objApp.Visible = True
   objApp.UserControl = True
   objApp.WindowState = xlMaximized
   objApp.DisplayAlerts = False
   
   With objSheet
   .Cells(2, 2).value = " Starting Date "
   .Cells(2, 3).value = " Ending Date "
   .Cells(2, 4).value = " Plot Date "
   .Cells(2, 5).value = " n "
   .Cells(2, 6).value = " R-squared "
   .Cells(2, 7).value = " Standard Error "
   .Cells(2, 8).value = " Slope "
   .Cells(2, 9).value = " Standard Error "
   .Cells(2, 10).value = " Y-intercept "
   .Cells(2, 11).value = " Standard Error "
   
   high = 3 ' This part needs to be fixed, as high should be the next row it adds to, I increment it in the end, but it becomes 3 here again.
   For i = high To high
   .Cells(i, 2).value = grd_result.TextMatrix(therow, 1)
   .Cells(i, 3).value = grd_result.TextMatrix(therowsel, 1)
   .Cells(i, 4).value = (grd_result.TextMatrix(therow, 1) + grd_result.TextMatrix(therowsel, 1)) / 2
   .Cells(i, 5).value = therowsel - therow
   .Cells(i, 6).value = RSQ
   .Cells(i, 7).value = RSQ
   .Cells(i, 8).value = Slope
   .Cells(i, 9).value = Slope
   .Cells(i, 10).value = Yintcpt
   .Cells(i, 11).value = Yintcpt
   ''' High = end row from Autocorrellation
   Next i
   
   .Range("B2:K" & high).Select
   
   With Selection
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlBottom
   .WrapText = False
   .Orientation = 0
   .AddIndent = False
   .IndentLevel = 0
   .ShrinkToFit = False
   .ReadingOrder = xlContext
   .MergeCells = False
   .Borders(xlDiagonalDown).LineStyle = xlNone
   .Borders(xlDiagonalUp).LineStyle = xlNone
   
   With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    End With
    
    .Range("B2:K2").Select
    
    .Columns.AutoFit
    
    With Selection
    .Font.Bold = True
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
   End With
   End With
 
   Set objExcelCI = objBook.Charts.Add
   With objExcelCI
    
    .Name = "Keeling Plot"
    .SetSourceData objSheet.Range("$J$3:$J" & high & ", $C$3:$C" & high), PlotBy = xlrow
    .HasTitle = True
    .ChartTitle.Characters.Text = "Keeling Plot"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Intercept"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Plot Date"
    .HasLegend = False
    
    With .PlotArea.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    
    .PlotArea.Fill.OneColorGradient Style:=msoGradientDiagonalUp, Variant:=3, _
        Degree:=0.231372549019608
        
    With .PlotArea
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 43
    End With
   
    With .SeriesCollection(1).Border
        .ColorIndex = 1
        .Weight = xlHairline
        .LineStyle = xlDot
    End With
    
    With .SeriesCollection(1)
        .MarkerBackgroundColorIndex = 2
        .MarkerForegroundColorIndex = 1
        .MarkerStyle = xlCircle
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    
    End With
       
       
   Set objSheet = Nothing
   Set objBook = Nothing
   Set objApp = Nothing
   Set objExcelCI = Nothing
   high = high + 1
   
End Sub

Open in new window

0
Student_101
Asked:
Student_101
2 Solutions
 
Chris BottomleyCommented:
To try and help,

You seem to need a loop that runs from 3 to x where:
x =  end row from Autocorrellation

Can you explain what you mean by Autocorrellation?

Chris
0
 
Student_101Author Commented:
Hi Chris,

I worked on this for a little while, and I just created a Boolean variable, set it to true when the command was clicked once, and then just added to the same worksheet if it was already created.

Thanks for your help :)

P.S. the variable high was what kept track of which row in excel I last added to,so we can disregard the autocorrelation statement.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now