How to copy entire row fm an xls file to another xls file?

K

K.K.

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.
 
K

keepITcool

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.K.

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
 
K

keepITcool

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\UnMatchedRecords
\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 >
 
J

Jamie Collins

keepITcool said:
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)
Click to expand...

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.

--
 
K

keepITcool

Jamie,

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.


I was referring to this BUG:

http://support.microsoft.com/default.aspx?scid=kb;en-us;319998
&Product=xlw

When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from
an Excel worksheet that is open in Excel, a memory leak occurs in the
Excel process. Repeated queries may eventually cause Excel to run out of
memory and raise an error, or cause Excel to stop responding.
RESOLUTION
The memory used by the ADO queries cannot be reclaimed by closing and
releasing the ADO objects. The only way to release the memory is to quit
Excel.

ask any EXCEL programmer


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
J

Jamie Collins

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.
Click to expand...

You must have missed the discussion about this yesterday:

http://groups.google.com/[email protected]

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.aspx?scid=kb;en-us;319998
Click to expand...

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.

--
 
K

K.K.

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
 
Top