File Browse Function?

I have a macro which opens *.txt files from one location. The current
version of the macro opens a *.txt file to perform the desired
operations, closes the file, then opens the next *.txt file in the
list. In order for the macro to know which files I want to open, I
have to type in the file names into excel.

I was wondering if there was a way to point to a particular folder and
have the macro work on all of the files contained in that folder
without having to type out the filename in excel?

Excerpt of the code which opens the files is below...

num = Application.InputBox("Enter the total number of files to be
processed.")

Sheets("File list").Select
[a1].Select
For x = 1 To num
n = ActiveCell.Formula


Workbooks.OpenText Filename:=n, Origin:=xlWindows _
, 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), _
Array(3, 1)), TrailingMinusNumbers:=True

Thanks!

Stefi
Thu Jul 24 07:05:01 CDT 2008

Maybe Getopenfilename method (see Help) helps you, is you use XL2003 or above.
Regards,
Stefi


â??fallowfzâ?? ezt írta:

> I have a macro which opens *.txt files from one location. The current
> version of the macro opens a *.txt file to perform the desired
> operations, closes the file, then opens the next *.txt file in the
> list. In order for the macro to know which files I want to open, I
> have to type in the file names into excel.
>
> I was wondering if there was a way to point to a particular folder and
> have the macro work on all of the files contained in that folder
> without having to type out the filename in excel?
>
> Excerpt of the code which opens the files is below...
>
> num = Application.InputBox("Enter the total number of files to be
> processed.")
>
> Sheets("File list").Select
> [a1].Select
> For x = 1 To num
> n = ActiveCell.Formula
>
>
> Workbooks.OpenText Filename:=n, Origin:=xlWindows _
> , 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), _
> Array(3, 1)), TrailingMinusNumbers:=True
>
> Thanks!
>

Stefi
Thu Jul 24 07:07:00 CDT 2008

Sorry, typo: if you use XL2003 or above.
Stefi


â??Stefiâ?? ezt írta:

> Maybe Getopenfilename method (see Help) helps you, is you use XL2003 or above.
> Regards,
> Stefi
>
>
> â??fallowfzâ?? ezt írta:
>
> > I have a macro which opens *.txt files from one location. The current
> > version of the macro opens a *.txt file to perform the desired
> > operations, closes the file, then opens the next *.txt file in the
> > list. In order for the macro to know which files I want to open, I
> > have to type in the file names into excel.
> >
> > I was wondering if there was a way to point to a particular folder and
> > have the macro work on all of the files contained in that folder
> > without having to type out the filename in excel?
> >
> > Excerpt of the code which opens the files is below...
> >
> > num = Application.InputBox("Enter the total number of files to be
> > processed.")
> >
> > Sheets("File list").Select
> > [a1].Select
> > For x = 1 To num
> > n = ActiveCell.Formula
> >
> >
> > Workbooks.OpenText Filename:=n, Origin:=xlWindows _
> > , 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), _
> > Array(3, 1)), TrailingMinusNumbers:=True
> >
> > Thanks!
> >

fallowfz
Thu Jul 24 07:41:05 CDT 2008

Thanks for the tip.

With this function, I see that it is possible to select an "Arrray" of
files. What would i need to do to 1) select an array of files with
the Getopenfilename method, then 2) perform a series of operations on
each file one-at-a-time?

Stefi
Thu Jul 24 08:16:02 CDT 2008

Sorry, I'm at a loss! I tried

filestoopen = Application.GetOpenFilename(MultiSelect:=True)

It allowed to select multiple files, but returned only the first selected
file name as a string, not an array of file names as it's stated in HELP.

Maybe somebody else can explain the reason!

Stefi


â??fallowfzâ?? ezt írta:

> Thanks for the tip.
>
> With this function, I see that it is possible to select an "Arrray" of
> files. What would i need to do to 1) select an array of files with
> the Getopenfilename method, then 2) perform a series of operations on
> each file one-at-a-time?
>

fallowfz
Thu Jul 24 12:22:25 CDT 2008

That's what I ran into as well. Thanks for giving it a shot.

Rick
Thu Jul 24 13:36:11 CDT 2008

Give this code a try...

