File Browse Function?

[fallowfz]

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]

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


„fallowfz†ezt írta:

 

[Stefi]

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]

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]

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:

 

[fallowfz]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[JerryH]

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

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

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]

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:

 

[fallowfz]

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 Rothstein \(MVP - VB\)]

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]

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:

 

[Rick Rothstein \(MVP - VB\)]

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

 

[ward376]

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