I searched and this seemed a topic that came up a lot, but as I'm a
complete newbie at macros I couldn't quite grasp what changes I needed
to make in order to adapt a macro such as Pearson's for my use.

What I have...a folder with 50 or so files that I currently open one
by one with the text import wizard.
What I need to do...I need to open each of those files with tab &
space delimiters, copy certain contents (always cell C32 to the end of
the data in column C), and paste it in a new workbook. Each
subsequent set of copied data would be pasted in the next column in
the new workbook.

I tried recording a macro as I imported one of these files, copied the
desired cell range, and pasted it in a new workbook, but that's as far
as I got. Here is the code for what I've done so far:

Sub Macro2()
'
' Macro2 Macro
'

'
ChDir _
"TestDiretory"
Workbooks.OpenText Filename:= _
"TestDirectory/TestFile" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
Range("C32").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thoughts on what to do next? First, I need it to open all the folders
in a given directory and second I need it to paste each copied column
of data into the next column over in the new workbook. Thank you
greatly for any help you can provide and for having patience with such
a novice.

Firstly, decide how you can differentiate which files are comma- and which
tab-separated format.
Do they have different extensions ?

NickHK

wrote in message
oups.com...
I searched and this seemed a topic that came up a lot, but as I'm a
complete newbie at macros I couldn't quite grasp what changes I needed
to make in order to adapt a macro such as Pearson's for my use.

What I have...a folder with 50 or so files that I currently open one
by one with the text import wizard.
What I need to do...I need to open each of those files with tab &
space delimiters, copy certain contents (always cell C32 to the end of
the data in column C), and paste it in a new workbook. Each
subsequent set of copied data would be pasted in the next column in
the new workbook.

I tried recording a macro as I imported one of these files, copied the
desired cell range, and pasted it in a new workbook, but that's as far
as I got. Here is the code for what I've done so far:

Sub Macro2()
'
' Macro2 Macro
'

'
ChDir _
"TestDiretory"
Workbooks.OpenText Filename:= _
"TestDirectory/TestFile" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
Range("C32").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thoughts on what to do next? First, I need it to open all the folders
in a given directory and second I need it to paste each copied column
of data into the next column over in the new workbook. Thank you
greatly for any help you can provide and for having patience with such
a novice.

Do you open all the .txt files in that folder?

If yes, then maybe something like:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range

Application.ScreenUpdating = False

'change the folder here
myPath = "C:\my documents\excel\test"
If myPath = "" Then Exit Sub
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1")

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
If fCtr DestCell.Parent.Columns.Count Then
MsgBox "too many files!"
Exit Sub
End If
For fCtr = LBound(myNames) To UBound(myNames)
Application.StatusBar _
= "Processing: " & myNames(fCtr) & " at: " & Now

'trailingminusnumbers was added in xl2002.
'if you're going to use xl2k or below,
'don't include it
'but you'll need something else to convert those
'numbers with a trailing minus
Workbooks.OpenText Filename:=myPath & myNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True

Set wks = ActiveSheet

With wks
Set RngToCopy _
= .Range("C32", .Cells(.Rows.Count, "C").End(xlUp))
End With

'put the file name in row 1???
DestCell.Value = myNames(fCtr)
RngToCopy.Copy _
Destination:=DestCell.Offset(1, 0)

'close that text file
wks.Parent.Close savechanges:=False

'get ready for the next one
Set DestCell = DestCell.Offset(0, 1)

Next fCtr
End If

DestCell.Parent.UsedRange.Columns.AutoFit

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

The code puts the filename in row 1. If you don't like this, you could modify
the code--or just delete row 1 when it's done.

And instead of using xldown, I start at the bottom and work my way up. Any data
after an empty row would be included. But I didn't include a check to see if
there was anything in that range C32:Cxxx). If you don't know your data, you
may want to add a check.

wrote:

I searched and this seemed a topic that came up a lot, but as I'm a
complete newbie at macros I couldn't quite grasp what changes I needed
to make in order to adapt a macro such as Pearson's for my use.

