Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use SUMPRODUCT and avoid rows with #REF! | Excel Programming | |||
How to avoid deleting of rows | Excel Programming | |||
How to avoid date convertions when importing external web data? | Excel Discussion (Misc queries) | |||
How to avoid deleteing certain rows? | Excel Programming | |||
How to Avoid Printing Blank Rows | Excel Programming |