Write text file output to user selected directory

[JamesgTx]

I have a workbook that writes out several text files for use with a separate program.

I also have a command button that allows the user to select a directory for the output. That code, from "Excel 2010 Power Programming with VBA" is as follows:

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

End Sub

The second command button created the text files. However, the code I have is still not directing the text file output to the proper, user selected directory. Below is the code for creating one of the text files:


Dim i As Long
Dim CoName As String ' Variable for Tank Company Name
Dim TankNum As String * 4 ' Variable for Tank Number
Dim Locat As String ' Variable for Location of Tank (City,State)
Dim DY As Single ' Variable for Nodal Displacement
Dim YM As Single ' Variable for Young's Modulus
Dim PR As Single ' Variable for Poisson's Ratio
Dim R1 As Single ' Variable for Real Constant 1 - Tank Bottom Thickness
Dim R2 As Single ' Variable for Real Constant 2 - Tank Shell Thickness
Dim intFH As Integer ' Dummy integer variable for opening text file

' Create and Open the file Empty.txt. This is the ANSYS Macro file to create the FE Model

intFH = FreeFile()

Open "EMPTY.txt" For Output As intFH

' Assign Cell values to specific variables

YM = Cells(14, 3).Value ' Get Young's Modulus from Worksheet
PR = Cells(15, 3).Value ' Get Poisson's Ratio from Worksheet
R1 = Cells(11, 3).Value ' Get Real Constant 1 from Worksheet
R2 = Cells(12, 3).Value ' Get Real Constant 2 from Worksheet
CoName = Cells(3, 2).Value ' Get Tank Company Name from Worksheet
TankNum = Cells(4, 3).Value ' Get Tank Number from Worksheet
Locat = Cells(5, 2).Value ' Get Location of Tank (City,State)

'Write Information to the EMPTY.txt file - ANSYS Macro
Print #intFH, "EMPTY"
Print #intFH, "/prep 7"
Print #intFH, "/TITLE," & CoName & "Tank Number" & TankNum & "at" & Locat ' Title for FE Model
Print #intFH, "ET,1,SHELL51" 'Set Element type for ANSYS Model
Print #intFH, "MP,EX,1," & YM ' Get Young's Modulus from Worksheet
Print #intFH, "MP,PRXY,1," & PR 'Get Poisson's Ratio from Worksheet
Print #intFH, "R,1," & R1 'Get Real Constant 1 (Tank Bottom Thickness) from Worksheet
Print #intFH, "R,2," & R2 'Get Real Constant 2 (Tank Shell Thickness) from Worksheet
Print #intFH, "NREAD,Node,txt" ' Read in Nodes for FE Model
Print #intFH, "EREAD,Element,txt" ' read in Elements for FE Model
' Loop to print out node displacements
For i = 1 To 40
DY = Cells(i + 4, 12).Value
Print #intFH, "D," & i & ",UY," & DY
Next i
Print #intFH, "finish"
Print #intFH, "/EOF"

'Close File
Close #intFH

I imagine I need a line of code to set the directory but I am not sure what it would be. Any help is greatly appreciated.

 

[JamesgTx]

I modified the code as follows:

Dim i As Long
Dim Filename As String ' Variable for File Path and File Name
Dim CoName As String ' Variable for Tank Company Name
Dim TankNum As String * 4 ' Variable for Tank Number
Dim Locat As String ' Variable for Location of Tank (City,State)
Dim DY As Single ' Variable for Nodal Displacement
Dim YM As Single ' Variable for Young's Modulus
Dim PR As Single ' Variable for Poisson's Ratio
Dim R1 As Single ' Variable for Real Constant 1 - Tank Bottom Thickness
Dim R2 As Single ' Variable for Real Constant 2 - Tank Shell Thickness
Dim intFH As Integer ' Dummy integer variable for opening text file

' Create and Open the file Empty.txt. This is the ANSYS Macro file to create the FE Model

intFH = FreeFile()

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

' Assign Cell values to specific variables

YM = Cells(14, 3).Value ' Get Young's Modulus from Worksheet
PR = Cells(15, 3).Value ' Get Poisson's Ratio from Worksheet
R1 = Cells(11, 3).Value ' Get Real Constant 1 from Worksheet
R2 = Cells(12, 3).Value ' Get Real Constant 2 from Worksheet
CoName = Cells(3, 2).Value ' Get Tank Company Name from Worksheet
TankNum = Cells(4, 3).Value ' Get Tank Number from Worksheet
Locat = Cells(5, 2).Value ' Get Location of Tank (City,State)

