PDA

View Full Version : Excel sheet to textfile



Gest1979
10-10-2015, 10:29 AM
Hi,

I would like to export an sheet in excel into a text file.

This is my code:


Sub text()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
myFile = "D:\test.txt"
Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 3 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If
Next j
Next i
Close #1
End Sub

Problem: I need changes in the code so that

1. no selection is nessesery - just take the open sheet, take all the rows and lines with a content ( if no content just go to the next )
2. start in line 1 row 3
3. never export rows 5 to 6 no matter if empty or not
4. write all in a textfile looking like in excel

THX a lot!

Paul_Hossler
10-10-2015, 05:48 PM
2. start in line 1 row 3

4. write all in a textfile looking like in excel


What does line 1 row 3 mean?

What does looking like in Excel mean?

Gest1979
10-11-2015, 12:30 AM
Sorry "row" was supposed to be column

Looking like excel: lines and columns in textfile the same like in excel.
Empty lines in excel are supposed to be ignored, so no empry lines in textfile.

THX

snb
10-11-2015, 03:54 AM
You can use .Saveas

Gest1979
10-11-2015, 04:14 AM
I need VBA code.


Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
myFile = "C:\test.txt"
Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If
Next j
Next i
Close #1
End Sub

Does anyone knows how to change this code, so that the selection is not needed?

Instead of Selection the code should start in line 1, column 4 go through each cell of line one until the column is empty and then go to the next line and do the sam, all until there is a first cell in a line with value "STOP"

snb
10-11-2015, 05:28 AM
Did you finish http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764574124.html ?

SamT
10-11-2015, 10:31 AM
With code


Delete empty rows
Delete Rows 5 & 6
Delete Column A & B
SaveAs text file.