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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range probelm in VB6
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range probelm in VB6
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range probelm in VB6
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. :) "mudraker " wrote in message ... 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).Row --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range probelm in VB6
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 "K.K." skrev i melding ... 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...... |
Reply |
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 |