"Folder Select" Dialogue - Opening multiple files from selected folder

[Rob]

Dear All

I have written a macro that takes three open text files (tab
delimited), moves them as worksheets into the 'mother' workbook
(containing the macro) and runs a load of calculations on them. To
save time, rather than opening the files one by one (and having to
click next, finish etc..) I recorded a macro to do this. It goes
something along the lines of...

Workbooks.OpenText Filename:= _
"X:\yz\abc.txt", Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

This is repeated three times for abc1.txt, abc2.txt &abc3.txt

However, it only takes the files from the same folder as when the macro
was recorded (X:\yz). This folder will be different every time the
macro is run (the three files will always be named the same though).

In order to do this I have copied some code that Lars-Eric Gisslén
submitted another topic (microsoft.public.word.vba.general "File Open
Dialogue") which uses the Folder Dialogue as such...

(blah, blah, Declarations, etc...)'
------------------------
Sub Test()
MsgBox SelectFolder("Choose a folder")
End Sub
------------------------
Private Function SelectFolder(sTitle) As String
blah blah blah...

This brings up the dialogue and once you have chosen a directory, reads
the path back to you in a message box.

This works fine. However, when I try to put my "open files" macro in
the place of the MsgBox bit, it doesn't work, the reason probably
being that I replace

Workbooks.OpenText Filename:= _
"X:\yz\abc.txt", Origin:=xlMSDOS, StartRow:=1, _ With...
Workbooks.OpenText Filename:= _
SelectFolder + "\abc.txt", Origin:=xlMSDOS, StartRow:=1, _

I have even tried doing the SelectFolder + "\abc.txt" bit outside the
'Workbooks.OpenText' bit by creating a single string variable to
combine the two, thusly...

Dim workdammit As String
workdammit = SelectFolder + "\abc.txt"
Workbooks.OpenText Filename:= _
workdammit, Origin:=xlMSDOS, StartRow:=1, _

EVERY time, it kicks back with "Compile Error: Argument not optional"
Can somebody please help me!!

I have tried to keep this as brief as possible, if this doesn't make
sense or for full details of the code, please just ask.

Many thanks

Rob

PS. Big shout out to all you guys on here, as someone who only started
VBA programming three weeks ago, these groups have been immeasurably
useful - keep up the good work!

 

[Mike Fogleman]

It appears that sTitle is the folder string and SelectFolder is the function
name.
Try:
Workbooks.OpenText Filename:= _
sTitle + "\abc.txt", Origin:=xlMSDOS, StartRow:=1, _

Mike F

Dear All

I have written a macro that takes three open text files (tab
delimited), moves them as worksheets into the 'mother' workbook
(containing the macro) and runs a load of calculations on them. To
save time, rather than opening the files one by one (and having to
click next, finish etc..) I recorded a macro to do this. It goes
something along the lines of...

Workbooks.OpenText Filename:= _
"X:\yz\abc.txt", Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

This is repeated three times for abc1.txt, abc2.txt &abc3.txt

However, it only takes the files from the same folder as when the macro
was recorded (X:\yz). This folder will be different every time the
macro is run (the three files will always be named the same though).

In order to do this I have copied some code that Lars-Eric Gisslén
submitted another topic (microsoft.public.word.vba.general "File Open
Dialogue") which uses the Folder Dialogue as such...

(blah, blah, Declarations, etc...)'
------------------------
Sub Test()
MsgBox SelectFolder("Choose a folder")
End Sub
------------------------
Private Function SelectFolder(sTitle) As String
blah blah blah...

This brings up the dialogue and once you have chosen a directory, reads
the path back to you in a message box.

This works fine. However, when I try to put my "open files" macro in
the place of the MsgBox bit, it doesn't work, the reason probably
being that I replace

Workbooks.OpenText Filename:= _
"X:\yz\abc.txt", Origin:=xlMSDOS, StartRow:=1, _ With...
Workbooks.OpenText Filename:= _
SelectFolder + "\abc.txt", Origin:=xlMSDOS, StartRow:=1, _

I have even tried doing the SelectFolder + "\abc.txt" bit outside the
'Workbooks.OpenText' bit by creating a single string variable to
combine the two, thusly...

Dim workdammit As String
workdammit = SelectFolder + "\abc.txt"
Workbooks.OpenText Filename:= _
workdammit, Origin:=xlMSDOS, StartRow:=1, _

EVERY time, it kicks back with "Compile Error: Argument not optional"
Can somebody please help me!!

I have tried to keep this as brief as possible, if this doesn't make
sense or for full details of the code, please just ask.

Many thanks

Rob

PS. Big shout out to all you guys on here, as someone who only started
VBA programming three weeks ago, these groups have been immeasurably
useful - keep up the good work!

 

[Rob]

Hello Mike

Thanks for the response, unfortunately that didn't quite work either
(same error), but it got me thinking on a different track... I now
have a better understanding of what's going on with the whole
'function' malarkey. I have made it work now by using the following
code:

(Same declarations)
------------------------
Sub Test()
Dim filepath As String
filepath = SelectFolder(filepath)
Workbooks.OpenText Filename:= _
filepath + "\abc.txt", Origin:=xlMSDOS, StartRow:=1, _
(etc...)
EndSub
------------------------
(Same Function)

This brings the string into the Subroutine and avoids the whole
"Compile Error: Argument not optional", the thing I still don't
understand is what goes into the brackets after the folder (the
'argument' ), it seems I can put anything in there and it works out the
same (so long as it's qualified as speech - SelectFolder("anything you
want") - or a variable). I suppose it's because whatever is in there
isn't used by the function.

So anyway, it works. Thanks again for your input.

Rob
ß-)