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
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

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