Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
"Folder Select" Dialogue - Opening multiple files from selected folder
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! |
#2
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
"Folder Select" Dialogue - Opening multiple files from selected folder
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 "Rob" wrote in message ups.com... 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! |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
"Folder Select" Dialogue - Opening multiple files from selected folder
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 ß-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent Excel 2007 from leaving ".tmp" files in my folder | Excel Discussion (Misc queries) | |||
How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | Excel Discussion (Misc queries) | |||
creating an XLS file from " files" data in a Folder | Excel Discussion (Misc queries) | |||
Backup to specific folder if workbook names begins with "NSR" or "MAC" | Excel Programming |