Dim X As Long
Dim FilesToOpen As Variant
FilesToOpen = Application.GetOpenFilename(MultiSelect:=True)
For X = LBound(FilesToOpen) To UBound(FilesToOpen)
MsgBox "Selected File #" & X & ": " & FilesToOpen(X)
Next

Don't pick too many files as you should get a MsgBox for each one you
select.

Rick


"fallowfz" <zack.fallowfield@roche.com> wrote in message
news:15b6d76d-e2c9-45f4-ab9b-d7ceca1bf0e1@q28g2000prh.googlegroups.com...
> That's what I ran into as well. Thanks for giving it a shot.
>

JerryH
Thu Jul 24 17:21:01 CDT 2008

How about something like this example:
Sub UseFileDialogOpen()
Dim lngCount As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount
End With
End Sub


"Dana DeLouis" wrote:

> > I was wondering if there was a way to point to a particular folder and
> > have the macro work on all of the files contained in that folder
> > without having to type out the filename in excel?
>
>
> I believe one way if via the DIR command.
>
> Sub Demo()
> Dim s As String
>
> On Error Resume Next
> ChDir ("C:\Your_Path")
>
> s = Dir("*.txt") ' or DIR("") for all files
> If Err.Number = 76 Then
> MsgBox "Error: Path not found"
> End
> End If
>
> Do While Len(s) > 0
> Debug.Print s
> s = Dir()
> Loop
> End Sub
>
> --
> HTH :>)
> Dana DeLouis
>
>
> "fallowfz" <zack.fallowfield@roche.com> wrote in message
> news:00c6b9df-308f-4989-9b61-ed39f9746074@y21g2000hsf.googlegroups.com...
> >I have a macro which opens *.txt files from one location. The current
> > version of the macro opens a *.txt file to perform the desired
> > operations, closes the file, then opens the next *.txt file in the
> > list. In order for the macro to know which files I want to open, I
> > have to type in the file names into excel.
> >
> > I was wondering if there was a way to point to a particular folder and
> > have the macro work on all of the files contained in that folder
> > without having to type out the filename in excel?
> >
> > Excerpt of the code which opens the files is below...
> >
> > num = Application.InputBox("Enter the total number of files to be
> > processed.")
> >
> > Sheets("File list").Select
> > [a1].Select
> > For x = 1 To num
> > n = ActiveCell.Formula
> >
> >
> > Workbooks.OpenText Filename:=n, Origin:=xlWindows _
> > , 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), _
> > Array(3, 1)), TrailingMinusNumbers:=True
> >
> > Thanks!
>

Stefi
Fri Jul 25 02:59:00 CDT 2008

Hi Rick,

I tried your code but it gave a "Type mismatch" error at line

For X = LBound(FilesToOpen) To UBound(FilesToOpen)

Locals window displayed the type of FilesToOpen to be Variant/String, not an
array as it was expected.

Regards,
Stefi

â??Rick Rothstein (MVP - VB)â?? ezt írta:

> Give this code a try...
>
> Dim X As Long
> Dim FilesToOpen As Variant
> FilesToOpen = Application.GetOpenFilename(MultiSelect:=True)
> For X = LBound(FilesToOpen) To UBound(FilesToOpen)
> MsgBox "Selected File #" & X & ": " & FilesToOpen(X)
> Next
>
> Don't pick too many files as you should get a MsgBox for each one you
> select.
>
> Rick
>
>
> "fallowfz" <zack.fallowfield@roche.com> wrote in message
> news:15b6d76d-e2c9-45f4-ab9b-d7ceca1bf0e1@q28g2000prh.googlegroups.com...
> > That's what I ran into as well. Thanks for giving it a shot.
> >
>
>

fallowfz
Fri Jul 25 07:17:40 CDT 2008

JerryH -

That seemed to work, but I was looking for a way to tell the macro to
work on the files which I've selected...not just show the file name.
Is there a way to extend what you've done for this application?

This piece of code below will populate an excel template with the
*.txt file names in a specific directory. This saves me the time of
typing each one out. Is there a way, perhaps via an input box, to not
be tied to one specific directory?

Sub ListFiles()

