HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

I recorded it already one time but it is all set to exactly the selected
folder and the file name nothing is random. So only for this file it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

Yes, of course that would be the case. You must post the code you have and
tell us how you want to select the directory and the files to be processed,
i.e. what the file extensions are, etc.


On Mon, 14 Mar 2005 20:37:04 -0800, bandy2000
wrote:

I recorded it already one time but it is all set to exactly the selected
folder and the file name nothing is random. So only for this file it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

what I wanted to do is to select a folder and automatically all files with
the ".res" suffix will be processed. The folder should be selected in a
regular file dialog.

this is the code for the opening of the file that I recorded earlier:
Sub Macro_open_file()
'
' Macro_open_file Macro
' Macro recorded 3/13/2005 '

'
Workbooks.OpenText Filename:= _
"C:\Measurement Results\FolderName\FileName.res" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True

I'm not sure how I will have to do the copy and paste operation and how I
should record it.

"Myrna Larson" wrote:

Yes, of course that would be the case. You must post the code you have and
tell us how you want to select the directory and the files to be processed,
i.e. what the file extensions are, etc.


On Mon, 14 Mar 2005 20:37:04 -0800, bandy2000
wrote:

I recorded it already one time but it is all set to exactly the selected
folder and the file name nothing is random. So only for this file it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

The vba function you want to start with is
GetOpenFilename

See the Help for this.

"bandy2000" wrote:

what I wanted to do is to select a folder and automatically all files with
the ".res" suffix will be processed. The folder should be selected in a
regular file dialog.

this is the code for the opening of the file that I recorded earlier:
Sub Macro_open_file()
'
' Macro_open_file Macro
' Macro recorded 3/13/2005 '

'
Workbooks.OpenText Filename:= _
"C:\Measurement Results\FolderName\FileName.res" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True

I'm not sure how I will have to do the copy and paste operation and how I
should record it.

"Myrna Larson" wrote:

Yes, of course that would be the case. You must post the code you have and
tell us how you want to select the directory and the files to be processed,
i.e. what the file extensions are, etc.


On Mon, 14 Mar 2005 20:37:04 -0800, bandy2000
wrote:

I recorded it already one time but it is all set to exactly the selected
folder and the file name nothing is random. So only for this file it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

Following is the code that allows me already to select a folder and to count
the files with the "*.res" and than open a specific file as a new workbook.
This should be no problem to be embedded into a For Loop that processes all
files contained in the folder.
But what I actually want is to open the files as a new worksheet.
How can I do this?



With Application.FileDialog(msoFileDialogFolderPicker)
.Show
selection = .SelectedItems.Count
Path = .SelectedItems(selection)
End With



Set fs = Application.FileSearch
With fs
.LookIn = Path
.Filename = "*.res"
If .Execute 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
Else
MsgBox "There were no files found."
End If
Set AllFilesFound = .FoundFiles

End With

MsgBox AllFilesFound(5)


Workbooks.OpenText Filename:= _
AllFilesFound(5) _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True


"gocush" wrote:

The vba function you want to start with is
GetOpenFilename

See the Help for this.

"bandy2000" wrote:

what I wanted to do is to select a folder and automatically all files with
the ".res" suffix will be processed. The folder should be selected in a
regular file dialog.

this is the code for the opening of the file that I recorded earlier:
Sub Macro_open_file()
'
' Macro_open_file Macro
' Macro recorded 3/13/2005 '

'
Workbooks.OpenText Filename:= _
"C:\Measurement Results\FolderName\FileName.res" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True

I'm not sure how I will have to do the copy and paste operation and how I
should record it.

"Myrna Larson" wrote:

Yes, of course that would be the case. You must post the code you have and
tell us how you want to select the directory and the files to be processed,
i.e. what the file extensions are, etc.


On Mon, 14 Mar 2005 20:37:04 -0800, bandy2000
wrote:

I recorded it already one time but it is all set to exactly the selected
folder and the file name nothing is random. So only for this file it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help with
modifications to make it operate on all files in a directory (.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new worksheet for
each file?
How can I select the folder before as it is not static?
Thanks

Turn on the macro recorder and go to Date=Get External Data (or Import
external data) and import your file. Turn off the macro recorder. Now you
can modify this code to allow you to pass the selected file name or pass the
name in a loop.

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Following is the code that allows me already to select a folder and to
count
the files with the "*.res" and than open a specific file as a new
workbook.
This should be no problem to be embedded into a For Loop that processes
all
files contained in the folder.
But what I actually want is to open the files as a new worksheet.
How can I do this?



With Application.FileDialog(msoFileDialogFolderPicker)
.Show
selection = .SelectedItems.Count
Path = .SelectedItems(selection)
End With



Set fs = Application.FileSearch
With fs
.LookIn = Path
.Filename = "*.res"
If .Execute 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
Else
MsgBox "There were no files found."
End If
Set AllFilesFound = .FoundFiles

End With

MsgBox AllFilesFound(5)


Workbooks.OpenText Filename:= _
AllFilesFound(5) _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True


"gocush" wrote:

The vba function you want to start with is
GetOpenFilename

See the Help for this.

"bandy2000" wrote:

what I wanted to do is to select a folder and automatically all files
with
the ".res" suffix will be processed. The folder should be selected in
a
regular file dialog.

this is the code for the opening of the file that I recorded earlier:
Sub Macro_open_file()
'
' Macro_open_file Macro
' Macro recorded 3/13/2005 '

'
Workbooks.OpenText Filename:= _
"C:\Measurement Results\FolderName\FileName.res" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True

I'm not sure how I will have to do the copy and paste operation and
how I
should record it.

"Myrna Larson" wrote:

Yes, of course that would be the case. You must post the code you
have and
tell us how you want to select the directory and the files to be
processed,
i.e. what the file extensions are, etc.


On Mon, 14 Mar 2005 20:37:04 -0800, bandy2000
wrote:

I recorded it already one time but it is all set to exactly the
selected
folder and the file name nothing is random. So only for this file
it can be
reused.


"Myrna Larson" wrote:

You'll probably need to open each one, then move or copy the
sheet into the
master workbook.

Record the steps as you do this manually, then somebody can help
with
modifications to make it operate on all files in a directory
(.TXT? .CSV?)


On Mon, 14 Mar 2005 15:57:01 -0800, bandy2000
wrote:

HI
How can I load all files contained in one folder into a new
worksheet for
each file?
How can I select the folder before as it is not static?
Thanks