Code to automate exporting to a .txt file
 

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

--
gavst
-----------------------------------------------------------------------
gavsta's Profile: http://www.excelforum.com/member.php...fo&userid=2339
View this thread: http://www.excelforum.com/showthread.php?threadid=46859

Code to automate exporting to a .txt file
 

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

"gavsta" wrote in
message ...

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 the
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 to
enter their choice of where they want the file saved, but I don't want
the user to choose. I just want the file saved to a path name that is a
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 each
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 some
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 to
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 Sub


--
gavsta
------------------------------------------------------------------------
gavsta's Profile:
http://www.excelforum.com/member.php...o&userid=23390
View this thread: http://www.excelforum.com/showthread...hreadid=468598

Code to automate exporting to a .txt file
 

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" wrote:


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 the
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 to
enter their choice of where they want the file saved, but I dont want
the user to choose. I just want the file saved to a path name that is a
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 each
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 some
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 to
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 Sub


--
gavsta
------------------------------------------------------------------------
gavsta's Profile: http://www.excelforum.com/member.php...o&userid=23390
View this thread: http://www.excelforum.com/showthread...hreadid=468598

Code to automate exporting to a .txt file
 

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


--
gavsta
------------------------------------------------------------------------
gavsta's Profile: http://www.excelforum.com/member.php...o&userid=23390
View this thread: http://www.excelforum.com/showthread...hreadid=468598