'Write Information to the EMPTY.txt file - ANSYS Macro
Print #intFH, "EMPTY"
Print #intFH, "/prep 7"
Print #intFH, "/TITLE," & CoName & "Tank Number" & TankNum & "at" & Locat ' Title for FE Model
Print #intFH, "ET,1,SHELL51" 'Set Element type for ANSYS Model
Print #intFH, "MP,EX,1," & YM ' Get Young's Modulus from Worksheet
Print #intFH, "MP,PRXY,1," & PR 'Get Poisson's Ratio from Worksheet
Print #intFH, "R,1," & R1 'Get Real Constant 1 (Tank Bottom Thickness) from Worksheet
Print #intFH, "R,2," & R2 'Get Real Constant 2 (Tank Shell Thickness) from Worksheet
Print #intFH, "NREAD,Node,txt" ' Read in Nodes for FE Model
Print #intFH, "EREAD,Element,txt" ' Read in Elements for FE Model
' Loop to print out node displacements
For i = 1 To 40
DY = Cells(i + 4, 12).Value
Print #intFH, "D," & i & ",UY," & DY
Next i
Print #intFH, "finish"
Print #intFH, "/EOF"

'Close File
Close #intFH

However, I still get the text writing to my C:\Libraries\Documents Directory. I am in Windows 7 (64 bit) and Using Excel 2010.

Any help is greatly appreciated.

 

[bertie]

Try playing about with the CurDir() function to see if that meets your needs.

[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=darkblue]Dim[/COLOR] iFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 
    iFolder = Application.FileDialog(msoFileDialogFolderPicker).Show
    sFolder = CurDir
    MsgBox sFolder
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[JamesgTx]

bertie

Should I use this code in place of the code I have to let the user select the directory and path?

Thank you for your help.

James

 

[bertie]

Test this code first of all.

Open a new excel workbook.
Ceate a user form with a command button, name SetDirectory.
Copy and paste the code into the form module.
Run the form and check the text file have been sent to the required folder.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] SetDirectory_Click()
   [COLOR=darkblue]Dim[/COLOR] iFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   iFolder = Application.FileDialog(msoFileDialogFolderPicker).Show
 
   OutputTextFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Sub[/COLOR] OutputTextFile()
   [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] intFH [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
[COLOR=red]sPath = CurDir & "\EMPTY.txt"[/COLOR]
[COLOR=red]intFH = FreeFile[/COLOR]
[COLOR=red]Open sPath For Output As #intFH[/COLOR]
 
      [COLOR=darkblue]Print[/COLOR] #intFH, "Test"
   [COLOR=darkblue]Close[/COLOR] #intFH
[COLOR=darkblue]End[/COLOR] Sub

The highlighted code shows how to use the CurDir() function to get the path to the directory.

 

[JamesgTx]

I did this and it came back with an error code 52 - Bad file name or number

The code line the debugger highlights is

Open sPath For Output As #intFH

I will continue to work with this. Thank you for the help so far. Any additional ideas are greatly appreciated.

Thank you.

 

[bertie]

Remove the hash mark.

Open sPath For Output As #intFH

 

[JamesgTx]

Ok

Here is my code for the test workbook

Private Sub SetDirectory_Click()

Dim iFolder As Integer
iFolder = Application.FileDialog(msoFileDialogFolderPicker).Show

OutputTextFile
End Sub


Sub OutputTextFile()
Dim sPath As String
Dim intFH As Integer

sPath = CurDir & "\EMPTY.txt"
intFH = FreeFile
Open sPath For Output As intFH

Print #intFH, "Test"
Close #intFH



End Sub

I am not getting any debug errors. When I click the command button I am able to select a directory. However, the Empty .txt file is being put in a directory one level above the one I am selecting. Thoughts?

 

[bertie]

I do remember reading something about the CurDir() function where it doesn't update if you only highlight the folder; I think you have to actually open the folder (double click).

 

[JamesgTx]

Even when I double click and get into the directory the Empty.txt file still is placed in the directory above where I double clicked. Could I have a setting wrong? Very strange.