Column retrieval as an input to an existing workbook

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

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]...
>
> 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
>
>
> --
> NaomiKay
> ------------------------------------------------------------------------
> NaomiKay's Profile:
http://www.excelforum.com/member.php...o&userid=32159
> View this thread: http://www.excelforum.com/showthread...hreadid=519105
>

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

NaomiKay wrote:
>
> 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
>
> --
> NaomiKay
> ------------------------------------------------------------------------
> NaomiKay's Profile: http://www.excelforum.com/member.php...o&userid=32159
> View this thread: http://www.excelforum.com/showthread...hreadid=519105

--

Dave Peterson

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

Thanks for the feedback.
Please come back if you have any problem with my solution

--
AP

"NaomiKay" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>
>
> --
> NaomiKay
> ------------------------------------------------------------------------
> NaomiKay's Profile:
http://www.excelforum.com/member.php...o&userid=32159
> View this thread: http://www.excelforum.com/showthread...hreadid=519105
>

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

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]...
>
> 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...
>
>
> --
> NaomiKay
> ------------------------------------------------------------------------
> NaomiKay's Profile:
http://www.excelforum.com/member.php...o&userid=32159
> View this thread: http://www.excelforum.com/showthread...hreadid=519105
>

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