Copy Column Data from one Xls file into Another

[Autosys]

Hi there

I was hoping someone could help me as I'm struggling a bit pls ..

I'm writing a little application that needs to copy certain columns' data from one xls file into another.

I have the below so far:

Say I have sheet1.xls and sheet2.xls (assume each file has only one worksheet).

In sheet1 I would like to copy/select all the data in column D from the 3rd row down until there is no more data and paste this data into sheet2's column A from the 3rd row down (as the 1st 3 rows are headers that should remain as they are.

I'm trying to do the following but it doesn't quite seem to work:

dim xlSourceApp as Excel.Application
dim xlResultApp as Excel.Application
dim xlSourceBook as Excel.Workbook
dim xlResultBook as Excel.Workbook
dim xlSourceSheet as Excel.Worksheet
dim xlSourceSheet as Excel.Worksheet
dim xlResultSheet as Excel.Worksheet

set xlSourceApp = new Excel.Application
set xlResultApp = new Excel.Application

set xlSourceBook = xlSourceApp.Workbooks.Open("c:\sheet1.xls")

set xlResultBook = xlResultApp.Workbooks.Open("c:\sheet2.xls")

set xlSourceSheet = xlSourceBook.sheets(1)
set xlResultSheet = xlResultBook.sheets(1)


xlSourceSheet.columns(3,4).copy
xlResultSheet.columns(3,1).paste

so ... I realise that my problem "probably" lies in the last 2 lines ... any advice would be great cheers!

A

 

[RoyVidar]

I'd use only one application object - you can manipulate multiple workbooks in the same application.

Using copy/paste utilizes the clipboard, doing a simple assigning, like below hardcoded, is probably faster.

[tt]xlResultSheet.Range("A3:A200").Value = xlSourceSheet.Range("D3200").Value[/tt]

Here's a version with a bit more dynamic approach:
[tt]
dim myarr as variant
dim lngRow as long

with xlSourceSheet.Range("d3", xlSourceSheet.Range("d3").End(xlDown))
myarr = .value
.lngRow = .rows.count
end with
with xlResultSheet.Range(xlResultSheet.Cells(3,1), xlResultSheet.Cells(lngRow + 2, 1))
.ClearContents
.value = myarr
end with[/tt]

Roy-Vidar

 

[Autosys]

Thanks RoyVidar

the information was helpful, and encouraged be to actually do it in a loop.

The loop works fine but I was wondering if someone could perhaps tell me how to loop until the last cell containing data in excel.

At the moment I put "END" in the last cell as an identified for my loop to know when to stop. I've got the below.

Do Until oXLSourceSheet.Cells(SourceRowNum, 1).Value = "END"

oXLResultSheet.Cells(ResultRowNum, 4).Value = oXLSourceSheet.Cells(SourceRowNum, 1).Value

SourceRowNum = SourceRowNum + 1
ResultRowNum = ResultRowNum + 1

Loop

 

[TysonLPrice]

I think it might be:

Dim i as long
i = xlWorksheet.UsedRange.Rows.Count

Gaps in the cells may cause issues. Not sure about that.

 

[RoyVidar]

Loops will be slower.

Utilizing the count on the selection, usedrange or currentregion, as suggested, should work, or - if there are empty cells only at the end, you could use the IsEmpty() function;

[tt]Do Until IsEmpty(oXLSourceSheet.Cells(SourceRowNum, 1))[/tt]

Roy-Vidar