Const MYPATH = "C:\MyDocuments\"

Dim PutRow As Long, fName As String
PutRow = 1
Columns("a").Clear
fName = Dir(MYPATH & "*.txt")
Cells(PutRow, "a") = fName
PutRow = PutRow + 1
Do
fName = Dir
Cells(PutRow, "a") = fName
PutRow = PutRow + 1
Loop Until fName = ""


End Sub

Rick
Fri Jul 25 09:24:51 CDT 2008

I really don't understand how that could be. I tested the code before
posting it, and I re-tested it just now, and it worked perfectly for me. All
I did was go to any code window (not the Immediate window though), type Sub
Test() and End Sub to give it someplace to work from and copy/paste'd the
code between them. When I run the Sub Test(), the file selector window
appeared... I used the Control Key to click-select a few files (although you
could only select one file if desired) and then clicked the Open button....
a MsgBox appeared for each file selected showing me its path and name. The
code works flawlessly for me... every time... so I am not sure what to tell
you. Is anyone else out there having trouble making the code I posted work?

Rick


"Stefi" <Stefi@discussions.microsoft.com> wrote in message
news:02C352FC-1888-4CD5-8754-705104D145FF@microsoft.com...
> Hi Rick,
>
> I tried your code but it gave a "Type mismatch" error at line
>
> For X = LBound(FilesToOpen) To UBound(FilesToOpen)
>
> Locals window displayed the type of FilesToOpen to be Variant/String, not
> an
> array as it was expected.
>
> Regards,
> Stefi
>
> â??Rick Rothstein (MVP - VB)â?? ezt írta:
>
>> Give this code a try...
>>
>> Dim X As Long
>> Dim FilesToOpen As Variant
>> FilesToOpen = Application.GetOpenFilename(MultiSelect:=True)
>> For X = LBound(FilesToOpen) To UBound(FilesToOpen)
>> MsgBox "Selected File #" & X & ": " & FilesToOpen(X)
>> Next
>>
>> Don't pick too many files as you should get a MsgBox for each one you
>> select.
>>
>> Rick
>>
>>
>> "fallowfz" <zack.fallowfield@roche.com> wrote in message
>> news:15b6d76d-e2c9-45f4-ab9b-d7ceca1bf0e1@q28g2000prh.googlegroups.com...
>> > That's what I ran into as well. Thanks for giving it a shot.
>> >
>>
>>

Stefi
Mon Jul 28 02:01:05 CDT 2008

I did exactly what you wrote, the result (or rather the failure) is the same.
Can it be affected by some environmental factor (version, any settings,
etc.)? I use Excel2003 SP3 Hungarian version.

Thank you for your efforts!
Regards,
Stefi


â??Rick Rothstein (MVP - VB)â?? ezt írta:

> I really don't understand how that could be. I tested the code before
> posting it, and I re-tested it just now, and it worked perfectly for me. All
> I did was go to any code window (not the Immediate window though), type Sub
> Test() and End Sub to give it someplace to work from and copy/paste'd the
> code between them. When I run the Sub Test(), the file selector window
> appeared... I used the Control Key to click-select a few files (although you
> could only select one file if desired) and then clicked the Open button....
> a MsgBox appeared for each file selected showing me its path and name. The
> code works flawlessly for me... every time... so I am not sure what to tell
> you. Is anyone else out there having trouble making the code I posted work?
>
> Rick
>
>
> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
> news:02C352FC-1888-4CD5-8754-705104D145FF@microsoft.com...
> > Hi Rick,
> >
> > I tried your code but it gave a "Type mismatch" error at line
> >
> > For X = LBound(FilesToOpen) To UBound(FilesToOpen)
> >
> > Locals window displayed the type of FilesToOpen to be Variant/String, not
> > an
> > array as it was expected.
> >
> > Regards,
> > Stefi
> >
> > â??Rick Rothstein (MVP - VB)â?? ezt írta:
> >
> >> Give this code a try...
> >>
> >> Dim X As Long
> >> Dim FilesToOpen As Variant
> >> FilesToOpen = Application.GetOpenFilename(MultiSelect:=True)
> >> For X = LBound(FilesToOpen) To UBound(FilesToOpen)
> >> MsgBox "Selected File #" & X & ": " & FilesToOpen(X)
> >> Next
> >>
> >> Don't pick too many files as you should get a MsgBox for each one you
> >> select.
> >>
> >> Rick
> >>
> >>
> >> "fallowfz" <zack.fallowfield@roche.com> wrote in message
> >> news:15b6d76d-e2c9-45f4-ab9b-d7ceca1bf0e1@q28g2000prh.googlegroups.com...
> >> > That's what I ran into as well. Thanks for giving it a shot.
> >> >
> >>
> >>
>
>

