VBA Code

S

santaviga

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
 
B

Bernie Deitrick

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
 
S

santaviga

hi Bernie,

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

Any ideas what i'm doing wrong

Thanks
 
B

Bernie Deitrick

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
 
S

santaviga

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
 
B

Bernie Deitrick

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 said:
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 said:
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
Click to expand...
Click to expand...
 
Top