$show=home

VBA - Text Files

VBA - Text Files Shout4Education
You can also read Excel File and write the contents of the cell into a Text File using VBA. VBA allows the users to work with text files using two methods −

  • File System Object
  • using Write Command

File System Object (FSO)

As the name suggests, FSOs help the developers to work with drives, folders, and files. In this section, we will discuss how to use a FSO.
Sr.No.Object Type & Description
1
Drive
Drive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system.
2
Drives
Drives is a Collection. It provides a list of the drives attached to the system, either physically or logically.
3
File
File is an Object. It contains methods and properties that allow developers to create, delete, or move a file.
4
Files
Files is a Collection. It provides a list of all the files contained within a folder.
5
Folder
Folder is an Object. It provides methods and properties that allow the developers to create, delete, or move folders.
6
Folders
Folders is a Collection. It provides a list of all the folders within a folder.
7
TextStream
TextStream is an Object. It enables the developers to read and write text files.

Drive

Drive is an object, which provides access to the properties of a particular disk drive or network share. Following properties are supported by Drive object −
  • AvailableSpace
  • DriveLetter
  • DriveType
  • FileSystem
  • FreeSpace
  • IsReady
  • Path
  • RootFolder
  • SerialNumber
  • ShareName
  • TotalSize
  • VolumeName

Example

Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot.
Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot shout4education

Step 2 − Add "Microsoft Scripting RunTime" and Click OK.
Microsoft Scripting RunTime Shout4Education

Step 3 − Add Data that you would like to write in a Text File and add a Command Button.
Add Data that you would like to write in a Text File and add a Command Button Shout4Education

Step 4 − Now it is time to Script.
Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = Trim(ActiveCell(i, j).Value)
         stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
      Next j
   Next i
  
   stream.Close
   MsgBox ("Job Done")
End Sub

Output

When executing the script, ensure that you place the cursor in the first cell of the worksheet. The Support.log file is created as shown in the following screenshot under "D:\Try".
file is created as shown in the following screenshot Shout4Education

The Contents of the file are shown in the following screenshot.
The Contents of the file are shown in the following screenshot Shout4Education

Write Command

Unlike FSO, we need NOT add any references, however, we will NOT be able to work with drives, files and folders. We will be able to just add the stream to the text file.

Example

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   FilePath = "D:\Try\write.txt"
   Open FilePath For Output As #2
  
   CellData = ""
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
         Write #2, CellData
      Next j
   Next i
  
   Close #2
   MsgBox ("Job Done")
End Sub

Output

Upon executing the script, the "write.txt" file is created in the location "D:\Try" as shown in the following screenshot.
Output file after executing the script Shout4Education

The contents of the file are shown in the following screenshot.
The contents of the file are shown in the following screenshot Shout4Education

Comments

Blogger
Name