Rick
Mon Jul 28 03:22:08 CDT 2008

I am using Excel 2003 (11.8211.8202) SP3 which is the American English
version.

Give the code I posted works flawlessly for me, I am not sure what else I
can tell you. Hopefully someone who knows something about possible
differences between international versions will come along and offer their
input.

Rick


"Stefi" <Stefi@discussions.microsoft.com> wrote in message
news:4199C6EE-3EA5-495A-B102-AEC6947A8D44@microsoft.com...
>I did exactly what you wrote, the result (or rather the failure) is the
>same.
> Can it be affected by some environmental factor (version, any settings,
> etc.)? I use Excel2003 SP3 Hungarian version.
>
> Thank you for your efforts!
> Regards,
> Stefi
>
>
> â??Rick Rothstein (MVP - VB)â?? ezt írta:
>
>> I really don't understand how that could be. I tested the code before
>> posting it, and I re-tested it just now, and it worked perfectly for me.
>> All
>> I did was go to any code window (not the Immediate window though), type
>> Sub
>> Test() and End Sub to give it someplace to work from and copy/paste'd the
>> code between them. When I run the Sub Test(), the file selector window
>> appeared... I used the Control Key to click-select a few files (although
>> you
>> could only select one file if desired) and then clicked the Open
>> button....
>> a MsgBox appeared for each file selected showing me its path and name.
>> The
>> code works flawlessly for me... every time... so I am not sure what to
>> tell
>> you. Is anyone else out there having trouble making the code I posted
>> work?
>>
>> Rick
>>
>>
>> "Stefi" <Stefi@discussions.microsoft.com> wrote in message
>> news:02C352FC-1888-4CD5-8754-705104D145FF@microsoft.com...
>> > Hi Rick,
>> >
>> > I tried your code but it gave a "Type mismatch" error at line
>> >
>> > For X = LBound(FilesToOpen) To UBound(FilesToOpen)
>> >
>> > Locals window displayed the type of FilesToOpen to be Variant/String,
>> > not
>> > an
>> > array as it was expected.
>> >
>> > Regards,
>> > Stefi
>> >
>> > â??Rick Rothstein (MVP - VB)â?? ezt írta:
>> >
>> >> Give this code a try...
>> >>
>> >> Dim X As Long
>> >> Dim FilesToOpen As Variant
>> >> FilesToOpen = Application.GetOpenFilename(MultiSelect:=True)
>> >> For X = LBound(FilesToOpen) To UBound(FilesToOpen)
>> >> MsgBox "Selected File #" & X & ": " & FilesToOpen(X)
>> >> Next
>> >>
>> >> Don't pick too many files as you should get a MsgBox for each one you
>> >> select.
>> >>
>> >> Rick
>> >>
>> >>
>> >> "fallowfz" <zack.fallowfield@roche.com> wrote in message
>> >> news:15b6d76d-e2c9-45f4-ab9b-d7ceca1bf0e1@q28g2000prh.googlegroups.com...
>> >> > That's what I ran into as well. Thanks for giving it a shot.
>> >> >
>> >>
>> >>
>>
>>

ward376
Mon Jul 28 05:59:52 CDT 2008

Stefi,

>I tried your code but it gave a "Type mismatch" error at line

>For X = LBound(FilesToOpen) To UBound(FilesToOpen)

>Locals window displayed the type of FilesToOpen to be Variant/String, not an
>array as it was expected.


Did you change this line:
>Dim FilesToOpen As Variant
to
>Dim FilesToOpen As array?

If you did, that is what's causing the type mishmash.

Cliff Edwards