Get the last row in the field in my ExcelSheet?

[Pirre001]

How do I do to get the value in the last row in the field, in my excelsheet, into a string. The problem is that the last row is a SUM function. "=SUM(A1:A40)".
Can anyone help me with that?

[Kraig K]

I'm assuming that you already know how to access data in Excel...
So, just do this:

SomeVariable = objExcel.Cells(Row, Column)

I've also assumed that you know what cell you want to read. If you don't, I can post some code to find the last used cell, let me know.

[SeaHag]

For dynamic
lists

Private Sub CommandButton1_Click() ' put a bunch of stuff in col a
 
Dim myRange As Range
 
Range("a1").Select
Set myRange = ActiveCell.CurrentRegion
MsgBox myRange.Count
 
Set myRange = Nothing
End Sub

Seahag

[SeaHag]

MsgBox myRange(myRange.Count)

That is all

[Pirre001]

Thanks again, SeaHag! And thanks Kraig K!

[Pirre001]

Help...

[SeaHag]

I dont know what u want..
What values where.
It looks like your on the right track though.




(this forum is sloooooooowwwww)

[Pirre001]

In field "C" in worksheet "Total" in excelfile "C:\test.xls" is a numeric field with many rows, i dont know how many it is, it various from time to time. How can I Sum this filed an put the sumvalue into a string or in VB? ...

[Kraig K]

Yep, I gotcha. I was working on getting some code for you but I had to reboot. Anywho, this is what I used to count the number of cells and enter a formula.

objExcel.Sheets("Sheet1").Select
   Rows = objExcel.ActiveSheet.UsedRange.Rows.Count
Print Rows

objExcel.ActiveCell.Formula = "=SUM(C1:C10)"

his just gives you something to start with, I haven't tested it. Give me a few minutes and I can post some working code...

[Pirre001]

Ok. thanks...

[Kraig K]

Here you go:

Option Explicit
Dim objExcel As New Excel.Application
Dim objBook As New Excel.Workbook
Dim Rows As Integer
Dim Formula As Variant

Private Sub Form_Load()

   On Error GoTo ErrHandler
   
   Set objBook = objExcel.Workbooks.Open("c:\temp\test.xls")
   With objExcel
   .Sheets("Sheet1").Select
   Rows = .ActiveSheet.UsedRange.Rows.Count
   .Range("A" & (Rows + 1)).Activate
   Formula = "=SUM(A1" & ":A" & Rows & ")"
   .Cells(Rows + 1, 1) = Formula
   objBook.Save
   .Workbooks.Close
   End With
   Set objExcel = Nothing
   Print "Done"
   
ErrHandler:
Print Err.Description
   objExcel.Workbooks.Close
   Set objExcel = Nothing
   Exit Sub
End Sub

I know this line looks like a mess:

Formula = "=SUM(A1" & ":A" & Rows & ")"

but it's the only way I've found to get variables in an Excel formula. If you put one in a formula, it enters it as text. I just figured out how to do this right now, so thanks for the lesson!

[Pirre001]

Thanks again Kraig K,

But there is a little problem. The value I get with:
Formula = "=SUM(A1" & ":A" & Rows & ")"
I wanna put it into a textbox in my form? How do I do that?

[Kraig K]

NP:

Text1.Text = objExcel.Cells(Rows +1, 1)

[Pirre001]

Kraig K, u are an angel, you have fix all my problems now!

Thanks!