Printable View
I have been using VB6 for some time now and now trying my hand at VBA.
I have created a spreadsheet which creates a worksheet, which I would like to take the contents of A1...A27, and create a simple .txt file.
Is there a way to do this in VBA? I can't seem to find posts regarding this kind of an issue.
Ok...after a little research...and tweaking some code I found, (a mixture of a number of posts)....I came up with the following.
Dim ExportFile As String
-----------------------------------------
Public Function ExportTxT(MyPath As String) As Boolean
On Error GoTo ErrorHandler
Dim lCols As Long, lRows As Long
Dim iFileNum As Integer
lCols = 1
lRows = 24
iFileNum = FreeFile
Open MyPath For Output As #iFileNum
For i = 1 To lRows
For j = 1 To lCols
Print #iFileNum, Trim(Cells(i, j).Value);
Print #iFileNum,
DoEvents
Next j
Next i
Print #iFileNum,
ExportTxT = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
-------------------------------------------
Private Sub CommandButton1_Click()
Dim r As Range
Set r = Application.Range("MyRangeName")
ExportFile = "A:\" & r
ExportTxT ExportFile
On Error GoTo BadDisk
If Len(Dir(ExportFile)) <> 0 Then
MsgBox ("File Created"), vbInformation
Else
MsgBox ("Error Writing File"), vbInformation
End If
Exit Sub
BadDisk:
MsgBox ("Please check drive"), vbInformation
End Sub