need VB list property for file contents
 

I am trying to insert content from 1000 text files into a column in excel and
I found this module in another post and am trying to modify it. The word
that needs to be replaced with the correct property is "MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly appreciated! The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly

need VB list property for file contents
 

What information do you want placed in the third column? (what property do
you want placed there). MsoAlertDefaultType is pretty much meaningless to
me, so I don't know what the intent was.

If you don't know what you want there, then I suggest just deleting the
line.

--
Regards,
Tom Ogilvy



"kwilson" wrote in message
...
I am trying to insert content from 1000 text files into a column in excel
and
I found this module in another post and am trying to modify it. The word
that needs to be replaced with the correct property is
"MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly appreciated! The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly

need VB list property for file contents
 

Hi Tom -

First, thanks for your response and willingness to help! I need content
from text files placed into excel cells in column just like the file names
are placed. I'm not sure what specific property I need placed there - that's
where I need your help. I need to know what property to write in place of
"MsoAlertDefaultType" so I can get the contents of those files extracted and
placed into those cells. I hope this isn't confusing!

Thanks again for your help!.

"Tom Ogilvy" wrote:

What information do you want placed in the third column? (what property do
you want placed there). MsoAlertDefaultType is pretty much meaningless to
me, so I don't know what the intent was.

If you don't know what you want there, then I suggest just deleting the
line.

--
Regards,
Tom Ogilvy



"kwilson" wrote in message
...
I am trying to insert content from 1000 text files into a column in excel
and
I found this module in another post and am trying to modify it. The word
that needs to be replaced with the correct property is
"MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly appreciated! The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly

need VB list property for file contents
 

All that does is list the filenames and their datetime.

