Avoid importing 65,536 rows
 

Hi

I have created a csv file to import into an external program, and regardless
of how many rows are populated, the other program attempts to import ALL
65,536 rows in the csv file. Is there something I can do, within Excel,
preferably programatically, to ensure that only rows with data are presented
for import.

tia
James

Avoid importing 65,536 rows
 

The code below will write a CSV file and will not output anything for a blank
line

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
OutputLine = Trim(OutputLine)
If Len(OutputLine) < 0 Then
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub

"access user" wrote:

Hi

I have created a csv file to import into an external program, and regardless
of how many rows are populated, the other program attempts to import ALL
65,536 rows in the csv file. Is there something I can do, within Excel,
preferably programatically, to ensure that only rows with data are presented
for import.

tia
James

Avoid importing 65,536 rows
 

Wow - I'll give that a go - let you know how I get on.
thanks
James

"Joel" wrote:

The code below will write a CSV file and will not output anything for a blank
line

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
OutputLine = Trim(OutputLine)
If Len(OutputLine) < 0 Then
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub

"access user" wrote:

Hi

I have created a csv file to import into an external program, and regardless
of how many rows are populated, the other program attempts to import ALL
65,536 rows in the csv file. Is there something I can do, within Excel,
preferably programatically, to ensure that only rows with data are presented
for import.

tia
James

Avoid importing 65,536 rows
 

Hi Joel

I get an error

'argument not optional' on line beginning LastRow =

Also, one thing I didn't mention, the worksheet from which the csv file is
to be created is within a workbook, so do we also need to use the With
statement to tell it which worksheet?

tia
James

"Joel" wrote:

The code below will write a CSV file and will not output anything for a blank
line

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
OutputLine = Trim(OutputLine)
If Len(OutputLine) < 0 Then
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub

"access user" wrote:

Hi

I have created a csv file to import into an external program, and regardless
of how many rows are populated, the other program attempts to import ALL
65,536 rows in the csv file. Is there something I can do, within Excel,
preferably programatically, to ensure that only rows with data are presented
for import.

tia
James