VBA Code
 

Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in
columns, is there anywhere I need to put in so if there is only 5 characters
it will pad the text out to 12 and therefore keep the text file as if it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data in
cells throughout the worksheet, what I need is a macro to export the data in
all cells to a text file on the desktop, but these need to be a maximum of 12
characters and if not 12 character it needs to be padded out to 12 characters
so that when it exports to a text file the data is displayed as if in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards

VBA Code
 

You could use a macro like this below, where your set the width for each column of your output using
the values in the array. The example is for four columns...

HTH,
Bernie
MS Excel MVP

Sub ExportToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths(1 To 4) As Integer

Widths(1) = 8
Widths(2) = 9
Widths(3) = 10
Widths(4) = 10

fName = "C:\Excel\Export.txt"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths(ColNdx)), Widths(ColNdx))
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

"santaviga" wrote in message
...
Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in
columns, is there anywhere I need to put in so if there is only 5 characters
it will pad the text out to 12 and therefore keep the text file as if it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data in
cells throughout the worksheet, what I need is a macro to export the data in
all cells to a text file on the desktop, but these need to be a maximum of 12
characters and if not 12 character it needs to be padded out to 12 characters
so that when it exports to a text file the data is displayed as if in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards

VBA Code
 

hi Bernie,

this macro is not exporting any text into the text file.

Any ideas what i'm doing wrong

Thanks

"Bernie Deitrick" wrote:

You could use a macro like this below, where your set the width for each column of your output using
the values in the array. The example is for four columns...

HTH,
Bernie
MS Excel MVP

Sub ExportToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths(1 To 4) As Integer

Widths(1) = 8
Widths(2) = 9
Widths(3) = 10
Widths(4) = 10

fName = "C:\Excel\Export.txt"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths(ColNdx)), Widths(ColNdx))
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

"santaviga" wrote in message
...
Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in
columns, is there anywhere I need to put in so if there is only 5 characters
it will pad the text out to 12 and therefore keep the text file as if it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data in
cells throughout the worksheet, what I need is a macro to export the data in
all cells to a text file on the desktop, but these need to be a maximum of 12
characters and if not 12 character it needs to be padded out to 12 characters
so that when it exports to a text file the data is displayed as if in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards

VBA Code
 

As written, the macro assumes that there is a contiguous block of cells (no
fully empty rows or columns) starting in cell A1 that is four columns wide -
so A, B, C, D starting in row 1. Also, the text cannot be padded with spaces
at the start of the cell with more spaces than the number given in the
widths array - for example, if Widths(1) is 10, and cell A1 is (without the
quotes) " lots of leading spaces" then only ten spaces
will be output.

If you can, post a sample of your data table, with cell A1 as the upper left
cell.

HTH,
Bernie
MS Excel MVP


"santaviga" wrote in message
...
hi Bernie,

this macro is not exporting any text into the text file.

Any ideas what i'm doing wrong

Thanks

"Bernie Deitrick" wrote:

You could use a macro like this below, where your set the width for each
column of your output using
the values in the array. The example is for four columns...

HTH,
Bernie
MS Excel MVP

Sub ExportToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths(1 To 4) As Integer

Widths(1) = 8
Widths(2) = 9
Widths(3) = 10
Widths(4) = 10

fName = "C:\Excel\Export.txt"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths(ColNdx)),
Widths(ColNdx))
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

"santaviga" wrote in message
...
Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and
not in
columns, is there anywhere I need to put in so if there is only 5
characters
it will pad the text out to 12 and therefore keep the text file as if
it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data
in
cells throughout the worksheet, what I need is a macro to export the
data in
all cells to a text file on the desktop, but these need to be a
maximum of 12
characters and if not 12 character it needs to be padded out to 12
characters
so that when it exports to a text file the data is displayed as if
in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file
will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be
better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards

VBA Code
 

Hi,

This is the data.

Field 1 Field 2 Field 3 Field 4 and so on possibly to the end
Title Title Title Title Title

Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name (these could be more than 12 character but need to
cut the export to 12 characters and if the cell doesn't have 12 characters it
needs to be padded out with spaces at the end so it can exprt
properly.e.g.[Name ]

When it exports to a text file it needs to be displayed the same as above as
if in columns so it can be read.
What I am getting is like this

Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name
NameNameNameName

Hope this is helpful.

Regards

Mark

"santaviga" wrote:

Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in
columns, is there anywhere I need to put in so if there is only 5 characters
it will pad the text out to 12 and therefore keep the text file as if it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data in
cells throughout the worksheet, what I need is a macro to export the data in
all cells to a text file on the desktop, but these need to be a maximum of 12
characters and if not 12 character it needs to be padded out to 12 characters
so that when it exports to a text file the data is displayed as if in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards

VBA Code
 

Try this version.

HTH,
Bernie
MS Excel MVP


Sub ExportToPRN12()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths As Integer

Widths = 12

fName = "C:\Excel\Export.txt" 'Change this

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With ActiveSheet.UsedRange 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths), Widths)
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub


"santaviga" wrote in message
...
Hi,

This is the data.

Field 1 Field 2 Field 3 Field 4 and so on possibly to the end
Title Title Title Title Title

Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name (these could be more than 12 character but need to
cut the export to 12 characters and if the cell doesn't have 12 characters it
needs to be padded out with spaces at the end so it can exprt
properly.e.g.[Name ]

When it exports to a text file it needs to be displayed the same as above as
if in columns so it can be read.
What I am getting is like this

Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name
NameNameNameName

Hope this is helpful.

Regards

Mark

"santaviga" wrote:

Hi to all

Anyone have any idea on some help for this macro

I have input the following code and it works to an extent, when it runs
the macro to export the data to text file, the text is all joined and not in
columns, is there anywhere I need to put in so if there is only 5 characters
it will pad the text out to 12 and therefore keep the text file as if it were
in columns?

Many thanks



Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

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
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
End If
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


"santaviga" wrote:

Hi to all.

I need some help with exporting excel data to text file, I have data in
cells throughout the worksheet, what I need is a macro to export the data in
all cells to a text file on the desktop, but these need to be a maximum of 12
characters and if not 12 character it needs to be padded out to 12 characters
so that when it exports to a text file the data is displayed as if in colums
and not all over the place.

Also I need to know how it exports e.g every time I save the file will it
export to a text file on desktop?

I am currenty doing this a long way by formulas but I think would be better
if it was done by a macro.

I thankyou in advance

Any help would be much appreciated I am new to macros

Regards