What I have...a folder with 50 or so files that I currently open one
by one with the text import wizard.
What I need to do...I need to open each of those files with tab &
space delimiters, copy certain contents (always cell C32 to the end of
the data in column C), and paste it in a new workbook. Each
subsequent set of copied data would be pasted in the next column in
the new workbook.

I tried recording a macro as I imported one of these files, copied the
desired cell range, and pasted it in a new workbook, but that's as far
as I got. Here is the code for what I've done so far:

Sub Macro2()
'
' Macro2 Macro
'

'
ChDir _
"TestDiretory"
Workbooks.OpenText Filename:= _
"TestDirectory/TestFile" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
Range("C32").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thoughts on what to do next? First, I need it to open all the folders
in a given directory and second I need it to paste each copied column
of data into the next column over in the new workbook. Thank you
greatly for any help you can provide and for having patience with such
a novice.

--

Dave Peterson

ps. I read your original message that you wanted each text file delimited by
both the tab character and space character--and your code showed that, too
(sorry Nick!).

Dave Peterson wrote:

Do you open all the .txt files in that folder?

If yes, then maybe something like:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range

Application.ScreenUpdating = False

'change the folder here
myPath = "C:\my documents\excel\test"
If myPath = "" Then Exit Sub
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1")

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
If fCtr DestCell.Parent.Columns.Count Then
MsgBox "too many files!"
Exit Sub
End If
For fCtr = LBound(myNames) To UBound(myNames)
Application.StatusBar _
= "Processing: " & myNames(fCtr) & " at: " & Now

'trailingminusnumbers was added in xl2002.
'if you're going to use xl2k or below,
'don't include it
'but you'll need something else to convert those
'numbers with a trailing minus
Workbooks.OpenText Filename:=myPath & myNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True

Set wks = ActiveSheet

With wks
Set RngToCopy _
= .Range("C32", .Cells(.Rows.Count, "C").End(xlUp))
End With

'put the file name in row 1???
DestCell.Value = myNames(fCtr)
RngToCopy.Copy _
Destination:=DestCell.Offset(1, 0)

'close that text file
wks.Parent.Close savechanges:=False

'get ready for the next one
Set DestCell = DestCell.Offset(0, 1)

Next fCtr
End If

DestCell.Parent.UsedRange.Columns.AutoFit

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

The code puts the filename in row 1. If you don't like this, you could modify
the code--or just delete row 1 when it's done.

And instead of using xldown, I start at the bottom and work my way up. Any data
after an empty row would be included. But I didn't include a check to see if
there was anything in that range C32:Cxxx). If you don't know your data, you
may want to add a check.

wrote:

I searched and this seemed a topic that came up a lot, but as I'm a
complete newbie at macros I couldn't quite grasp what changes I needed
to make in order to adapt a macro such as Pearson's for my use.

What I have...a folder with 50 or so files that I currently open one
by one with the text import wizard.
What I need to do...I need to open each of those files with tab &
space delimiters, copy certain contents (always cell C32 to the end of
the data in column C), and paste it in a new workbook. Each
subsequent set of copied data would be pasted in the next column in
the new workbook.

I tried recording a macro as I imported one of these files, copied the
desired cell range, and pasted it in a new workbook, but that's as far
as I got. Here is the code for what I've done so far:

Sub Macro2()
'
' Macro2 Macro
'

'
ChDir _
"TestDiretory"
Workbooks.OpenText Filename:= _
"TestDirectory/TestFile" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
Range("C32").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thoughts on what to do next? First, I need it to open all the folders
in a given directory and second I need it to paste each copied column
of data into the next column over in the new workbook. Thank you
greatly for any help you can provide and for having patience with such
a novice.

--

Dave Peterson

--

Dave Peterson

On Feb 14, 9:12 am, Dave Peterson wrote:
ps. I read your original message that you wanted each text file delimited by
both the tab character and space character--and your code showed that, too
(sorry Nick!).





Dave Peterson wrote:

Do you open all the .txt files in that folder?

If yes, then maybe something like:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range

Application.ScreenUpdating = False

