I would like to be able to copy two columns from a .xls file, columns 2
(B) and 9(I) into a .txt file for approximately 100 rows, using VBA.
What is the best way to do this?

Thank you

try this

Sub Copy_Rows()

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

FpatH = "C:\Documents and Settings\username\filename.xls"

Workbooks.Open FpatH
FileP = ActiveWorkbook.path
Range("B1:C100").Copy
Workbooks.Add
ActiveSheet.Paste

ActiveWorkbook.SaveAs Filename:= _
FileP & "\" & "1.txt", FileFormat:=xlUnicodeText,
CreateBackup:=False

ActiveWorkbook.Close True

With Application
.DisplayAlerts = True
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub




On Dec 3, 10:00*am, gedinfo wrote:
I would like to be able to copy two columns from a .xls file, columns 2
(B) and 9(I) into a .txt file for approximately 100 rows, using VBA.
What is the best way to do this?

Thank you

Couple questions:

- Does the .txt file already exist, or are you wanting to create a new .txt
file with just the table in it?

- Do you want two 1x100 tables or one 2x100 table?

--
Frank

"Do or do not; There is no try"
-Yoda


"gedinfo" wrote:

I would like to be able to copy two columns from a .xls file, columns 2
(B) and 9(I) into a .txt file for approximately 100 rows, using VBA.
What is the best way to do this?

Thank you
.

On Dec 2, 11:45*pm, Phuelgod
wrote:
Couple questions:

- Does the .txt file already exist, or are you wanting to create a new .txt
file with just the table in it?

- Do you want two 1x100 tables or one 2x100 table?

--
Frank

"Do or do not; There is no try"
* *-Yoda



"gedinfo" wrote:
I would like to be able to copy two columns from a .xls file, columns 2
(B) and 9(I) into a .txt file for approximately 100 rows, using VBA.
What is the best way to do this?

Thank you
.- Hide quoted text -

- Show quoted text -

Hello Frank,

It does not matter whether or not the file exists; I have test code to
either open or create.

As for the other question, I am only looking for two entries of 100,
so 2 single tables.

Thank you

As for the other question, I am only looking for two entries
of 100, so 2 single tables.

This will place the first 100 cells (line feed delimited) in Column B in the
indicated variable...

Col_B = Join(WorksheetFunction.Transpose(Range("B1:B100")) , vbLf)

and this will do the same for Column I...

Col_I = Join(WorksheetFunction.Transpose(Range("I1:I100")) , vbLf)

You can replace the 100 in each statement with a calculated value for the
Last Row if you will not have exactly 100 entries and you only want the
values for the number of cells down to the last filled in row in that
column. For example, for the Col_B variable (do similarly for the Col_I
variable)...

LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Col_B = Join(WorksheetFunction.Transpose(Range("B1:B" & LastRow)), vbLf)

You can process these variables out to your file in whatever order you want.

--
Rick (MVP - Excel)