VBA Generate text file

I am using your VB code to print excel to text, I need help in getting data from a sheet other than the sheet in which the command is present

How to insert the sheet name from which we need to fetch the data. I want the text file to be generated with a command button in Sheet 1 using data in Sheet 4? Please help.

I am using this code:-
Sub VBA_write_to_a_text_file_from_Excel_Range()
Dim iCntr
Dim strFile_Path As String
strFile_Path = "C:\temp\test.txt"
Open strFile_Path For Output As #1
For iCntr = 1 To 10
Print #1, Range("A" & iCntr)
Next iCntr
Close #1
End Sub
Top Contributor Asked on December 9, 2016 in VBA: Functions.
Add Comment
3 Answer(s)

Hi, we can pass the sheet name to get the data from the other worksheet:

Please modify this VBA code ‘Range(“A” & iCntr)’ in the above example:

Range("A" & iCntr)

change the above VBA code to refer the worksheet as shown in the below VBA code:

Sheets("Sheet 4").Range("A" & iCntr)

Thanks!

Keymaster Answered on December 9, 2016.

With regards to the above VB code. I need help in having each text file with a unique name. How do I modify my VB code. It will be a great help.

on January 11, 2017.
Add Comment

I need help in having each text file with a unique name. How do I modify my VB code. It will be a great help

Top Contributor Answered on January 11, 2017.
Add Comment

Hi,

Here is the solution for your query.

Sub VBA_write_to_a_text_file_from_Excel_Range()
Dim iCntr
Dim strFile_Path As String
strFile_Path = "C:\temp\test " & Format(Now(), "dd-MMM-yyyy h-m-s") & ".txt"
Open strFile_Path For Output As #1
For iCntr = 1 To 10
Print #1, Sheets("sheet4").Range("A" & iCntr)
Next iCntr
Close #1
End Sub

Thanks!

Expert Answered on January 11, 2017.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.