PDA

View Full Version : [SOLVED] Preventing VBA converting cell value to date when printing as .txt



jebarri
04-13-2015, 05:08 PM
I am trying to get VBA to print a sheet to a .txt file. I should not that saving as a .txt file created "" around each cell value. The output file is used directly by a spotting robot so the "" cannot be present.
I have 3 columns and multiple (over 5000) rows with some blank. The first row contains array placements (e.g. 1/1, 1/2, 1/3....2/18...all the way to 18/36).


When the macro prints to the .txt file, any array coordinate which can be read as a date is printed as such.


For example, the first lines in the .txt file are
01/01/2015 *probe*, *droplets*,
01/02/2015 *probe*, *droplets*,
01/03/2015 *probe*, *droplets*,
01/04/2015 *probe*, *droplets*,
01/05/2015 *probe*, *droplets*,
01/06/2015
01/07/2015 *probe*, *droplets*,
01/08/2015 *probe*, *droplets*,


How do I request the value of the cell not be converted into a date. I have tried adding in '1/1, etc. but that does not solve the issue.


Thanks so much.


Example of repeated code for printing:
With Sheet1
sLine = Format(Cells(lRow, 1)) & vbTab
sLine = sLine & .Cells(lRow, 2) & vbTab
sLine = sLine & .Cells(lRow, 3) & vbTab

Paul_Hossler
04-13-2015, 05:30 PM
As a GUESS, try




sLine = .Cells(lRow, 1).Text & vbTab .Cells(lRow, 2).Text & vbTab & .Cells(lRow, 3).Text



without having a small sample of real data, it's hard to tell exactly

jebarri
04-14-2015, 12:32 PM
Wow. That was simple. Thank you so much. :hifive:

Cheers.
Josh.

Paul_Hossler
04-15-2015, 06:46 AM
NP

Only thing to be aware of is that .Text returns the cell's presentation EXACTLY as show. So it the column width is too small, and the worksheet displays ######, that's what will come back in .Text

jebarri
04-15-2015, 10:36 AM
Thanks for the note.

Sadly I ran into a bit of snag when testing the text file with the spotting robot. Apparently tabs generated by the breaks between arrays is an issue.
For example, in .xls the generated data to be converted to a text file looks like this with arrays 0-7:



Field Data:




[0, 0, 0]




1/1
1A10,1D10,
1,1,


1/2
1A9,1D10,
1,1,


1/3
1A8,1D10,
1,1,


1/4
1A7,1D10,
1,1,


1/5
1A6,1D10,
1,1,


1/6




1/7




1/8
1A5,1D10,
1,1,


1/9
1A4,1D10,
1,1,


1/10
1A3,1D10,
1,1,


1/11




1/12
1B10,1D10,
1,1,


1/13




1/14




1/15
1B9,1D10,
1,1,


1/16




1/17




1/18
1B8,1D10,
1,1,


1/19




1/20




1/21




1/22




...


18/35




18/36









[1, 0, 0]




1/1




1/2




1/3




1/4




...

The empty line between each array field has tabs present with my current VBA code.

Dim sLine As String
Dim sFName As String
Dim iFNumber As Integer
Dim lRow As Long

sFName = "C:" & "\" & Sheets("Cell Count Array").Range("T1") & "_Wafergen\" & Sheets("Cell Count Array").Range("T1") & "_Lysis.txt"
iFNumber = FreeFile
Open sFName For Output As #iFNumber
lRow = 1
Do
With Sheets("Lysis")
sLine = .Cells(lRow, 1).Text & vbTab & .Cells(lRow, 2).Text & vbTab & .Cells(lRow, 3).Text & vbTab
End With
Print #iFNumber, sLine
lRow = lRow + 1
Loop Until lRow = 5222
Close #iFNumber

I have tried simply
Sheets("Lysis").SaveAs "C:" & "\" & Sheets("Cell Count Array").Range("T1") & "_Wafergen\" & Sheets("Cell Count Array").Range("T1") & "_Lysis.txt", FileFormat:=xlTextWindows

but that generated not only the same filling in empty lines with tabs, but also quotations only around array field names.

Field Data:
"[0, 0, 0]"
1/1



Thanks again for your time.
Cheers.
Josh.

Paul_Hossler
04-15-2015, 01:16 PM
Could you just not write an 'empty' row or a blank line?




If Len(sLine) > 3 The Print #iFNumber, sLine


or



If Len(sLine) > 3 The Print #iFNumber, ""