If your doing 1000 files, then hopefully they are less than 65 rows each (on
average).


Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next
set sh = ActiveSheet
NextFile = Dir(Folder & "\*.CSV")
Do Until NextFile = ""
set bk = workbooks.Open(Folder & "\" & nextFile)
bk.worksheets(1).UsedRange.Copy Destination:= _
sh.Cells(rows.count,1).End(xlup)(2)
bk.Close SaveChanges:=False
Loop
End Sub

--
Regards,
Tom Ogilvy

"kwilson" wrote in message
...
Hi Tom -

First, thanks for your response and willingness to help! I need content
from text files placed into excel cells in column just like the file names
are placed. I'm not sure what specific property I need placed there -
that's
where I need your help. I need to know what property to write in place of
"MsoAlertDefaultType" so I can get the contents of those files extracted
and
placed into those cells. I hope this isn't confusing!

Thanks again for your help!.

"Tom Ogilvy" wrote:

What information do you want placed in the third column? (what
property do
you want placed there). MsoAlertDefaultType is pretty much meaningless
to
me, so I don't know what the intent was.

If you don't know what you want there, then I suggest just deleting the
line.

--
Regards,
Tom Ogilvy



"kwilson" wrote in message
...
I am trying to insert content from 1000 text files into a column in
excel
and
I found this module in another post and am trying to modify it. The
word
that needs to be replaced with the correct property is
"MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly appreciated!
The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly

need VB list property for file contents
 

Hi Tom -

Thanks for the macro. It worked but only opened two files and pasted them
into Excel. Can you modify the macro so that it opens all of the files and
pastes their text into one cell (one row, not multiple) at a time?

Thanks!

Kelly

"Tom Ogilvy" wrote:

All that does is list the filenames and their datetime.

If your doing 1000 files, then hopefully they are less than 65 rows each (on
average).


Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next
set sh = ActiveSheet
NextFile = Dir(Folder & "\*.CSV")
Do Until NextFile = ""
set bk = workbooks.Open(Folder & "\" & nextFile)
bk.worksheets(1).UsedRange.Copy Destination:= _
sh.Cells(rows.count,1).End(xlup)(2)
bk.Close SaveChanges:=False
Loop
End Sub

--
Regards,
Tom Ogilvy

"kwilson" wrote in message
...
Hi Tom -

First, thanks for your response and willingness to help! I need content
from text files placed into excel cells in column just like the file names
are placed. I'm not sure what specific property I need placed there -
that's
where I need your help. I need to know what property to write in place of
"MsoAlertDefaultType" so I can get the contents of those files extracted
and
placed into those cells. I hope this isn't confusing!

Thanks again for your help!.

"Tom Ogilvy" wrote:

What information do you want placed in the third column? (what
property do
you want placed there). MsoAlertDefaultType is pretty much meaningless
to
me, so I don't know what the intent was.

If you don't know what you want there, then I suggest just deleting the
line.

--
Regards,
Tom Ogilvy



"kwilson" wrote in message
...
I am trying to insert content from 1000 text files into a column in
excel
and
I found this module in another post and am trying to modify it. The
word
that needs to be replaced with the correct property is
"MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly appreciated!
The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly

need VB list property for file contents
 

Sorry, I left out a line. Here is a revision.

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next
set sh = ActiveSheet
NextFile = Dir(Folder & "\*.CSV")
Do Until NextFile = ""
set bk = workbooks.Open(Folder & "\" & nextFile)
bk.worksheets(1).UsedRange.Copy Destination:= _
sh.Cells(rows.count,1).End(xlup)(2)
bk.Close SaveChanges:=False
NextFile = Dir()
Loop
End Sub

as far as your other request, I would have to know what the file looks like
and what you want the results to look like. However, If the file is only
one line long, then it should do that already. If it is multiple lines but
a single column wide then possibly this:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long, rng as Range
On Error Resume Next
set sh = ActiveSheet
NextFile = Dir(Folder & "\*.CSV")
Do Until NextFile = ""
set bk = workbooks.Open(Folder & "\" & nextFile)
with bk.worksheets(1)
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
rng.Copy
sh.Cells(rows.count,1).End(xlup)(2).PasteSpecial Transpose:=True
bk.Close SaveChanges:=False
NextFile = Dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


"kwilson" wrote in message
...
Hi Tom -

Thanks for the macro. It worked but only opened two files and pasted them
into Excel. Can you modify the macro so that it opens all of the files
and
pastes their text into one cell (one row, not multiple) at a time?

Thanks!

Kelly

"Tom Ogilvy" wrote:

All that does is list the filenames and their datetime.

If your doing 1000 files, then hopefully they are less than 65 rows each
(on
average).


Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next
set sh = ActiveSheet
NextFile = Dir(Folder & "\*.CSV")
Do Until NextFile = ""
set bk = workbooks.Open(Folder & "\" & nextFile)
bk.worksheets(1).UsedRange.Copy Destination:= _
sh.Cells(rows.count,1).End(xlup)(2)
bk.Close SaveChanges:=False
Loop
End Sub

--
Regards,
Tom Ogilvy

"kwilson" wrote in message
...
Hi Tom -

First, thanks for your response and willingness to help! I need
content
from text files placed into excel cells in column just like the file
names
are placed. I'm not sure what specific property I need placed there -
that's
where I need your help. I need to know what property to write in
place of
"MsoAlertDefaultType" so I can get the contents of those files
extracted
and
placed into those cells. I hope this isn't confusing!

Thanks again for your help!.

"Tom Ogilvy" wrote:

What information do you want placed in the third column? (what
property do
you want placed there). MsoAlertDefaultType is pretty much
meaningless
to
me, so I don't know what the intent was.

If you don't know what you want there, then I suggest just deleting
the
line.

--
Regards,
Tom Ogilvy



"kwilson" wrote in message
...
I am trying to insert content from 1000 text files into a column
in
excel
and
I found this module in another post and am trying to modify it.
The
word
that needs to be replaced with the correct property is
"MsoAlertDefaultType".
I'm not very knowledgable in VB so any help is greatly
appreciated!
The
module is below:

Sub Test()
Call ListWordFiles("C:\Biology")
End Sub

Sub ListWordFiles(Folder As String)
Dim NextFile As String
Dim L As Long
On Error Resume Next

NextFile = Dir(Folder & "\*")
Do Until NextFile = ""
L = L + 1
Cells(L, 1) = Folder & "\" & NextFile
Cells(L, 2) = FileDateTime(Folder & "\" & NextFile)
Cells(L, 3) = MsoAlertDefaultType(Folder & "\" & NextFile)
NextFile = Dir()
Loop
End Sub


Thanks,

Kelly