Setting a directory for writing files - Excel 2010 and Windows 7

[JamesgTx]

I have a VBA program that generates output text files from data on a worksheet. The output text files are generated correctly however I cannot seem to get the output text files to write to a directory of my chosing. Here is the code for selecting the directory

Private Sub SetDirectory_Click()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a location for ANSYS Macros and Data Files."
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Canceled"
Else
MsgBox .SelectedItems(1)
End If
End With
End Sub

The message box comes back with the correct directory chosen, but the files all write to the User Documents directory. In the code where the output files are created I have the follwoing code:

intFH = FreeFile()

Filename = Application.DefaultFilePath & "\Empty.txt"
Open Filename For Output As intFH

Is there a setting in Windows 7 or Excel that I am missing?? Any ideas?

Thanks in advance for help with this.

 

[Ruddles]

You're not saving the directory after picking it, then when you set the value of Filename you're using DefaultFilePath.

In SetDirectory_Click, instead of MsgBox .SelectedItems(1), code Folderpath=.SelectedItems(1), then later do Filename=Folderpath & "\Empty.txt".

(You'll have to declare Folderpath somewhere where it's available to both your bits of code, so at the top of the module if both your bits of code are in the same module, or as PUBLIC in a separate general module if your bits of code are in different modules.)

 

[JamesgTx]

Thank you! I will give that a try right away!

 

[JamesgTx]

Ok I added the code and now I am getting a run time error in this part of the code

intFH = FreeFile()

Filename = Folderpath & "\Empty.txt"
Open Filename For Output As intFH

I also used

Dim Folderpath as String

there must be something else I need to do. The error is occuring when executung the

Open Filename for Output as intFH

Thanks to anyone for help

 

[Ruddles]

What's in Folderpath and Filename at that point?

If you open the Immediate window (Ctrl-G) then type ?folderpath and hit ENTER then ?filename and hit ENTER, it'll tell you.

Make sure that both of those bits of code can access the same variable Folderpath. Heading every code module with Option Explicit helps to expose errors of this type.

If the solution doesn't hit you in the face and the code isn't too lengthy, could you post it?

 

[JamesgTx]

Ruggles

I added some MsgBox entries and found my problem. The Filename variable was losing the directory path between subroutines. So I moved the directory selection code to the rest of the subroutine and things are working great now. Thank you so much for your help. I have two separate workbooks that use similar code so I am now updating the second workbook.

 

[Ruddles]

Do get into the habit of heading up all your modules with the Option Explicit directive as this will highlight that type of problem before your program starts to run - i.e. at compile time.

But good - I'm glad it's sorted!