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

JamesgTx

New Member
Joined
Jan 6, 2010
Messages
25
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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.)
 
Last edited:

JamesgTx

New Member
Joined
Jan 6, 2010
Messages
25
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786

ADVERTISEMENT

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

New Member
Joined
Jan 6, 2010
Messages
25
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top