Hello! I'm really new to Excel VBA and I made a Button called 'Create text file' and I want it to convert the spreadsheet into a text file and save it on my PC when I click it.

I copied some code online and tried it and it didn't work. so can you please guide me in the right direction and help me solve this.

Here's the code I have so far:

Sub Button_click()
'
' Button_click Macro
' Click button to create a text file that includes data on formulaire page
'
Sub create_text_file()

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

'using create text file method
Dim myFile As Object
Set myFile = fld.CreateTextFile("C:\Users\Dell\Desktop\myFolder\myTextFile.txt", True)

End Sub

Thank you!!

CodePudding user response：

Answering to your question:

How to create a text file when you click a button in Excel

Those are the steps i too:

1. Add a form to the project

2. Add a button to form

3. Add following code to click event of the form:

   Private Sub CommandButton1_Click()

   'object to use as folder

   Dim fld As Object Set fld = CreateObject("Scripting.FileSystemObject")

   'using create text file method

   Dim myFile As Object Set myFile = fld.CreateTextFile("C:\TESTES\myTextFile.txt", True)

   End Sub

As you can see the code is quite similar to your, I have only changed the folder were the file is saved.

I have run the code and the file is created, no problem with that.

Now, what happens is that the file has no contents. Depending on what you have on your excel file and what you want to send to your txt file, you can have very diferent aproaches. However to write lines to you txt file you can use the fso that is already initiated.

The code bellow will add a line to the txt file

Private Sub CommandButton1_Click()

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

'using create text file method
Dim myFile As Object
Set myFile = fld.CreateTextFile("C:\TESTES\myTextFile.txt", True)

myFile.WriteLine "test"

myFile.Close

Set fld = Nothing

Set myFile = Nothing

End Sub