'change the folder here
myPath = "C:\my documents\excel\test"
If myPath = "" Then Exit Sub
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1")

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
If fCtr DestCell.Parent.Columns.Count Then
MsgBox "too many files!"
Exit Sub
End If
For fCtr = LBound(myNames) To UBound(myNames)
Application.StatusBar _
= "Processing: " & myNames(fCtr) & " at: " & Now

'trailingminusnumbers was added in xl2002.
'if you're going to use xl2k or below,
'don't include it
'but you'll need something else to convert those
'numbers with a trailing minus
Workbooks.OpenText Filename:=myPath & myNames(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True

Set wks = ActiveSheet

With wks
Set RngToCopy _
= .Range("C32", .Cells(.Rows.Count, "C").End(xlUp))
End With

'put the file name in row 1???
DestCell.Value = myNames(fCtr)
RngToCopy.Copy _
Destination:=DestCell.Offset(1, 0)

'close that text file
wks.Parent.Close savechanges:=False

'get ready for the next one
Set DestCell = DestCell.Offset(0, 1)

Next fCtr
End If

DestCell.Parent.UsedRange.Columns.AutoFit

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

The code puts the filename in row 1. If you don't like this, you could modify
the code--or just delete row 1 when it's done.

And instead of using xldown, I start at the bottom and work my way up. Any data
after an empty row would be included. But I didn't include a check to see if
there was anything in that range C32:Cxxx). If you don't know your data, you
may want to add a check.

wrote:

I searched and this seemed a topic that came up a lot, but as I'm a
complete newbie at macros I couldn't quite grasp what changes I needed
to make in order to adapt a macro such as Pearson's for my use.

What I have...a folder with 50 or so files that I currently open one
by one with the text import wizard.
What I need to do...I need to open each of those files with tab &
space delimiters, copy certain contents (always cell C32 to the end of
the data in column C), and paste it in a new workbook. Each
subsequent set of copied data would be pasted in the next column in
the new workbook.

I tried recording a macro as I imported one of these files, copied the
desired cell range, and pasted it in a new workbook, but that's as far
as I got. Here is the code for what I've done so far:

Sub Macro2()
'
' Macro2 Macro
'

'
ChDir _
"TestDiretory"
Workbooks.OpenText Filename:= _
"TestDirectory/TestFile" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True
Range("C32").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

Thoughts on what to do next? First, I need it to open all the folders
in a given directory and second I need it to paste each copied column
of data into the next column over in the new workbook. Thank you
greatly for any help you can provide and for having patience with such
a novice.

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you kindly for the help. The code worked great. Now I have
another, very novice question. Where do I put/save this macro so
that, when I'm in Excel, I can run it. Right now it's saved in a
Macro-enabled workbook. Is opening the workbook with the macro in it
the only way to use the macro? Thanks again!

Macros live in workbooks. And to run the macro, the workbook has to be open.

With a procedure like this, I'd create a dedicated workbook with this single
macro. I'd plop a button from the Forms toolbar on the only worksheet in the
workbook. Then I'd assign this macro to the button.

Then whenever I needed this functionality, I'd open this workbook and click that
big old button.



wrote:
<<snipped

Thank you kindly for the help. The code worked great. Now I have
another, very novice question. Where do I put/save this macro so
that, when I'm in Excel, I can run it. Right now it's saved in a
Macro-enabled workbook. Is opening the workbook with the macro in it
the only way to use the macro? Thanks again!

--

Dave Peterson

On Feb 14, 11:27 am, Dave Peterson wrote:
Macros live in workbooks. And to run the macro, the workbook has to be open.

With a procedure like this, I'd create a dedicated workbook with this single
macro. I'd plop a button from the Forms toolbar on the only worksheet in the
workbook. Then I'd assign this macro to the button.

Then whenever I needed this functionality, I'd open this workbook and click that
big old button.

wrote:

<<snipped



Thank you kindly for the help. The code worked great. Now I have
another, very novice question. Where do I put/save this macro so
that, when I'm in Excel, I can run it. Right now it's saved in a
Macro-enabled workbook. Is opening the workbook with the macro in it
the only way to use the macro? Thanks again!

--

Dave Peterson

