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?


---
Message posted from http://www.ExcelForum.com/

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)

"junkboy " wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/

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

"junkboy " wrote in
message ...
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?


---
Message posted from http://www.ExcelForum.com/

Thanks, Bob!

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

Thanks so much!


---
Message posted from http://www.ExcelForum.com/