Open a file from a drop-down list of files 2

[burcello]

How can I make a UserForm display all the files within a given directory and allow the user to choose one of them? I have a piece of code which makes Excel open a CSV text file. It works flawlessly but it requires the user to type into a textbox the exact name of the file she wants to open. I'd rather she just be able to pick one from some kind of drop-down list. I'm not using the FileDialog object because I don't see how it could allow me to control the type of formatting (text, date, currency, etc.) that Excel uses for each column. Is there a way to use a ComboBox or ListBox automatically populated with the names of the files in the current directory instead of using the TextBox? Or, is there a way for FileDialog to open a file using my desired formatting of each column?

This is the code I have so far. I adapted most of it from a macro I recorded:

Private Sub cmdOpenFile_Click()
ChDrive "P"
' The directory containing the files from which the user must choose:
ChDir "P:\Central Records\cvs from Siebel"
    Workbooks.OpenText Filename:= _
        txtFileName.Text, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
'This FieldInfo array is where my column formatting choices are coded.
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
        Array(23, 1), Array(24, 1))
End Sub

I doubt I'm the first person to post a query along these lines. I looked but couldn't find an example that had just what I needed. I may lack the experienced needed to recognize the answer when I see it. Or maybe I wasn't searching for the right terms.

thanks,
cb

 

[fumei]

I am not totally understanding the question, in particular the reference to formatting on columns and something on a userform.

In any case, you could certainly populate a combobox with a list of files from a specified folder using FSO.

Sub UserForm_Initialize()
Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File
Dim var

Set fld = fso.GetFolder("c:\YaddaYadda\Whatever\BlahBlah")
For Each fil In fld.Files
[COLOR=red]' add just the actual file name
' to combobox...who wants to display
' the whole path?[/color red]
   ComboBox1.AddItem fil.ShortName
[COLOR=red]' but you DO need the whole path to
' be able to open it[/color red]
   ReDim Preserve GetMyFile(var)
   GetMyFile(var) = fil.Path
   var = var + 1
Next
ComboBox1.ListIndex = 0
Set fld = Nothing
End Sub

Sub CommandButton1_Click()
Dim j As Long
j = ComboBox1.ListIndex
   Documents.Open FileName:=GetMyFile(j)
Unload Me
End Sub

With the array GetMyFile as a public variable of course. The array is built with the full path, but the combobox displays just the name. The commandbutton opens the file.

This is for Word, but you should be able to adjust for Excel. Again, I am not quite understanding your question, but as for:

How can I make a UserForm display all the files within a given directory and allow the user to choose one of them?

The above is one way.

faq219-2884

Gerry
My paintings and sculpture

 

[genomon]

Being an Access VBA guy with no Excel experience: can you add a common dialog control?

Silence is golden.
Duct tape is silver.

 

[burcello]

Thanks, Gerry.

I think FSO is just the tool I needed, but your code is a little over my head. I'll spend some time studying it.

I'm getting a compile error on this line:

Dim fso As New Scripting.FileSystemObject

It says "User defined type not defined"

Thanks for helping me get on track!

-cb

 

[genomon]

To get rid of the error, you need to check a project reference to the Microsoft Scripting Runtime library.

Silence is golden.
Duct tape is silver.

 

[fumei]

Sorry, yes, I should have mentioned that. You must have a reference to the type library to be able to use FSO.

In the VBE, Tools > References and scroll down and check Microsoft Scripting Runtime.

Also (just in case) the public variable GetMyFile must be declared in a standard module. You can not declare Public variables in object code modules.

faq219-2884

Gerry
My paintings and sculpture

 

[PHV]

You don't really need FSO: have a look at the Dir function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[burcello]

Okay, I have made reference now to the Scripting Runtime library. I'll just fix a couple of other bugs and post the resulting code . . .

 

[burcello]

Thanks fumei, genomon and PH. With your help, my goals are achieved. Here's what I put together:

Private Sub ShowFiles_Click()
Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File
Dim var

Set fld = fso.GetFolder("P:\yaddayadda\blahblahblah")
For Each fil In fld.Files
' I changed this to "fil.Name" instead of "fil.ShortName"
' The ShortName was returning a truncated file name
' which the code I have under CammondButton2_Click()
' couldn't use.
   ComboBox1.AddItem fil.Name
' I commented out the code below because
' (A) I'm ignorant of the terms and objects and syntax and 
' (B) The Workbooks.OpenText expression in the next Sub
' contains a workaround
' important to my project (but not relevent to this thread)
  ' ReDim Preserve GetMyFile(var)
  ' GetMyFile(var) = fil.Path
  ' var = var + 1
Next
ComboBox1.ListIndex = 0
Set fld = Nothing
End Sub

Here's the Sub that opens the file:

Private Sub CommandButton2_Click()
ChDrive "P"
ChDir "P:\yaddayadda\blahblahblah"
 Workbooks.OpenText Filename:= _
        ComboBox1.Text, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
' Array (5, 2) is my Excel workaround
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
        Array(23, 1), Array(24, 1))
End Sub