Range probelm in VB6

[K.K.]

Hi all,

I have a VB6 app which will validate the records in an excel file (source) ,
if the record failed the rule then it will be copy to a "Pending" excel
file.

The app has a list box which will show a list of xls file in a directory,
then it process it one by one. Then open both files and assign last row + 1
into a variable iNewRow from the Pending file, so if a rec. is failed it
will copy the rec and append to the end of the file (hopefully I'm on the
right track)

My problem is, it didn't append to the last row after the first file is
processed, but start writing from the second record in the Pending file, so
previous records are overwritten. Here's my code:

-----------------------------------------------------------
Private Sub Command1_Click()
Dim i As Integer

For i = 0 To LstDSLPending.ListCount - 1 'Loop thru each file
in the list box
Call MatchExcelFileRecords("C:\Source\", LstNACPending.List(i))
Next

End Sub


Sub MatchExcelFileRecords(pFilePath As String, pFileName As String)
Dim sAccountNo As String
Dim XlsApp As Excel.Application
Dim WkBk(1 To 2) As Excel.Workbook '1 = Source File, 2 = Pending
File
Dim WkSh(1 To 2) As Excel.Worksheet
Dim Rng(1 To 2) As Excel.Range
Dim iRow As Integer
Dim iNewRow As Integer

Screen.MousePointer = vbHourglass
Set XlsApp = New Excel.Application
XlsApp.Visible = True
Set WkBk(1) = XlsApp.Workbooks.Open(pFilePath & pFileName)
Set WkBk(2) = XlsApp.Workbooks.Open("C:\UnMatchedRecords\Pending.xls")
Set WkSh(1) = WkBk(1).Worksheets(1)
Set WkSh(2) = WkBk(2).Worksheets(1)
Set Rng(1) = WkSh(1).UsedRange
Set Rng(2) = WkSh(2).UsedRange
iNewRow = (Rng(2).Rows.Count) + 1

For iRow = 1 To Rng(1).Rows.Count
WkSh(1).Cells(iRow, "AD") = " " 'Initialise the
column
If WkSh(1).Cells(iRow, "A") = "CHK" Then 'This will skip the
header
sAccountNo = WkSh(1).Cells(iRow, "R") & "" 'Phone number in Col
R
sAccountNo = funGetACNoByProspID(sAccountNo) 'Do checking here

If sAccountNo = "" Then
WkSh(1).Rows(iRow).Copy WkSh(2).Rows(iNewRow) '*** here where I copy
a failed record to the Pending file
iNewRow = iNewRow + 1
WkSh(1).Cells(iRow, "AD") = "U"
WkSh(1).Cells(iRow, "AE") = pFileName
frmData.mbNacReturnedOK = False
End If

End If
Next

Set WkSh(1) = Nothing
Set WkSh(2) = Nothing
WkBk(1).Close True
WkBk(2).Close True
Erase Rng
Erase WkSh
Erase WkBk
XlsApp.Quit
Screen.MousePointer = vbDefault

End Sub

--------------------------------------------


Thanks for your help~~

K.K.

 

[mudraker]

K

The problem is in your code

iNewRow = (Rng(2).Rows.Count) + 1

rows.count = 65536 (lastrow) + 1 = 1(firstrow)



I hate computor qliches that send messages early.

see my followup messag

 

[mudraker]

K

The problem is in your code

iNewRow = (Rng(2).Rows.Count) + 1

rows.count = 65536 (lastrow) + 1 = 1(firstrow)


you need to use


iNewRow = Range("a" & Rows.Count).End(xlUp).Row + 1


or


iNewRow = TheSheet.Cells.Find(What:="*", _ SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Ro

 

[K.K.]

Hi Mudraker

Thank you for your reply, I've try

(1)
iNewRow = Range("a" & Rows.Count).End(xlUp).Row + 1

it doesn't work because it gives a type mismatch error that return a cell's
value......

(2)
iNewRow = TheSheet.Cells.Find(What:="*", _ SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

iNewRow = iNewRow + 1

It cause an error(if the worksheet has nothing in it) but it's not a big
deal coz I can create a dummy row before hand.

Or is there any better way you (or other nice helper) can suggest me?

Thanks~~

K.K.

 

[Harald Staff]

Hi KK

"Range" is not a VB6 object, it's an excel object, so it needs proper
addressing. This is untested (a little too much to set up for me right now)
but see of one of these work:

XlsApp.Range("a" & Rows.Count).End(xlUp).Row + 1
WkBk(1).Range("a" & Rows.Count).End(xlUp).Row + 1
XlsApp.WkBk(1).Range("a" & Rows.Count).End(xlUp).Row + 1

HTH. Best wishes Harald