Column retrieval as an input to an existing workbook

N

NaomiKay

Hi,

im new to vb6, and i have a problem on how to retrieve a particular
column from an existing excel file and append that as another column to
another existing workbook...

also, what about if you want to store the data on that column to appear
every other row...

for example:

ORIGINAL COLUMN
hello
hi
there
how's
life

TRANSFERRED COLUMN
hello
<blank cell>
hi
<blank cell>
there
<blank cell>
how's
<blank cell>
life



need help...
thanks:D
thanks
 
A

Ardus Petus

Adjust constants according to your needs (WB name, WS name, column to move)

Sub MoveIt()
'Assume both workbooks are opened
Const KsrcWB = "mySrcWorkbook.xls"
Const KsrcWS = "Sheet1"
Const KsrcCol = "A" 'Assume src data is in column A

Const KdestWB = "myWorkbook.xls"
Const KdestWS = "Sheet1"
Const KdestCol = "A" 'Assume dest data is in column A

Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim c As Range
Set srcWS = Workbooks(KsrcWB).Worksheets(KsrcWS)
Set destWS = Workbooks(KdestWB).Worksheets(KdestWS)
With srcWS
For Each c In .Range( _
.Cells(1, KsrcCol), _
.Cells(Rows.Count, KsrcCol).End(xlUp) _
)
destWS.Cells(c.Row * 2 - 1, KdestCol).Value = c.Value
Next c
End With
End Sub

HTH
--
AP

"NaomiKay" <[email protected]> a écrit
dans le message de
news:[email protected]...
 
D

Dave Peterson

One way is to just loop through the first range by 1's and the second range by
2's.

Option Explicit
Sub testme01()

Dim FromCell As Range
Dim ToCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Workbooks("book1.xls").Worksheets("sheet1")
Set FromCell = .Range("a1")
FirstRow = FromCell.Row
LastRow = .Cells(.Rows.Count, FromCell.Column).End(xlUp).Row
End With

With Workbooks("book2.xls").Worksheets("sheet1")
Set ToCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(ToCell) Then
'leave it there
Else
'come down two rows?
Set ToCell = ToCell.Offset(2, 0)
End If
End With

For iRow = FirstRow To LastRow
ToCell.Value = FromCell.Offset(iRow - FromCell.Row, 0).Value
Set ToCell = ToCell.Offset(2, 0)
Next iRow

End Sub
 
N

NaomiKay

haven't tried them out yet, but thanks for the quick reps guys... really
appreciate it. :) now, I have an idea how to go about my prob...

thank you thank you thank you;)
 
N

NaomiKay

Hi guys,

using the first code posted above,
i got an error msg when this code was being run

SET SRCWS = WORKBOOKS(KSRCWB).WORKSHEET(KSRCWS)

runtime error 9: subscript out of range

i tried all sorts of things but I can't get past this...

thanks...
 
A

Ardus Petus

My Sub begins with:

Const KsrcWB = "mySrcWorkbook.xls"
Const KsrcWS = "Sheet1"
Const KsrcCol = "A" 'Assume src data is in column A

Const KdestWB = "myWorkbook.xls"
Const KdestWS = "Sheet1"
Const KdestCol = "A" 'Assume dest data is in column A

You have to adjust the calues between ""'s to your own needs, ie your own
Workbooks' and Sheets' names.

HTH
--
AP

"NaomiKay" <[email protected]> a écrit
dans le message de
news:[email protected]...
 
N

NaomiKay

yeah, I already did that... but it doesn't seem to work..

when you say assume that the workbooks are open, do you mean the excel
application itself or I have to open it using a vb6 code?

also, is it necessary for the excel files that i'm using to be in the
same folder as my project? or can I just indicate the file path in the
conts itself...

sorry, i really have no idea
 
A

Ardus Petus

Yes, the way my code is written, you have to manually open both src & dest
Worbooks before you run the macro.

No: don't indicate the full file path in the consts.

Depending on where you want to store the macro (src or dest WB or another
WB),
I can adapt the macro so that it opens the WB's

Cheers,
--
AP

"NaomiKay" <[email protected]> a écrit
dans le message de
news:[email protected]...
 
Top