Hi there,

Just wandering if anyone can help me out. I'm looking for a method of
opening all text files in a folder.

I'm still pretty new to vba/macros - so not sure if this is possible. I
have tried googling and using the office vba help file to no avail.

It seems a filename is required when opening a document in excel using
vba. I have tried changing the filename to *.txt - which works but only
opens the first file then stops.

Would it be possible to use some sort of count function to open them
all?

Cheers

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=526961

Rob


Try (modify path code as required)

Sub OpenFiles()
Dim sDir As String
Dim sPath As String

sPath$ = "c:\Test"
sDir$ = Dir(sPath, vbDirectory)
If sDir = "" Then
MsgBox "Path " & sDir & " Not Found"
End
End If
sDir$ = Dir(sPath & "\*.txt")
Do Until sDir = ""
your code to open text file here
Loop

End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=526961

Thanks Mudraker for the help.

I still seem to be having trouble. The code I am using to open the
textfiles is -

Workbooks.OpenText Filename:= _
"G:\CusSer\Call Centre\C3 Statistics\DB\Rob's Test
Folder\*.txt" _
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 1), Array(13, 1), Array(20, 1), Array(28, 1),
Array(44, 1), Array(46, 1), Array(50 _
, 1)), TrailingMinusNumbers:=True

I can't specify a filename, as this will change on a daily basis (there
are about 15 files to be opened each day - each witha unique report
ID).

Sorry to be a pain...but is there anything else you can suggest?

Thanks

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=526961

Rob

If you use a set naming convention for the file name then you ca
replace "G:\CusSer\Call Centre\C3 Statistics\DB\Rob's Tes
Folder\*.txt"
with a variable

example

FileToOpen = FileName & ".txt"
Workbooks.OpenText Filename:= _
"G:\CusSer\Call Centre\C3 Statistics\DB\Rob's Test Folder\"
FileToOpen_
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array
_
Array(0, 1), Array(13, 1), Array(20, 1), Array(28, 1), Array(44, 1)
Array(46, 1), Array(50 _
, 1)), TrailingMinusNumbers:=True


If you cannot use the above method then try
FileToOpen$ = Application.GetOpenFilename

FileToOpen_
, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array
_
Array(0, 1), Array(13, 1), Array(20, 1), Array(28, 1), Array(44, 1)
Array(46, 1), Array(50 _
, 1)), TrailingMinusNumbers:=Tru

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=52696

FileSearch might do what you want. Here is a word macro that, starting with
an open file, finds the next file in the folder, closes the open one, and
opens the new one. It might have something you can adapt to what you want
to do. You can specify wildcards for the files to be found.

"systemx" wrote:


Hi there,

Just wandering if anyone can help me out. I'm looking for a method of
opening all text files in a folder.

I'm still pretty new to vba/macros - so not sure if this is possible. I
have tried googling and using the office vba help file to no avail.

It seems a filename is required when opening a document in excel using
vba. I have tried changing the filename to *.txt - which works but only
opens the first file then stops.

Would it be possible to use some sort of count function to open them
all?

Cheers

Rob


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=526961