• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • 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 BottomleySoftware Quality Lead EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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