Code to automate exporting to a .txt file

[gavsta]

Hi Everyone - Need some help after a very tedious attempt with no luck
Am a novice :-(

I am having problems trying to write code that will automate th
selection of a path for saving file when the user runs a macro.

The code at the moment, when you run the macro will ask the user t
enter their choice of where they want the file saved, but I don’t wan
the user to choose. I just want the file saved to a path name that is
combination of certain cells i.e. cell A1 = QLD, D3 = 3636125, H3
Promo851, so the file will be saved to c:/3636125QLDPromo851.txt.

Every time the user uses the worksheet different data is input, so eac
time the user saves it, it will save to a different file name.

Any help on this would be great!! The code I have used so far is som
that I source from the web and changed to suit my project.
________________________________________________________________


Sub ExporttoTXTFile()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

'Select all cells in range from I15 to last cell with data.
ActiveSheet.Range("I16"
ActiveSheet.Range("I16").End(xlDown)).Select

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(Must be complete path): eg C:/abc.txt", "Export t
TXT File")

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file.
Print #FileNum, Selection.Cells(RowCount, _
ColumnCount).Text


' Start next iteration of ColumnCount loop.
Next ColumnCount

' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum

End Su

 

[PY & Associates]

Try
ActiveWorkbook.SaveAs ("c:/" & Range("D3") & Range("A1")& Range("H3")&
".txt")

 

[Eric White]

Using your example, i.e., "cell A1 = QLD, D3 = 3636125, H3 = Promo851,"
replace your input box user filename query with:

strDestFile = "c:/" & Range("D3").Value & Range("A1").Value &
Range("H3").Value & ".txt"

'Kill existing file , if it exists (and don't throw an error if it doesn't)
On Error Resume Next
Kill strDestFile
On Error Goto 0

'etc.

 

[gavsta]

Well done - Thanks for the code. Helped out heaps......now I have to
tackle the next project