Newbie question

[steel]

I have an Excel spreadsheet with 1 column and about 50-60 rows. Could someone post the code to tell how many rows there are? I can't figure it out
Thanks

[SteveCRM]

In Excell, isn't there numbers next to the columns?

[steel]

Yes there is, but, The excel sheet is never opened. There is about 30 spreadsheets and I have a flexgrid that displays the info on them. I have to know how many rows however. Anyone with some simple code?

[Zaf Khan]

Hi,

Use this macro, and whenever you wnat to know how many rows, click on TOOLS > MACRO > RUN > GETCOLSNUMBER
and a messagebox will pop up etc


Sub GetColsNumber()

Dim i As Integer
Dim test As Boolean

test = False
On Error Resume Next
Do
i = i + 1
If Trim(Sheet1.Cells(i, 1)) = "" Then
MsgBox i - 1 & " Rows", 64, "information."
test = True
Exit Sub
End If
If Err <> 0 Then test = True
Debug.Print i
Loop Until test
On Error Resume Next

End Sub


DocZaf
{;->

[Zaf Khan]

Sub GetColsNumber()

Dim i As Integer
Dim test As Boolean

test = False
On Error Resume Next
Do
i = i + 1
If Trim(Sheet1.Cells(i, 1)) = "" Then
MsgBox i - 1 & " Rows", 64, "information."
test = True
Exit Sub
End If
If Err <> 0 Then test = True
Debug.Print i
Loop Until test
On Error Resume Next

End Sub

Use this macro
and click on TOOLS > MACRO > RUN > GETCOLUMNSNUMBER

A message box popups etc....

DocZaf
{;->

[steel]

Thanks for the help, but can I run this within my program. The program opens different excel files and needs to know how many rows before it displays them

[Zaf Khan]

Sure you can create it a macro and paste the sub i complete as it appears there, If in doubt consult the help on how to create a macro

Zaf

[Nitro]

Steel!

I thought I helped you on this subject yesterday already!

Anyway, you need to use GetObject and connect to the file and grab the row number.

Use something like this.

  'Reference "Microsoft Excel 9.0 Library"
  Dim xlsWorkBook As Excel.Workbook
  Dim xlsWorkSheet As Excel.Worksheet
  
  Set xlsWorkBook = GetObject("C:\City Charts.xls")
  Set xlsWorkSheet = xlsWorkBook.Worksheets("C")
  Text1.Text = xlsWorkSheet.Cells(1, 1).Value
  xlsWorkBook.Close

Program it exactly like the way how you would program in Excel, except put "xlsworksheet." Use this code combine with what I posted for you yesterday and you should have it.

Use the property "Userrange" with "EntireColumn" or "Column".

If you have furthur problems, I need more detail.

[Edited by Nitro on 05-02-2000 at 04:12 PM]

[steel]

Thanks for all the help everyone!

You did help Nitro I just was having trouble looking it up.
I do appreciate your eagerness to help me. Have you been doing VB for awhile now?

[Nitro]

Hello Steel!

Glad that I could help! I started off with Access and Excel VBA. I have been doing Excel VBA for a long time and just started doing 2-tiers design with VB, about 3 years ago.

API is new to me and have only started using it for a year now. There are a couple of guys on this site that are really good with them.

If I can be of any other assistant in Excel VBA, feel free to post again.

[Edited by Nitro on 04-26-2000 at 11:26 AM]

[steel]

Thanks Nitro