Print # Statement & Boolean data
Posted by Jose Antonio Gonzalez on March 16, 2001 3:49 AM
I am trying to solve the post "CSV Format and list separator" with a different procedure.
Source data should be a sheet with this data:
Mike 1500
Peter 0
John 123
Martin 6500
Output data should be a text file with ; as separator list and excluding cell with data 0:
Mike;1500
John;123
Martin;6500
I have write the following lines:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/16/2001 by Jose Antonio Gonzalez
'
Open "C:\TMP\TESTFILE.CSV" For Output As #1
Nombre = Range("A1").Select
Año = Range("B1").Select
For N = 1 To 4
If Año <> 0 Then Print #1, Nombre; ";"; Año
Nombre = ActiveCell.Offset(1, -1).Select
Año = ActiveCell.Offset(0, 1).Select
Next N
Close #1
Range("A1").Select
'
End Sub
However output file is:
True;True
True;True
....
True;True
How can I avoid the Boolean result using a IF for avoiding export cells with 0 values?
Thanks again for your help
--
Jose Antonio Gonzalez
Controller
Hotel Maria Cristina RES 086 - FRS 347 - SAP F220
Posted by Roger on March 18, 2001 10:11 AM
Macro1 Macro Macro recorded 3/16/2001 by Jose Antonio Gonzalez
Jose,
The reason you're getting True rather than your data is because you are using the incorrect property of Range("A1"). You need to use Range("A1").Value not Range("A1").Select in order to return a cell's value. This slightly altered code will write the text file you're after.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/16/2001 by Jose Antonio Gonzalez
'
Open "C:\TMP\TESTFILE.TXT" For Output As #1
Nombre = Range("A1").Value
Año = Range("B1").Value
Range("a1").Select
For n = 1 To 4
If Año <> 0 Then Print #1, Nombre; ";"; Año
Nombre = ActiveCell.Offset(n, 0).Value
Año = ActiveCell.Offset(n, 1).Value
Next n
Close #1
Range("A1").Select
'
End Sub
Also, you should give the file a TXT extension rather than CSV as Excel will assume that the file is a comma seperated text file and will open it accordingly.
Hope this helps,
Roger.