.NET_Interview,1,Accenture_GFT,1,Accenture_Prep,1,Advance_Excel,22,Advance_Python,10,Advanced_Linux,6,Advanced_SQL,18,Advanced_Unix,6,AMCAT,1,AMCAT_Prep,1,AMCAT_Solved_Papers,1,Android,1,Apache_Sqoop,10,Aptitude,1,AWS,2,AWS_Dumps,1,AWS_Interview,1,B_Tech,19,Banking_Exam,1,Basic_Linux,29,Basic_Python,19,Basic_SQL,24,Basic_Unix,30,Best_Websites,1,Big_Data_Analytics,70,Blog,274,Blogger,3,Blogging,2,Blogspot,1,Books,1,BTech,20,C++_Interview,1,CBSE,30,Civil_1st_Semester,1,Class_12,28,Class_12_Chemistry,4,Class_12_Computer_Science,7,Class_12_Mathematics,1,Class_12_NCERT,15,Class_12_NCERT_Solutions,15,Class_12_Physics,18,Class_12_Physics_NCERT_Solutions,15,Class_12_Science,29,Cloud_Service,1,Coding,1,Communication,2,Computer,18,Computer_Memory,2,Computer_Programming,2,Computer_Science,4,Control_System,9,Crack_Interview,3,CSE_5th_Semester,1,CSS,1,Data_Analytics,16,Data_Science,18,Data_Science_Interview,1,Database,47,Database_Interview,4,Dumps,2,ECE_1st_Semester,1,ECE_1st_Year,1,ECE_4th_Semester,9,Electrical,2,Electrical_1st_Semester,1,Electronics,1,Electronics_&_Communication,14,English,2,Ethical_Hacking,1,ETL_Tools,17,Exam_Cracker,3,Exams,5,Exams_Banking,1,Exams_Prep,1,Excel,22,Excel_Macros,22,Excel_Terms,1,Excel_VBA,22,Free_OS,1,Free_Softwares,1,Games,3,GATE,19,GATE_2019,16,GATE_2020,16,GATE_2021,7,GATE_EC,10,GATE_ECE,9,GATE_ECE_Best_Book,1,GATE_Electrical,1,GATE_Electronics,12,GATE_Made_Easy,1,GATE_ME,6,GATE_Mechanical,6,GGSIPU,1,Handwritten_Notes,10,Hardware,6,HCL_Prep,1,How_To,8,HR_Interview,3,Hyderabad_News,1,IBPS,1,IBPS_English,1,IBPS_PO,2,Informatica,1,Informatica_Interview,1,Information,20,Internet,5,Interview,20,Interview_Prep,20,IPU,1,Java,1,Java_Interview,2,Java_Questions_&_answers,1,JEE,6,JEE_Mains,6,Jobs,1,Keyboard,1,Keyboard_Shortcuts,1,Learn_VBA,22,Linux,33,Linux_Distributions,1,Linux_Interview,1,Linux_Redirections,1,Linux_Scripting,30,Linux_Shell_Arrays,1,Linux_Shell_Functions,1,Linux_Shell_Quote,1,Linux_Signals_And_Traps,1,Logical_Reasoning,1,Machine_Learning,1,Machine_Learning_Interview,1,Macros,22,Manufacturing_Processes,1,ME_1st_Semester,1,ME_Fluid_Mechanics,1,ME_Industrial_Engineering,1,ME_Machine_Design,1,Mechanical,6,Memory,1,Microsoft,1,Microsoft_Azure,1,Microsoft_Azure_Interview,1,Mobile,1,MS_Access,40,MySQL,44,Network,2,News,3,Notes,31,Open_Source_OS,1,Operating_Systems,2,Operating_Systems_Interview,1,Oracle,42,Oracle_Interview,1,Physics,2,PL_SQL,42,PL_SQL_Interview,1,Placement,21,Placement_Prep,24,Poetry,1,Programming,28,Programming_Languages,1,Python,28,Python_Built_In_Strings_Methods,2,Python_built_In_Tuple_Functions,1,Python_CAlling_a_Function,1,Python_CGI,1,Python_Class,1,Python_Data_Types,1,Python_DAte,1,Python_Decision_Making,1,Python_Dictionary,1,Python_DOM_APIs,1,Python_Features,1,Python_Files_Functions,1,Python_For_Loop,1,Python_Functions,6,Python_GUI,1,Python_History,1,Python_If_Else,1,Python_import_Statements,1,Python_Installation,1,Python_Interview,1,Python_JPython,1,Python_Lists,2,Python_Loops,1,Python_Methods,1,Python_Modules,1,Python_MySQL,1,Python_Nested_If_Else,1,Python_Nested_Loops,1,Python_Number_Type_Conversion,1,Python_Numbers,2,Python_Object_Oriented,1,Python_OOP,1,Python_Pass_By_Reference_vs_Value,1,Python_Programming,28,Python_Scripting,28,Python_Special_Operators,1,Python_Strings,2,Python_Strings_Functions,1,Python_Threading_Module,1,Python_Time,1,Python_Tkinter,1,Python_Tuples,2,Python_Tutorial,28,Python_Types_of_Loops,1,Python_Variables,1,Python_Web_Server,1,Python_While_Loop,1,Python_wxPython,1,Python_XML_Processing,1,PythonPath_Setup,1,Quantitative_Aptitude,2,RDBMS,1,Scripting,52,Security,1,Shell_Command_Manual,1,Shell_Logging_Commands,1,Shell_Scripting,31,Shell_Scripting_Interview,1,Software_Engineering_Interview,1,Solutions,1,SQL,45,SQL_Alias_Syntax,1,SQL_Alter_Table_Query,1,SQL_Alter_Table_Statement,1,SQL_AND_OR_Query,1,SQL_AND_OR_Statement,1,SQL_Architecture,1,SQL_Clone_Table,1,SQL_Commands,1,SQL_Conjunctive_Operators,1,SQL_Constraints,1,SQL_Create_Database,1,SQL_Create_Table,1,SQL_DataTypes,1,SQL_Date_Functions,1,SQL_Date_Statement,1,SQL_DCL,1,SQL_Server,39,SQL_Temporary_Table_Statement,1,SQLite,43,Sqoop,9,Sqoop_Tutorial,10,SSC,3,SSC_CGL,3,SSC_CGL_English,1,SSC_CHSL,1,SSC_CPO,1,SSC_GS,1,SSC_Quantative,1,Talend,17,Talend_ETL,15,Talend_Tutorials,16,TCS_Interview,1,TCS_Prep,1,Teradata_Interview,1,Tips_&_Tricks,18,Top_10,1,Top_50,18,Tutorials,151,Tutorials_Python,28,Tutorials_VBA,11,Unix,32,Unix_Interview,1,Unix_Scripting,31,VBA,22,VBA_Basics,22,VBA_Excel,22,VBA_Scripting,22,VBA_Tutorials,22,Visual_Basic_Application,22,Websites,1,Windows,14,
ltr
item
Shout4Education - Get Jobs, Tutorials and Notes: VBA - Text Files
VBA - Text Files
VBA - Text Files - VBA Tutorials ... Best VBA Tutorials only @ Shout4Education aka Shout for Education. ... Learn VBA in an Easy and Simplified Way ... Keep Shouting For Education and Keep Learning ... VBA - Text Files Shout4Education You can also read Excel File and write the contents of the cell into a Text File using VBA. VBA allows the users to work with text files using two methods − File System Object using Write Command File System Object (FSO) As the name suggests, FSOs help the developers to work with drives, folders, and files. In this section, we will discuss how to use a FSO. Sr.No. Object Type & Description 1 Drive Drive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system. 2 Drives Drives is a Collection. It provides a list of the drives attached to the system, either physically or logically. 3 File File is an Object. It contains methods and properties that allow developers to create, delete, or move a file. 4 Files Files is a Collection. It provides a list of all the files contained within a folder. 5 Folder Folder is an Object. It provides methods and properties that allow the developers to create, delete, or move folders. 6 Folders Folders is a Collection. It provides a list of all the folders within a folder. 7 TextStream TextStream is an Object. It enables the developers to read and write text files. Drive Drive is an object, which provides access to the properties of a particular disk drive or network share. Following properties are supported by Drive object − AvailableSpace DriveLetter DriveType FileSystem FreeSpace IsReady Path RootFolder SerialNumber ShareName TotalSize VolumeName Example Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot. Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot shout4education Step 2 − Add "Microsoft Scripting RunTime" and Click OK. Step 2 − Add "Microsoft Scripting RunTime" and Click OK shout for education Step 3 − Add Data that you would like to write in a Text File and add a Command Button. Step 3 − Add Data that you would like to write in a Text File and add a Command Button shout4education Step 4 − Now it is time to Script. Private Sub fn_write_to_text_Click() Dim FilePath As String Dim CellData As String Dim LastCol As Long Dim LastRow As Long Dim fso As FileSystemObject Set fso = New FileSystemObject Dim stream As TextStream LastCol = ActiveSheet.UsedRange.Columns.Count LastRow = ActiveSheet.UsedRange.Rows.Count ' Create a TextStream. Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True) CellData = "" For i = 1 To LastRow For j = 1 To LastCol CellData = Trim(ActiveCell(i, j).Value) stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData Next j Next i stream.Close MsgBox ("Job Done") End Sub Output When executing the script, ensure that you place the cursor in the first cell of the worksheet. The Support.log file is created as shown in the following screenshot under "D:\Try". When executing the script, ensure that you place the cursor in the first cell of the worksheet. The Support.log file is created as shown in the following screenshot under "D:\Try" Shout4Education The Contents of the file are shown in the following screenshot. The Contents of the file are shown in the following screenshot Shout For Education Write Command Unlike FSO, we need NOT add any references, however, we will NOT be able to work with drives, files and folders. We will be able to just add the stream to the text file. Example Private Sub fn_write_to_text_Click() Dim FilePath As String Dim CellData As String Dim LastCol As Long Dim LastRow As Long LastCol = ActiveSheet.UsedRange.Columns.Count LastRow = ActiveSheet.UsedRange.Rows.Count FilePath = "D:\Try\write.txt" Open FilePath For Output As #2 CellData = "" For i = 1 To LastRow For j = 1 To LastCol CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value) Write #2, CellData Next j Next i Close #2 MsgBox ("Job Done") End Sub Output Upon executing the script, the "write.txt" file is created in the location "D:\Try" as shown in the following screenshot. Upon executing the script, the "write.txt" file is created in the location "D:\Try" as shown in the following screenshot Shout 4 Education The contents of the file are shown in the following screenshot. The contents of the file are shown in the following screenshot Shout4Education @ Shout 4 Education , @ Shout For Education
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s640/VBA_Tutorials_Shout4Education.jpg
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s72-c/VBA_Tutorials_Shout4Education.jpg
Shout4Education - Get Jobs, Tutorials and Notes
https://www.shout4education.com/2020/04/vba-text-files.html
https://www.shout4education.com/
https://www.shout4education.com/
https://www.shout4education.com/2020/04/vba-text-files.html
true
7947974353386595563
UTF-8
Loaded All Posts Not Found Any Posts :( View All Read More Reply Cancel Reply Delete By Home Pages Posts View All Similar Posts Label Archive Search All Posts Not Found Any Post Match with Your Request Sorry !! Search Something Blazing :) Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Just Now 1 Minute Ago $$1$$ minutes ago 1 Hour Ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago More than 5 Weeks Ago Followers Follow :) This Premium Content is LOCKED !!! STEP 1: Share. STEP 2: Click the Link You Shared to Unlock Copy All Code Select All Code All Codes were Copied to Your Clipboard :) Can NOT Copy the Codes / Texts, Please Press [CTRL]+[C] (or CMD+C with Mac) to Copy