CODEWITHSUNDEEP
vbaexcel
Dan S
Dan S

Reputation: 147

Export a fixed range quickly using FreeFile

I'm trying to get the values for a fixed range of cells (constantly changing) out into a text file at regular intervals as cleanly as possible.

I've got as far as putting one cell value into the filename of the txt:

        FF = FreeFile()
        Open ThisWorkbook.Path & "\" & Range("A" & cell.Row).Value & ".txt" For Output As #FF
        Print #FF, cell.Text
        Close #FF

But I want to put the data into the file, and leave the name alone.

Any help appreciated!

Upvotes: 1

Views: 155

Answers (1)

interesting-name-here
interesting-name-here

Reputation: 1890

Add a reference to your VBA code:

Tools > References > Microsoft Scripting Runtime

Delete the file and write your new one. This will give it the same name every time and will be yourFile.txt. Your directory will then have yourFile.xlsx and yourFile.txt.

Dim fso As New Scripting.FileSystemObject

If fso.FileExists(Replace(Replace(ThisWorkbook.FullName, ".xlsx", ".txt"), ".xls", ".txt")) Then
    fso.DeleteFile (Replace(Replace(ThisWorkbook.FullName, ".xlsx", ".txt"), ".xls", ".txt"))
End If

FF = FreeFile()
Open Replace(Replace(ThisWorkbook.FullName, ".xlsx", ".txt"), ".xls", ".txt") For Output As #FF
Print #FF, cell.Text
Close #FF

Upvotes: 1

Related Questions