Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range probelm in VB6
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Probelm with macro | Excel Worksheet Functions | |||
Hyperlink probelm | Excel Worksheet Functions | |||
Hyperlink probelm | Excel Discussion (Misc queries) | |||
textbox counter probelm | Excel Programming | |||
Probelm with Data formatting | Excel Programming |