Data transfer from Excel file to text file

[deepakgoyal2005]

I have an excel sheet with data like,

<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#000000 cellPadding=2 width=380><TBODY><TR><TD bgColor=#c0c0c0 height=19 width="8%">

​

</TD><TD bgColor=#c0c0c0 height=19 width="57%">A

​

</TD><TD bgColor=#c0c0c0 height=19 width="18%">B

​

</TD><TD bgColor=#c0c0c0 height=19 width="18%">C

​

</TD></TR><TR><TD bgColor=#c0c0c0 height=19 width="8%">1

​

</TD><TD height=19 width="57%">Deepak Goyal 123 345

​

</TD><TD height=19 width="18%">123

​

</TD><TD height=19 width="18%">abcd

​

</TD></TR><TR><TD bgColor=#c0c0c0 height=19 width="8%">2

​

</TD><TD height=19 width="57%">ABC

​

</TD><TD height=19 width="18%">456

​

</TD><TD height=19 width="18%">abcd

​

</TD></TR><TR><TD bgColor=#c0c0c0 height=19 width="8%">3

​

</TD><TD height=19 width="57%">Deepak

​

</TD><TD height=19 width="18%">789

​

</TD><TD height=19 width="18%">abcd

</TD></TR></TBODY></TABLE>

I want to transfer the data into a .txt file.
To do this, I save the excel file as Tab dilimited .txt file so as to maintain the alignment of data under same coulmn one below the other. But still the alignment is disturbed even after a tab is inserted between data fields.

Actual Output received:

Deepak Goyal 123 345 123 abcd
ABC 456 abcd
Deepak 789 abcd

Required Output:

Deepak Goyal 123 345 123 abcd
ABC                  456 abcd
Deepak               789 abcd

Please advice.

 

[Joe4]

Making it tab-delimited will not make it look all lined up in viewing it in the text file - all that does is put a tab between each field. If you are importing it into a program which accepts tab-delimited files, that will work nicely.

But if you want everything to be lined up when viewing it in a text file, you do not want this option. You want the "Formatted Text (Space Delimited) (*.prn)" option under "Save as type". Even though "*.prn" is the default file extension, you can save it with a ".txt" extension instead.

 

[deepakgoyal2005]

Hi Joe,

Thanks for the help.
It works as required. But in this case, I noticed strange results. My input excel file had 214 rows. After saving it into .prn file, in the first half of final output, all the records were truncated after 214 characters. then in its next half the truncated data was present.

Like,

Expected output was,
RECORD1 11111111111
RECORD2 22222222222
RECORD3 33333333333

Actual output was,
RECORD1 11111111
RECORD2 22222222
RECORD3 33333333
111
222
333

Is it some limitation to .prn file?

 

[deepakgoyal2005]

I noticed that it works fine when the excel sheet to be transfered has its 'Page Setup' changes to 'Fit to' 1 (wide) by 1 (tall).

Then I tried converting this transfer into a macro as below,

Sub SavePrn()
Dim NewFileName As String
Dim WorkingFile As String
 
Application.ScreenUpdating = False
WorkingFile = ActiveWorkbook.Name
With ActiveSheet.PageSetup
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = 1
End With
NewFileName = "C:\Deepak\Tools" & "\" & Sheets(1).Name
Sheets(1).Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs
ActiveWorkbook.SaveAs Filename:=NewFileName & ".prn", FileFormat _
   :=xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(WorkingFile).Activate
Application.ScreenUpdating = True
End Sub

But the "ActiveSheet.PageSetup" doesn't seems to be working. As, the output is still truncated when this macro is used

 

[Joe4]

Exactly how wide is your data?

 

[deepakgoyal2005]

I have about 12 columns and with variable widths.
I tried by reducing the visible widths of the cloumns and it worked well.
I doubt if other team members would always take care of this limitation.

So, I tried coding for columns to be changed to compatible widths before transfer of data as,

Sub SavePrn()
Dim NewFileName As String
Dim WorkingFile As String
 
Application.ScreenUpdating = False
WorkingFile = ActiveWorkbook.Name
With ActiveSheet.PageSetup
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = 1
End With
Columns("D:O").ColumnWidth = 13.5
NewFileName = "C:\Deepak\Tools\Datasheet"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs
ActiveWorkbook.SaveAs Filename:=NewFileName & ".prn", FileFormat _
   :=xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(WorkingFile).Activate
Application.ScreenUpdating = True
End Sub

But the results were still truncated.
The required data is not truncated only if we adjust column width manually and not through VBA code

 

[Joe4]

Yes, exporting to a space delimited file (prn) using that method is limited to 240 characters. However, there is some VBA code you can use to get around that. See here: http://support.microsoft.com/kb/249885