VBA DriveList / DirList Controls??

  • Thread starter Thread starter junkboy
  • Start date Start date
J

junkboy

I've been working on a project in VBA, and what its doing is reading an
excel file and writing out a fixed format text file using some of the
data in the excel file. I'm writing out the text file using the # Print
Statement.

What I need is a way to specify the drive and path for the text file
that will be output. If it were straight VB I could use the DriveList
and DirList controls, but I cannot seem to find an equivalent in VBA.

I don't believe Common Dialog Control will work either because I'm not
saving the excel file. I'm taking data from the excel file and writing
it to the text file. The Cdlg will save the excel file after the dialog
box is closed.

Does anyone know if those controls exist in VBA? And if not, is there a
suggested work around?
 
Could you not use GetSaveAsFileName method which would throw up a dialog box
that the user could navigate through the folders and specify the file name.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Set a reference to the "Microsoft Shell Controls And Automation"
library, and then use code like the following:



Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder2
Set SH = New Shell32.Shell

Set Fldr = SH.BrowseForFolder(0, "Pick A Folder", 0, "C:\")
If Fldr Is Nothing Then
MsgBox "You clicked cancel"
Else
MsgBox "You chose: " & Fldr.Self.Path
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks, Bob!

GetSaveAsFileName was exactly what I was looking for. I don't know why
I didn't find that.

Thanks so much!
 
Back
Top