I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP

I took some old code I had and made some quick changges. Don't have time to
fully test but it looks like it will work. Change the filename and path as
necessary

Sub WriteCSV()

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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
If Range("D" & RowCount) Date Then
For ColCount = 1 To 12
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub


"Joe K." wrote:


I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP

When I executed the script listed below the file is created but is empty.

Please help me resolve this issue.

Thanks,



"Joel" wrote:


I took some old code I had and made some quick changges. Don't have time to
fully test but it looks like it will work. Change the filename and path as
necessary

Sub WriteCSV()

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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
If Range("D" & RowCount) Date Then
For ColCount = 1 To 12
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub


"Joe K." wrote:


I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP

I suspect the date in your worksheet may be a string and not a serial date
(excel name for a formated date cell)

try this change
from
If Range("D" & RowCount) Date Then
to
If datevalue(Range("D" & RowCount)) Date Then


If this doesn't work you need to set break points to determine how far the
code is getting.

You can click any line with the mouse and then press F9 to set break point
Use F8 to step through code.

The code is very simple. The datte is probably the reason why you are not
getting any ouput.

"Joe K." wrote:


When I executed the script listed below the file is created but is empty.

Please help me resolve this issue.

Thanks,



"Joel" wrote:


I took some old code I had and made some quick changges. Don't have time to
fully test but it looks like it will work. Change the filename and path as
necessary

Sub WriteCSV()

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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
If Range("D" & RowCount) Date Then
For ColCount = 1 To 12
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub


"Joe K." wrote:


I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP

Please help me a add the column names (AT to LT) to the first record to the
output file. The column names will not be read from the input spreadsheet.

Thanks so much for the wonderful help.



"Joel" wrote:

I suspect the date in your worksheet may be a string and not a serial date
(excel name for a formated date cell)

try this change
from
If Range("D" & RowCount) Date Then
to
If datevalue(Range("D" & RowCount)) Date Then


If this doesn't work you need to set break points to determine how far the
code is getting.

You can click any line with the mouse and then press F9 to set break point
Use F8 to step through code.

The code is very simple. The datte is probably the reason why you are not
getting any ouput.

"Joe K." wrote:


When I executed the script listed below the file is created but is empty.

Please help me resolve this issue.

Thanks,



"Joel" wrote:


I took some old code I had and made some quick changges. Don't have time to
fully test but it looks like it will work. Change the filename and path as
necessary

Sub WriteCSV()

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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
If Range("D" & RowCount) Date Then
For ColCount = 1 To 12
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub


"Joe K." wrote:


I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP

chane the following
from
If Range("D" & RowCount) Date Then
to
If Range("D" & RowCount) Date or _
Rowcount = 1 Then

"Joe K." wrote:

Please help me a add the column names (AT to LT) to the first record to the
output file. The column names will not be read from the input spreadsheet.

Thanks so much for the wonderful help.



"Joel" wrote:

I suspect the date in your worksheet may be a string and not a serial date
(excel name for a formated date cell)

try this change
from
If Range("D" & RowCount) Date Then
to
If datevalue(Range("D" & RowCount)) Date Then


If this doesn't work you need to set break points to determine how far the
code is getting.

You can click any line with the mouse and then press F9 to set break point
Use F8 to step through code.

The code is very simple. The datte is probably the reason why you are not
getting any ouput.

"Joe K." wrote:


When I executed the script listed below the file is created but is empty.

Please help me resolve this issue.

Thanks,



"Joel" wrote:


I took some old code I had and made some quick changges. Don't have time to
fully test but it looks like it will work. Change the filename and path as
necessary

Sub WriteCSV()

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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
If Range("D" & RowCount) Date Then
For ColCount = 1 To 12
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub


"Joe K." wrote:


I have a spreadsheet with a worksheet that I would like to create a CSV file
from the worksheet. I would like to copy all columns data from A to L to the
CSV file, only the records where date (Column D) greater than today, and not
include hidden columns H and I. The first record in the worksheet starts on
A10 and the first row of the CSV file should include the column names.

Please help me create a script to complete this task.

Example

Worksheet
Columns Names (Hidden)
AT BT CT DT ET FT GT HT IT JT
KT LT
20 ABV VB 12/20/07 CV GH 1221 34 45 HJ
GF AS
60 AYT VB 11/22/07 CV GH 1111 45 66 HJ
GF KY
70 FTY BT 12/22/07 GB HY 1233 44 88 JK
YY OP
80 FFY BT 12/22/07 GB BB 1233 44 88 JK
YY OP


Desired Output F:\VBA\NMexico.CSV file

AT BT CT DT ET FT GT JT KT
LT
20 ABV VB 12/20/07 CV GH 1221 HJ GF AS
70 FTY BT 12/22/07 GB HY 1233 JK YY OP
80 FFY BT 12/22/07 GB BB 1233 JK YY OP