That sounds like a great idea. I will give that a go later tonight.
I do have some additional questions though. They relate to the style
of the imported spreadsheet. For example, is it possible to import
into adjoining columns as described earlier, but (for example) to
leave an empty column between every three columns containing data?
Also, I was wondering about the order in which the files get
imported. Is there any way to modify this? For example, I have files
named like so:

ADP - 1nM - 1
ADP - 1nM - 2
ADP - 1nM - 3
ADP - 10nM - 1
ADP - 10nM - 2
ADP - 10nM - 3
ADP - 100nM - 1
ADP - 100nM - 2
ADP - 100nM - 3

That's the order I'd like them to be imported, but they are actually
imported in the following order:

ADP - 100nM - 1
ADP - 100nM - 2
ADP - 100nM - 3
ADP - 10nM - 1
ADP - 10nM - 2
ADP - 10nM - 3
ADP - 1nM - 1
ADP - 1nM - 2

I'm unable to sort them based on the name no matter what format those
title cells are in (general, text, number). Any thoughts?

Thanks again for your kind assistance!
ADP - 1nM - 3

You can leave the empty column by changing this code:

'get ready for the next one
Set DestCell = DestCell.Offset(0, 1)
to:
'get ready for the next one
Set DestCell = DestCell.Offset(0, 1)
If DestCell.Column Mod 4 = 0 Then
Set DestCell = DestCell.Offset(0, 1)
End If

But since your file names aren't nicely formatted, it would be more difficult to
sort. You could write code that formats the names in a consistent manner so
that:

ADP - 1nM - 1
ADP - 1nM - 2
ADP - 1nM - 3
ADP - 10nM - 1
ADP - 10nM - 2
ADP - 10nM - 3
ADP - 100nM - 1
ADP - 100nM - 2
ADP - 100nM - 3

looks more like:
ADP - 0001nM - 1
ADP - 0001nM - 2
ADP - 0001nM - 3
ADP - 0010nM - 1
ADP - 0010nM - 2
ADP - 0010nM - 3
ADP - 0100nM - 1
ADP - 0100nM - 2
ADP - 0100nM - 3

Then sorting becomes a lot easier. (although your sorting requirement is even
more different.)

But I think I'd try to change the thing that creates those files so that it uses
nicer named files.

wrote:

On Feb 14, 11:27 am, Dave Peterson wrote:
Macros live in workbooks. And to run the macro, the workbook has to be open.

With a procedure like this, I'd create a dedicated workbook with this single
macro. I'd plop a button from the Forms toolbar on the only worksheet in the
workbook. Then I'd assign this macro to the button.

Then whenever I needed this functionality, I'd open this workbook and click that
big old button.

wrote:

<<snipped



Thank you kindly for the help. The code worked great. Now I have
another, very novice question. Where do I put/save this macro so
that, when I'm in Excel, I can run it. Right now it's saved in a
Macro-enabled workbook. Is opening the workbook with the macro in it
the only way to use the macro? Thanks again!

--

Dave Peterson

That sounds like a great idea. I will give that a go later tonight.
I do have some additional questions though. They relate to the style
of the imported spreadsheet. For example, is it possible to import
into adjoining columns as described earlier, but (for example) to
leave an empty column between every three columns containing data?
Also, I was wondering about the order in which the files get
imported. Is there any way to modify this? For example, I have files
named like so:

ADP - 1nM - 1
ADP - 1nM - 2
ADP - 1nM - 3
ADP - 10nM - 1
ADP - 10nM - 2
ADP - 10nM - 3
ADP - 100nM - 1
ADP - 100nM - 2
ADP - 100nM - 3

That's the order I'd like them to be imported, but they are actually
imported in the following order:

ADP - 100nM - 1
ADP - 100nM - 2
ADP - 100nM - 3
ADP - 10nM - 1
ADP - 10nM - 2
ADP - 10nM - 3
ADP - 1nM - 1
ADP - 1nM - 2

I'm unable to sort them based on the name no matter what format those
title cells are in (general, text, number). Any thoughts?

Thanks again for your kind assistance!
ADP - 1nM - 3

--

Dave Peterson