vba transforms excel forms into txts
Here are a few ways to convert an Excel table into a txt-file using VBA:
Method 1:
1. He opened the Excel file to be converted, pressed Alt + F11, and opened the macro editor.
2. Create a new VBA module (right-click on vba project and select insert-> module from the drop-down menu).
3. Add the following code to the module:
```vba
Option Explicit
Public Sub ConvertExcelToTxt1()
ActivWorkbook.SaveAs "c: Student Grade.txt', XlFileForm. xldsv
End Sub
```
4. Run the VBA code to generate a txt file. You can place the pointer in the code, press F8 to start single-step tuning, press F8 continuously to run it, or press F5 to run it all at once.
Method 2:
1. Similarly, open the Excel macro editor (Alt + F11) and create a new VBA module.
2. Add the following code to the module:
```vba
Public Sub ConvertExcelToTxt2()
Dim lngRow As Long
Dim lngCol As Long
Dim strContent As String
Dim objSheet As Worksheet
Set objSheet = ActiveWorkbook.Sheets(1)
Open "c: Student Grade.txt." For Production As #1
For lngRow = 1 To 4
For lngCol = 1 To 4
strContent = strContent & objSheet.Cells(lngRow, lngCol)
If lngCol < 4 Then
strContent = strContent & ","
End If
Next
strContent = strContent & vbCrLf
Next
Print #1, strContent
Close #1
End Sub
```
3. Run the code to achieve the conversion.
Method 3:
1. Create a FilesystemObject object.
2. specify the file path and name.
3. Create a new text file (set to Overwrite if the file exists).
4. Confirm the scope of use of the Excel sheet (such as determining the first row, the last row, the first column, and the last column).
5. Loop through each row of the current sheet, separating and connecting the cells in the row area with specific characters (such as commas).
6. Write each line of data into a text file.
7. Close the file and release the resource. The sample code is as follows:
```vba
Sub export Excel to text file ()
'Create the FilesystemObject object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
'specify the file path and name
Dim filePath As String
filePath = "C:\Users\VBAMatrix\Desktop\newfile.txt"
'Create a new text file. The parameters are True, which means that the file will be erased if it exists.
Dim file As Object
Set file = fso.CreateTextFile(filePath, True)
Dim firstRow As Integer, lastRow As Integer
firstRow = ActiveSheet.UsedRange.Row
lastRow = firstRow + ActiveSheet.UsedRange.Rows.Count - 1
Dim firstColumn As Integer, lastColumn As Integer
firstColumn = ActiveSheet.UsedRange.Column
lastColumn = firstColumn + ActiveSheet.UsedRange.Columns.Count - 1
Dim i As Integer, j As Integer
Dim txtwd As String
'Loop through each row of the current sheet
For i = firstRow To lastRow
txtwd = ""
For j = firstColumn To lastColumn
'Rows are separated by commas
txtwd = txtwd & ActiveSheet.Cells(i, j).Value & ","
Next
'Write the connected line data into a text file.
file.WriteLine (Left(txtwd, Len(txtwd) - 1))
Next
'Close the file
file.Close
'Release resources
Set file = Nothing
Set fso = Nothing
End Sub
```
There was another way to save the path through the pop-up window:
1. He opened the exel file, pressed alt+F11, clicked this textbook, and inserted the module.
2. Clone the following code:
```vba
Sub export data () 'export data
Dim wjm As String
Dim wjh As Integer
Dim hh As Long
Dim I As Long
Dim lh As Integer
Dim j As Integer
Dim txtwd As String
'The pop-up window chooses the save path and indicates the save type.
wjm =