CODEWITHSUNDEEP
excelvba
Sophie Chen
Sophie Chen

Reputation: 1

Writing to new .txt file with Excel VBA macro, code snippet won't compile (Expected =)

I'm trying to write an Excel macro that loops through cells (4 columns, some # of rows) and writes the contents to a text file with each cell separated by a carriage return. I'm getting a compile error "= expected" with creating the text file:

Dim filePath As String
filePath = Application.ActiveWorkbook.Path
If Not System.IO.file.Exists(filePath) Then
    System.IO.file.Create(filePath).Dispose()
End If

Full code:

Sub GenerateFile()
    'get last row with data
    Dim lastRow As Integer
    lastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    'loop through rows to build string
    Dim textFile As String
    For i = 2 To lastRow
        textFile = textFile & ActiveSheet.Cells(i, 1) & Chr(13) & ActiveSheet.Cells(i, 2) & Chr(13) & ActiveSheet.Cells(i, 3) & Chr(13) & ActiveSheet.Cells(i, 4) & Chr(13)
    Next i

    'write string to file
    Dim filePath As String
    filePath = Application.ActiveWorkbook.Path
    If Not System.IO.file.Exists(filePath) Then
        System.IO.file.Create(filePath).Dispose()
    End If
End Sub

[EDIT] So I think it may be because I was trying to use a VB code snippet, I've found a guide here(http://www.wiseowl.co.uk/blog/s211/writeline.htm) to use FileSystemObject and TextStream but these require enabling of a library. I need to share this tool with others and cannot ask them all to enable the library :( Any other suggestions?

Upvotes: 0

Views: 3173

Answers (2)

jradich1234
jradich1234

Reputation: 1425

You can try something like the following would work in VBA with out having to add references

Dim filePath As String
filePath = Application.ActiveWorkbook.Path

Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet

Open filePath + "\Output.txt" For Output As #1

For Row = 1 To 100
    For Column = 1 To 100
        Write #1, ws.Cells(Row, Column).Value
    Next Column
Next Row

Close #1

Upvotes: 1

Sophie Chen
Sophie Chen

Reputation: 1

Answered thanks to http://www.wiseowl.co.uk/blog/s211/writeline.htm and @JohnColeman:

Used this below code and enabled reference to 'Microsoft Scripting Runtime' library (Tools>References> 'Microsoft Scripting Runtime')

'get filePath, fileName
Dim filePath As String
filePath = Application.ActiveWorkbook.Path
fileName = "\file.txt"

'create file
Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.CreateTextFile(filePath & fileName, True)
ts.WriteLine (fileString)
ts.Close

Upvotes: 0

Related Questions