end of row

[kozzen]

I want to load a catalogus (excell file) to a database, because iets over de 30000 rows. i thought to put an progressbar so i can see how far the process is.

but how can i see how much rows there are, so i can put the max of the progressbar the same as the rows.

something like EOF ???

[westconn1]

as you do not show what method you are using to do this, it will either be the recordcount of an excel sheet opened as a recordset, or

obj.cells(obj.rows.count, 1).end(xlup).row
'where obj is a worksheet object

[kozzen]

as you do not show what method you are using to do this, it will either be the recordcount of an excel sheet opened as a recordset, or

obj.cells(obj.rows.count, 1).end(xlup).row
'where obj is a worksheet object

it doesn't work

my code that i found :



Dim i As Integer
Dim var(10) As String

Dim NieuweArt, GewijzigdeArt As Integer

NieuweArt = 0
GewijzigdeArt = 0


On Error GoTo Err
'Inisialitation Excel object
StartExcel

Set ExcelWBk = Excel.Workbooks.Open(CommonDialog1.FileName)

'Displaying its status on the form
Print "Successfully open file ..."
'Access the first Worksheet (1)
'If you want to switch to the second Worksheet, then
'simply replace (1) with (2), and so forth...
Set ExcelWS = ExcelWBk.Worksheets(1)
'Displaying its status on the form
Print "Successfully read Worksheet Sheet1 ..."
'Processing the ExcelWS variable
With ExcelWS

i = 1
var(1) = "1"

Do Until var(1) = ""
ProgressBar1.Value = i
'Assign to a variabel
var(1) = .Cells(i, 1)
var(2) = .Cells(i, 2)
var(3) = .Cells(i, 3)
var(4) = .Cells(i, 4)
var(5) = .Cells(i, 5)
var(6) = .Cells(i, 6)
var(7) = .Cells(i, 7)
var(8) = .Cells(i, 8)
var(9) = .Cells(i, 9)
var(10) = .Cells(i, 10)

If var(1) <> "" Then

j = 1
Do Until j = 11
If Left(var(j), 1) = "'" Then
var(j) = Right(var(j), Len(var(j)) - 1)
End If
j = j + 1
Loop

codenrspatie = InStr(var(2), Chr(32))
If codenrspatie <> 0 Then
codenr = Left(var(txtGegevens(1)), codenrspatie - 1)
Else
codenr = var(txtGegevens(1))
End If


DatArtikel.RecordSource = "select * from tblartikelen where codenr like'" & codenr & "*'"
DatArtikel.Refresh

If DatArtikel.Recordset.RecordCount = 0 Then
'artikel niet aanwezig
NieuweArt = NieuweArt + 1
DatArtikel.Recordset.AddNew
DatArtikel.Recordset.Fields(0) = var(txtGegevens(0))
DatArtikel.Recordset.Fields(1) = var(txtGegevens(1))
DatArtikel.Recordset.Fields(2) = var(txtGegevens(2))

DatArtikel.Recordset.Fields(3) = var(txtGegevens(3))
DatArtikel.Recordset.Fields(4) = var(txtGegevens(4))
DatArtikel.Recordset.Fields(5) = var(txtGegevens(5))
DatArtikel.Recordset.Fields(12) = DBCombLev
DatArtikel.Recordset.Update
DatArtikel.Refresh

ElseIf DatArtikel.Recordset.RecordCount = 1 Then
'artikel aanwezig
DatArtikel.Recordset.Edit

If DatArtikel.Recordset.Fields(3) <> var(5) Then
DatArtikel.Recordset.Fields(3) = var(5)
GewijzigdeArt = GewijzigdeArt + 1
End If

If DatArtikel.Recordset.Fields(4) <> var(7) Then
DatArtikel.Recordset.Fields(4) = var(7)
GewijzigdeArt = GewijzigdeArt + 1
End If

DatArtikel.Recordset.Fields(5) = var(9)
DatArtikel.Recordset.Fields(12) = DBCombLev

DatArtikel.Recordset.Update
DatArtikel.Refresh
End If
End If
i = i + 1
Loop
End With

Print "Aantal nieuwe artikelen : " & NieuweArt
Print "Aantal gewijzigde artikelen : " & GewijzigdeArt
Print "Successfully close worksheet and Excel file ..."

'After finished, don't forget to close the worksheet
CloseWorkSheet
'Displaying its status on the form
Print "Successfully close worksheet and Excel file ..."
'Don' forget neither, always clean-up the memory that
'has just been used by Excel object
ClearExcelMemory
'Displaying its status on the form
Print "Successfully clean-up the memory used by Excel ..."
'Displaying the message
Exit Sub
Err:
'CloseWorkSheet
'When error occured, don't forget to clean-up the memory
ClearExcelMemory
MsgBox Err.Description, vbCritical, "Error Occured"
End Sub

Private Sub StartExcel()
On Error GoTo Err:
'First of all, get the Excel object, and if error occured
'jumpt to the Err label on the bottom of this Sub,
'then create the Excel object. Typically, error occured here
'if the Excel object has not been created, yet
Set Excel = GetObject(, "Excel.Application")
Exit Sub
Err:
'Create an Excel object if it does not exist.
Set Excel = CreateObject("Excel.Application")
End Sub

Private Sub CloseWorkSheet()
On Error Resume Next
'Close the Excel workbook
ExcelWBk.Close
'Quit from Excel application
Excel.Quit
End Sub

Private Sub ClearExcelMemory()
'Clean-up the memory, check first, whether
'the Excel object exists or not in the memory ...
If Not ExcelWS Is Nothing Then Set ExcelWS = Nothing
If Not ExcelWBk Is Nothing Then Set ExcelWBk = Nothing
If Not Excel Is Nothing Then Set Excel = Nothing
End Sub

[westconn1]

it doesn't work

what does this mean? you get error? wrong result ? other?

[kozzen]

what does this mean? you get error? wrong result ? other?

error 438
object doesnt support this property of method

that's the code

ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row

[westconn1]

ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row

what do you do with the return from this? the code is correct and definitely works to return the last row

rowcount = ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row ' or
msgbox ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row

is your object already set and declared correctly?

[kozzen]

what do you do with the return from this? the code is correct and definitely works to return the last row

rowcount = ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row ' or
msgbox ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row

is your object already set and declared correctly?

it's ok (my fault), no error any more:

progressbar.max =ExcelWS.Cells(ExcelWS.Rows.Count, 1).End(xlUp).Row