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


--
NaomiKay
------------------------------------------------------------------------
NaomiKay's Profile: http://www.excelforum.com/member.php...o&userid=32159
View this thread: http://www.excelforum.com/showthread...hreadid=519105

Column retrieval as an input to an existing workbook
 

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" a écrit
dans le message de
...

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

Column retrieval as an input to an existing workbook
 

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

Column retrieval as an input to an existing workbook
 

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

Column retrieval as an input to an existing workbook
 

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

--
AP

"NaomiKay" a écrit
dans le message de
...

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

Column retrieval as an input to an existing workbook
 

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

Column retrieval as an input to an existing workbook
 

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" a écrit
dans le message de
...

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

Column retrieval as an input to an existing workbook
 

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

Column retrieval as an input to an existing workbook
 

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" a écrit
dans le message de
...

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