VBA: Export range to text file

[maraki]

Hi all,

~Great forum - VBA newbie here!

I am looking for a macro which exports to a text file, the range A1:E* where * is the last used cell in column E.

Columns should be separated by tabs in the text file.

any ideas?

many thanks
Maria x

 

[VBA Geek]

Sub CrText()


Dim c00            As Variant
Const textFilePath As String = "C:\Desktop\myTextFile.txt"
Dim lngCounter     As Long
Dim FF             As Integer


FF = VBA.FreeFile
c00 = Range("A1").CurrentRegion


Open textFilePath For Output As #FF
    For lngCounter = LBound(c00, 1) To UBound(c00, 1)
        Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
    Next
Close #FF
MsgBox "Your text file has been created."
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Turn on the macro recorder. Select A1 and choose Go To Special Current Region. Copy the selected range. Insert a new workbook and Paste. Save the active workbook as a txt file. Turn off the macro recorder and look at the recorded code. Adjust it to suit.

 

[maraki]

Sub CrText()


Dim c00            As Variant
Const textFilePath As String = "C:\Desktop\myTextFile.txt"
Dim lngCounter     As Long
Dim FF             As Integer


FF = VBA.FreeFile
c00 = Range("A1").CurrentRegion


Open textFilePath For Output As #FF
    For lngCounter = LBound(c00, 1) To UBound(c00, 1)
        Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
    Next
Close #FF
MsgBox "Your text file has been created."
End Sub

thanks this is very useful. However, the macro will be used by a number of users and they don't necessarily have a C: drive which could lead to errors. I don't need the text file to be saved anywhere - just appear in front of users. Could we amend this detail please?

@andrew: thanks i am already learning how to use the recorder !

x

 

[VBA Geek]

a text file needs to be create before you can write on it. You can let VBA choose where to create it depending on who is using your macro.

textFilePath = CStr(VBA.CurDir) & "\mTextFile.txt"

try

Sub CrText()


Dim c00            As Variant
Dim textFilePath   As String
Dim lngCounter     As Long
Dim FF             As Integer


textFilePath = CStr(VBA.CurDir) & "\mTextFile.txt"


FF = VBA.FreeFile
c00 = Range("A1").CurrentRegion


Open textFilePath For Output As #FF
    For lngCounter = LBound(c00, 1) To UBound(c00, 1)
        Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
    Next
Close #FF
Workbooks.OpenText textFilePath
End Sub

thanks this is very useful. However, the macro will be used by a number of users and they don't necessarily have a C: drive which could lead to errors. I don't need the text file to be saved anywhere - just appear in front of users. Could we amend this detail please?

@andrew: thanks i am already learning how to use the recorder !

x

 

[VBA Geek]

PS

if you wanna open it in a notepad then replace

Workbooks.OpenText textFilePath

with

Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus

 

[maraki]

ok I have tested this and it works but instead of opening this in a workbook, could the file just open in notepad or the default text editor?!

edit: you answered my question first! But what if the user is using MAC ?

 

[VBA Geek]

I have never used VBA on a Mac. But I am assuming it has a default folder where the notepad.exe is located. You need to know where that folder is and then you can update the code accordingly