software

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
lt;blank cellgt;
hi
lt;blank cellgt;
there
lt;blank cellgt;
how's
lt;blank cellgt;
life
need help...
thanks
thanks--
NaomiKay
------------------------------------------------------------------------
NaomiKay's Profile: www.excelforum.com/member.php...oamp;userid=32159
View this thread: www.excelforum.com/showthread...hreadid=519105Adjust constants according to your needs (WB name, WS name, column to move)

Sub MoveIt()
'Assume both workbooks are opened
Const KsrcWB = quot;mySrcWorkbook.xlsquot;
Const KsrcWS = quot;Sheet1quot;
Const KsrcCol = quot;Aquot; 'Assume src data is in column A

Const KdestWB = quot;myWorkbook.xlsquot;
Const KdestWS = quot;Sheet1quot;
Const KdestCol = quot;Aquot; '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

quot;NaomiKayquot; gt; a écrit
dans le message de
...
gt;
gt; Hi,
gt;
gt; im new to vb6, and i have a problem on how to retrieve a particular
gt; column from an existing excel file and append that as another column to
gt; another existing workbook...
gt;
gt; also, what about if you want to store the data on that column to appear
gt; every other row...
gt;
gt; for example:
gt;
gt; ORIGINAL COLUMN
gt; hello
gt; hi
gt; there
gt; how's
gt; life
gt;
gt; TRANSFERRED COLUMN
gt; hello
gt; lt;blank cellgt;
gt; hi
gt; lt;blank cellgt;
gt; there
gt; lt;blank cellgt;
gt; how's
gt; lt;blank cellgt;
gt; life
gt;
gt;
gt;
gt; need help...
gt; thanks
gt; thanks
gt;
gt;
gt; --
gt; NaomiKay
gt; ------------------------------------------------------------------------
gt; NaomiKay's Profile:
www.excelforum.com/member.php...oamp;userid=32159
gt; View this thread: www.excelforum.com/showthread...hreadid=519105
gt;
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(quot;book1.xlsquot;).Worksheets(quot;sheet1quot;)
Set FromCell = .Range(quot;a1quot;)
FirstRow = FromCell.Row
LastRow = .Cells(.Rows.Count, FromCell.Column).End(xlUp).Row
End With

With Workbooks(quot;book2.xlsquot;).Worksheets(quot;sheet1quot;)
Set ToCell = .Cells(.Rows.Count, quot;Aquot;).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:
gt;
gt; Hi,
gt;
gt; im new to vb6, and i have a problem on how to retrieve a particular
gt; column from an existing excel file and append that as another column to
gt; another existing workbook...
gt;
gt; also, what about if you want to store the data on that column to appear
gt; every other row...
gt;
gt; for example:
gt;
gt; ORIGINAL COLUMN
gt; hello
gt; hi
gt; there
gt; how's
gt; life
gt;
gt; TRANSFERRED COLUMN
gt; hello
gt; lt;blank cellgt;
gt; hi
gt; lt;blank cellgt;
gt; there
gt; lt;blank cellgt;
gt; how's
gt; lt;blank cellgt;
gt; life
gt;
gt; need help...
gt; thanks
gt; thanks
gt;
gt; --
gt; NaomiKay
gt; ------------------------------------------------------------------------
gt; NaomiKay's Profile: www.excelforum.com/member.php...oamp;userid=32159
gt; View this thread: 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--
NaomiKay
------------------------------------------------------------------------
NaomiKay's Profile: www.excelforum.com/member.php...oamp;userid=32159
View this thread: www.excelforum.com/showthread...hreadid=519105Thanks for the feedback.
Please come back if you have any problem with my solution

--
AP

quot;NaomiKayquot; gt; a écrit
dans le message de
...
gt;
gt; haven't tried them out yet, but thanks for the quick reps guys... really
gt; appreciate it. now, I have an idea how to go about my prob...
gt;
gt; thank you thank you thank you
gt;
gt;
gt; --
gt; NaomiKay
gt; ------------------------------------------------------------------------
gt; NaomiKay's Profile:
www.excelforum.com/member.php...oamp;userid=32159
gt; View this thread: www.excelforum.com/showthread...hreadid=519105
gt;

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: www.excelforum.com/member.php...oamp;userid=32159
View this thread: www.excelforum.com/showthread...hreadid=519105My Sub begins with:

Const KsrcWB = quot;mySrcWorkbook.xlsquot;
Const KsrcWS = quot;Sheet1quot;
Const KsrcCol = quot;Aquot; 'Assume src data is in column A

Const KdestWB = quot;myWorkbook.xlsquot;
Const KdestWS = quot;Sheet1quot;
Const KdestCol = quot;Aquot; 'Assume dest data is in column A

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

HTH
--
AP

quot;NaomiKayquot; gt; a écrit
dans le message de
...
gt;
gt; Hi guys,
gt;
gt; using the first code posted above,
gt; i got an error msg when this code was being run
gt;
gt; SET SRCWS = WORKBOOKS(KSRCWB).WORKSHEET(KSRCWS)
gt;
gt; runtime error 9: subscript out of range
gt;
gt; i tried all sorts of things but I can't get past this...
gt;
gt; thanks...
gt;
gt;
gt; --
gt; NaomiKay
gt; ------------------------------------------------------------------------
gt; NaomiKay's Profile:
www.excelforum.com/member.php...oamp;userid=32159
gt; View this thread: www.excelforum.com/showthread...hreadid=519105
gt;

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--
NaomiKay
------------------------------------------------------------------------
NaomiKay's Profile: www.excelforum.com/member.php...oamp;userid=32159
View this thread: www.excelforum.com/showthread...hreadid=519105Yes, the way my code is written, you have to manually open both src amp; 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

quot;NaomiKayquot; gt; a écrit
dans le message de
...
gt;
gt; yeah, I already did that... but it doesn't seem to work..
gt;
gt; when you say assume that the workbooks are open, do you mean the excel
gt; application itself or I have to open it using a vb6 code?
gt;
gt; also, is it necessary for the excel files that i'm using to be in the
gt; same folder as my project? or can I just indicate the file path in the
gt; conts itself...
gt;
gt; sorry, i really have no idea
gt;
gt;
gt; --
gt; NaomiKay
gt; ------------------------------------------------------------------------
gt; NaomiKay's Profile:
www.excelforum.com/member.php...oamp;userid=32159
gt; View this thread: www.excelforum.com/showthread...hreadid=519105
gt;