Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
Hi All,
I have an app in VB6 that will open an excel file, validate then process the data, if the validation is failed for a record, I want to copy (append) that record (entire row) from the original data worksheet to an exisiting xls file ? I think I prbably can use ADO connection to get the record but Do you think using an ADO connection would Not be better? Or there is another way to do it? Thanks a lot! K.K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
if it's just excel data you're transferring i'd never use ado but stick to 'pure' excel. (probably faster easier coding less overhead and less chance of memory leaking and strange lockups) dim ws(1 to 2) as excel.worksheet dim rg(1 to 2) as excel.range set ws(1)=workbooks(1).worksheets(1) set ws(2)=workbooks(2).worksheets(5) ws(1).rows(13).copy ws(2).rows(15) 'or ws(1).cells(123,18).entirerow.copy _ destination:=ws(2).cells(2^16,1).end(xlup).offset( 1).entirerow 'if you use .entirerow it doesn't matter in which column you 'start' although 'generalized' that would work for me :) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "K.K." wrote: Hi All, I have an app in VB6 that will open an excel file, validate then process the data, if the validation is failed for a record, I want to copy (append) that record (entire row) from the original data worksheet to an exisiting xls file ? I think I prbably can use ADO connection to get the record but Do you think using an ADO connection would Not be better? Or there is another way to do it? Thanks a lot! K.K. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
Hi All & Cool
Thanks for your reply, I'm now trying your code but I don't have much experience with excel - VB so I got a few probelm(hopefully easy one)... Acutally what I'm want to do is go thru each record and validate them, if the record failed then I want to copy it into a "Pending" excel file. Now my problem become how to go thru each used row in the file? Cool, after I got your code, I implement it but got an "Subscript out of range" error (in "*" below) Dim XlsApp(1 To 2) As Excel.Application Dim WkBk(1 To 2) As Excel.Workbook Dim WkSh(1 To 2) As Excel.Worksheet Dim Range(1 To 2) As Excel.Range Dim iRow As Integer lblReturnedStatus.Caption = "Initialising Excel connection..." lblReturnedStatus.Refresh sErr = "" funCheck = True Set XlsApp(1) = New Excel.Application Set XlsApp(2) = New Excel.Application XlsApp(1).Visible = False XlsApp(2).Visible = False Set WkBk(1) = XlsApp(1).Workbooks.Open(pFilePath & pFileName) Set WkBk(2) = XlsApp(2).Workbooks.Open("P:\Optimum\Data\Returned \UnMatchedRecords\NAC.xls" ) Set WkSh(1) = Workbooks(1).Worksheets(1) Set WkSh(2) = Workbooks(2).Worksheets(2) '* * * error here * * * Set Range(1) = WkSh(1).UsedRange Set Range(2) = WkSh(2).UsedRange WkSh(1).Rows(13).Copy WkSh(2).Rows(15) For iRow = 1 To Range(1).Row MsgBox WkSh(1).Cells(iRow, "C") Next XlsApp(1).Quit XlsApp(2).Quit Set WkSh(1) = Nothing Set WkSh(2) = Nothing "keepITcool" wrote in message ... if it's just excel data you're transferring i'd never use ado but stick to 'pure' excel. (probably faster easier coding less overhead and less chance of memory leaking and strange lockups) dim ws(1 to 2) as excel.worksheet dim rg(1 to 2) as excel.range set ws(1)=workbooks(1).worksheets(1) set ws(2)=workbooks(2).worksheets(5) ws(1).rows(13).copy ws(2).rows(15) 'or ws(1).cells(123,18).entirerow.copy _ destination:=ws(2).cells(2^16,1).end(xlup).offset( 1).entirerow 'if you use .entirerow it doesn't matter in which column you 'start' although 'generalized' that would work for me :) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "K.K." wrote: Hi All, I have an app in VB6 that will open an excel file, validate then process the data, if the validation is failed for a record, I want to copy (append) that record (entire row) from the original data worksheet to an exisiting xls file ? I think I prbably can use ADO connection to get the record but Do you think using an ADO connection would Not be better? Or there is another way to do it? Thanks a lot! K.K. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
Now my problem become how to go thru each used row in the file? Cool, after I got your code, I implement it but got an "Subscript out of range" error (in "*" below) Made a few changes... :) 'changed this as you dont want to work with 2 instances Dim XlsApp As Excel.Application Dim WkBk(1 To 2) As Excel.Workbook Dim WkSh(1 To 2) As Excel.Worksheet 'NOT A GOOD IDEA to NAME a VARIABLE AS A KEYWORD 'Dim Range(1 to 2) as range Dim rng(1 To 2) As Excel.Range Dim iRow As Integer lblReturnedStatus.Caption = "Initialising Excel connection..." lblReturnedStatus.Refresh sErr = "" funCheck = True Set XlsApp = New Excel.Application = do this when it's functioning 'XlsApp.Visible = False Set WkBk(1) = XlsApp.Workbooks.Open(pFilePath & pFileName) Set WkBk(2) = XlsApp.Workbooks.Open("P:\Optimum\Data\Returned\Un MatchedRecords \NAC.xls" ) 'dont use generic workbooks.. you've got the object'' use it! Set WkSh(1) = wkbk(1).Worksheets(1) Set WkSh(2) = wkbk(2).Worksheets(2) Set Rng(1) = WkSh(1).UsedRange Set Rng(2) = WkSh(2).UsedRange WkSh(1).Rows(13).Copy WkSh(2).Rows(15) For iRow = 1 To Rng(1).Rows.count MsgBox WkSh(1).Cells(iRow, "C") Next Set WkSh(1) = Nothing Set WkSh(2) = Nothing wkbk(1).close false wkbk(2).close true erase rng erase wksh erase wkbk xlapp.quit keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "K.K." wrote: Thanks for your reply, I'm now trying your code but I don't have much experience with excel - VB so I got a few probelm(hopefully easy one)... Acutally what I'm want to do is go thru each record and validate them, if the record failed then I want to copy it into a "Pending" excel file. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
"K.K." wrote:
I have an app in VB6 that will open an excel file, validate then process the data, if the validation is failed for a record, I want to copy (append) that record (entire row) from the original data worksheet to an exisiting xls file ? I think I prbably can use ADO connection to get the record but Do you think using an ADO connection would Not be better? Or there is another way to do it? keepITcool wrote ... if it's just excel data you're transferring i'd never use ado but stick to 'pure' excel. (probably faster easier coding less overhead and less chance of memory leaking and strange lockups) If the OP can do 'validation' in the WHERE clause of a SELECT query, then using ADO will be: - faster - easier coding If you are getting memory leaks and strange lockups when using ADO with Excel, then you are doing it wrong :-) And I don't know what is meant by 'less overhead' but automating Excel from another app just to retrieve data is a lot of overhead compared with using ADO (ask any ASP programmer). Jamie. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
Jamie..
i admit i was thinking too much as working from within excel, in hindsight my advice may not be the best. KK.. see www.erlandsendata.no for a good start on Ado and Excel. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool keepITcool wrote: |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
keepITcool wrote ...
I'm an ADO fan, dont get me wrong. I'm a decent coder too, and I know that I've got to close connections and objects. You must have missed the discussion about this yesterday: http://groups.google.com/groups?thre...GP09.p hx.gbl arno finally agreed with me that closing ADO's connections and objects has no effect. I was referring to this BUG: http://support.microsoft.com/default...b;en-us;319998 Yep, I'm familiar with that article. Below I've emphasized the important bit: "When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet <emphasis that is open in Excel </emphasis, a memory leak occurs in the Excel process." And you missed the important bit from the RESOLUTION: "query the Excel worksheet only while the file is not open in Excel." Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy entire row fm an xls file to another xls file?
Hi Cool,
Thank you very much for your great help! I got my function running now~! Also thanks Jamie to participated in this thread~ K.K. :D "Jamie Collins" wrote in message om... keepITcool wrote ... I'm an ADO fan, dont get me wrong. I'm a decent coder too, and I know that I've got to close connections and objects. You must have missed the discussion about this yesterday: http://groups.google.com/groups?thre...GP09.p hx.gbl arno finally agreed with me that closing ADO's connections and objects has no effect. I was referring to this BUG: http://support.microsoft.com/default...b;en-us;319998 Yep, I'm familiar with that article. Below I've emphasized the important bit: "When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet <emphasis that is open in Excel </emphasis, a memory leak occurs in the Excel process." And you missed the important bit from the RESOLUTION: "query the Excel worksheet only while the file is not open in Excel." Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data for match word from one file to another file | Excel Worksheet Functions | |||
How do I copy a excel file into a InDesign file correctly? | Excel Discussion (Misc queries) | |||
copy the same raws of all sheets from about a 100 file to a new sheet of a book and save the file | Setting up and Configuration of Excel | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
Copy entire file using VBA | Excel Programming |