Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

Dear Dave,

It works in Lotus but not here. I suspect that the macro recorder records
the results of the R2C wizard's evaluation of the data rather than the
keystrokes required to progress through the wizard. The recorded VBA
programs look like specific arrays that are data specific. When the recorded
macro is run against another set of different data the results are not
accurate. The recorder just does not record the keystrokes with Wizards.
My posted keystroke sequence works fine. It just gets tiring after doing it
for the 4 or 500th time in a row.
BTW I can record a macro for each different text string length and run a
macro for each row. This still means I have to key in something for each row.
The idea is to write a VBA program in a relative loop that duplicates the
keystrokes noted.

Pa

"Dave Peterson" wrote:

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson

I don't understand.

If you're depending on excel to guess where the field breaks should be, then it
sure sounds like the data could be parsed by using space as the delimiter.

But maybe I'm missing something.

Parusky wrote:

Dear Dave,

It works in Lotus but not here. I suspect that the macro recorder records
the results of the R2C wizard's evaluation of the data rather than the
keystrokes required to progress through the wizard. The recorded VBA
programs look like specific arrays that are data specific. When the recorded
macro is run against another set of different data the results are not
accurate. The recorder just does not record the keystrokes with Wizards.
My posted keystroke sequence works fine. It just gets tiring after doing it
for the 4 or 500th time in a row.
BTW I can record a macro for each different text string length and run a
macro for each row. This still means I have to key in something for each row.
The idea is to write a VBA program in a relative loop that duplicates the
keystrokes noted.

Pa

"Dave Peterson" wrote:

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson


--

Dave Peterson

Dear Dave,
The delimiter is sort of moot.

The data string length of the text is different for each row.
Never-the-less, the R2Column function parses it fine using fixed width. The
problem is that the Macro recorder cannot record a function where it assigns
a value to a function.

For example, the following is an example of unparsed data with each segment
equal to one cell:
7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 1096

870 855 735 1050 455 455 420 775 60 0 35 420

116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
The recorded macro to parse these three cells into 12 columns of data
correctly which was generated from the original posting commands is as
follows:

Selection.TextToColumns Destination:=Range("D8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(14,
1), Array(19, 1), _
Array(24, 1), Array(29, 1), Array(34, 1), Array(39, 1), Array(45,
1), Array(50, 1), Array( _
55, 1))
Range("D9").Select
Selection.TextToColumns Destination:=Range("D9"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1), Array(11,
1), Array(16, 1), _
Array(20, 1), Array(24, 1), Array(28, 1), Array(32, 1), Array(35,
1), Array(37, 1), Array( _
40, 1))
Range("D10").Select
Selection.TextToColumns Destination:=Range("D10"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(12, 1), Array(19,
1), Array(26, 1), _
Array(33, 1), Array(39, 1), Array(46, 1), Array(53, 1), Array(60,
1), Array(67, 1), Array( _
74, 1))
End Sub

Note that the cells are not relative when in reality it would be. Each line
has a different array solution based on the data. If I were to run this
macro on another set of data with a different length it does not and cannot
work.

All I wish to do is start on the first cell and duplicate the key strokes of :

ALT D,e
Enter
Enter
Enter

For each cell

Thanks for considering the problem.

Cordially,
PA



"Dave Peterson" wrote:

I don't understand.

If you're depending on excel to guess where the field breaks should be, then it
sure sounds like the data could be parsed by using space as the delimiter.

But maybe I'm missing something.

Parusky wrote:

Dear Dave,

It works in Lotus but not here. I suspect that the macro recorder records
the results of the R2C wizard's evaluation of the data rather than the
keystrokes required to progress through the wizard. The recorded VBA
programs look like specific arrays that are data specific. When the recorded
macro is run against another set of different data the results are not
accurate. The recorder just does not record the keystrokes with Wizards.
My posted keystroke sequence works fine. It just gets tiring after doing it
for the 4 or 500th time in a row.
BTW I can record a macro for each different text string length and run a
macro for each row. This still means I have to key in something for each row.
The idea is to write a VBA program in a relative loop that duplicates the
keystrokes noted.

Pa

"Dave Peterson" wrote:

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson


--

Dave Peterson

First, Did you try Data|Text to columns using delimited by space (treating
consecutive delimiters as one)?

If no, then try it. You may be pleasantly surprised.

And you wouldn't have to do each cell one by one, either.

=========
But...

I put this in A1:
7770-6255-------7340-7250-------7725-8578-------8511-9837-------
11185-9250-9775-1096

(Where each hyphen represents a space character--and there was no line wrap
within A1)

I manually ran data|text to columns|fixed width|just clicked next.

Each number was put into its own cell:
A1:L1 (Transposed for posting)
7770
6255
7340
7250
7725
8578
8511
9837
11185
9250
9775
1096

Is each of these numbers a "segment". I'm not sure what that means.

Do you get different results?

========
If you want to fiddle with just mimicking keystrokes, you may want to look at
sendkeys in VBA's help. I wouldn't use this. I wouldn't recommend others use
it either.


Parusky wrote:

Dear Dave,
The delimiter is sort of moot.

The data string length of the text is different for each row.
Never-the-less, the R2Column function parses it fine using fixed width. The
problem is that the Macro recorder cannot record a function where it assigns
a value to a function.

For example, the following is an example of unparsed data with each segment
equal to one cell:
7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 1096

870 855 735 1050 455 455 420 775 60 0 35 420

116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
The recorded macro to parse these three cells into 12 columns of data
correctly which was generated from the original posting commands is as
follows:

Selection.TextToColumns Destination:=Range("D8"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(14,
1), Array(19, 1), _
Array(24, 1), Array(29, 1), Array(34, 1), Array(39, 1), Array(45,
1), Array(50, 1), Array( _
55, 1))
Range("D9").Select
Selection.TextToColumns Destination:=Range("D9"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1), Array(11,
1), Array(16, 1), _
Array(20, 1), Array(24, 1), Array(28, 1), Array(32, 1), Array(35,
1), Array(37, 1), Array( _
40, 1))
Range("D10").Select
Selection.TextToColumns Destination:=Range("D10"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(12, 1), Array(19,
1), Array(26, 1), _
Array(33, 1), Array(39, 1), Array(46, 1), Array(53, 1), Array(60,
1), Array(67, 1), Array( _
74, 1))
End Sub

Note that the cells are not relative when in reality it would be. Each line
has a different array solution based on the data. If I were to run this
macro on another set of data with a different length it does not and cannot
work.

All I wish to do is start on the first cell and duplicate the key strokes of :

ALT D,e
Enter
Enter
Enter

For each cell

Thanks for considering the problem.

Cordially,
PA

"Dave Peterson" wrote:

I don't understand.

If you're depending on excel to guess where the field breaks should be, then it
sure sounds like the data could be parsed by using space as the delimiter.

But maybe I'm missing something.

Parusky wrote:

Dear Dave,

It works in Lotus but not here. I suspect that the macro recorder records
the results of the R2C wizard's evaluation of the data rather than the
keystrokes required to progress through the wizard. The recorded VBA
programs look like specific arrays that are data specific. When the recorded
macro is run against another set of different data the results are not
accurate. The recorder just does not record the keystrokes with Wizards.
My posted keystroke sequence works fine. It just gets tiring after doing it
for the 4 or 500th time in a row.
BTW I can record a macro for each different text string length and run a
macro for each row. This still means I have to key in something for each row.
The idea is to write a VBA program in a relative loop that duplicates the
keystrokes noted.

Pa

"Dave Peterson" wrote:

Why won't the macro recorder work?

It usually works ok for me when I record data|text to columns.

The only time I've seen any problem with an array is when I have lots and lots
of fields and get an "out of memory" error when I try to rerun the recorded
macro.

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



Parusky wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If Dave's advise has not worked, please explain why you think you have
"fixed width data", when in other posts you appear to have variable length,
space separated data.

NickHK

"Parusky" wrote in message
...
Please help. VBA Ignorant. I am trying to build a macro to convert a lot
of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple
keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple
repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

I ain't giving up on this either <bg.

NickHK wrote:

If Dave's advise has not worked, please explain why you think you have
"fixed width data", when in other posts you appear to have variable length,
space separated data.

NickHK

"Parusky" wrote in message
...
Please help. VBA Ignorant. I am trying to build a macro to convert a lot
of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple
keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple
repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson

Dave,
I think you are on the right track.

NickHK

"Dave Peterson" wrote in message
...
I ain't giving up on this either <bg.

NickHK wrote:

If Dave's advise has not worked, please explain why you think you have
"fixed width data", when in other posts you appear to have variable
length,
space separated data.

NickHK

"Parusky" wrote in message
...
Please help. VBA Ignorant. I am trying to build a macro to convert a
lot
of
fixed width data from text to columns sequentially for multiple rows.
The
macro recorder will not work due its nature with arrays. The simple
keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple
repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"



Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub


"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson

Gentlemen,

Dave P's last suggestion works as a macro on the live data. I think I know
why. When a fixed width gets stored in the macro it is raw data dimensionally
set when the data length varies the result varies. Thank you Dave P for
your kind and patient efforts. Your solution works for the repetitive macro
on this data.

Joel's program was also tested out for some smaller PDF files where I need
all of the data. It worked perfectly for extracting the info into a CSV file
nice and neat. I will retain that code for many other applications. This
application is an extract out of a massive PDF and doesn't justify its use
but it will not go to waste.

Thanks for your useful comments Nick

Now all I have to do is figure out how to convert it to a looped manual
macro with a empty cell delimited if statement and I am in business. Must
have bought the wrong book or am getting to old to learn this protocol.
Started out board wiring on a IBM 401 and am tired of learning new languages.
C'est la Vie!

Thanks to all again
Parusky




"NickHK" wrote:

Dave,
I think you are on the right track.

NickHK

"Dave Peterson" wrote in message
...
I ain't giving up on this either <bg.

NickHK wrote:

If Dave's advise has not worked, please explain why you think you have
"fixed width data", when in other posts you appear to have variable
length,
space separated data.

NickHK

"Parusky" wrote in message
...
Please help. VBA Ignorant. I am trying to build a macro to convert a
lot
of
fixed width data from text to columns sequentially for multiple rows.
The
macro recorder will not work due its nature with arrays. The simple
keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple
repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson

The suggestion I made didn't rely on any fixed width fields. It used the fact
that the data was delimited (by spaces).

And if this worked for you for a single cell, it'll work for a range (a range
with just one column), too.

And the real good news is that you don't have too loop through each row. You
just have to make sure you apply data|text to columns to all the cells you want.

Try it manually first (recording a macro if you want).

Select A1:A99 (or whatever)
do the data|text to columns
delimited by space

and see if it works for you.

====
And you must have gotten pretty lucky with the .pdf file. Every .pdf file I've
seen is much too complex for it to work.

Parusky wrote:

Gentlemen,

Dave P's last suggestion works as a macro on the live data. I think I know
why. When a fixed width gets stored in the macro it is raw data dimensionally
set when the data length varies the result varies. Thank you Dave P for
your kind and patient efforts. Your solution works for the repetitive macro
on this data.

Joel's program was also tested out for some smaller PDF files where I need
all of the data. It worked perfectly for extracting the info into a CSV file
nice and neat. I will retain that code for many other applications. This
application is an extract out of a massive PDF and doesn't justify its use
but it will not go to waste.

Thanks for your useful comments Nick

Now all I have to do is figure out how to convert it to a looped manual
macro with a empty cell delimited if statement and I am in business. Must
have bought the wrong book or am getting to old to learn this protocol.
Started out board wiring on a IBM 401 and am tired of learning new languages.
C'est la Vie!

Thanks to all again
Parusky




"NickHK" wrote:

Dave,
I think you are on the right track.

NickHK

"Dave Peterson" wrote in message
...
I ain't giving up on this either <bg.

NickHK wrote:

If Dave's advise has not worked, please explain why you think you have
"fixed width data", when in other posts you appear to have variable
length,
space separated data.

NickHK

"Parusky" wrote in message
...
Please help. VBA Ignorant. I am trying to build a macro to convert a
lot
of
fixed width data from text to columns sequentially for multiple rows.
The
macro recorder will not work due its nature with arrays. The simple
keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple
repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson




--

Dave Peterson

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson

I put a bunch of random numbers generated with =randbetween(1,1000) in a A1:A30
of a new worksheet.

I used PrimoPDF to print this page.

I opened the resulting .pdf file in UltraEdit (a very nice text editor). I
couldn't find the first string that represented the number in A1 (347). There
were no three contiguous characters that showed 347.

And there were lots of numeric control characters.

I would not use this technique to try to rescue numerical data. For me, it
doesn't come close to being useful. (You may have better results.)

On the other hand, I have used this technique to try to save text from damaged
binary files -- not just .pdf's. But then I could run it through a spell
checker and use lots of checking.



Joel wrote:

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave: there are different forms of pdf files. Some are text files and some
are graphic. You can tell the differences if you try to select text and you
can't then that portion of the pdf file is not test, but a graphic.

when working with pdf files I ofter can't select text. I have Adobe
proffesional at work and sometimes with these files I can convert the graphic
section to text using the OCR converter. Sometimes the pdf file is protected
and the protection feature soesn't allow the conversion to text. there are
lots of pdf file type and you can't make any generalizations.

"Dave Peterson" wrote:

I put a bunch of random numbers generated with =randbetween(1,1000) in a A1:A30
of a new worksheet.

I used PrimoPDF to print this page.

I opened the resulting .pdf file in UltraEdit (a very nice text editor). I
couldn't find the first string that represented the number in A1 (347). There
were no three contiguous characters that showed 347.

And there were lots of numeric control characters.

I would not use this technique to try to rescue numerical data. For me, it
doesn't come close to being useful. (You may have better results.)

On the other hand, I have used this technique to try to save text from damaged
binary files -- not just .pdf's. But then I could run it through a spell
checker and use lots of checking.



Joel wrote:

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I don't recall ever seeing a .pdf file that was a text file. Even the .pdf
files that contain no graphics have been binary (non-text) files.



Joel wrote:

Dave: there are different forms of pdf files. Some are text files and some
are graphic. You can tell the differences if you try to select text and you
can't then that portion of the pdf file is not test, but a graphic.

when working with pdf files I ofter can't select text. I have Adobe
proffesional at work and sometimes with these files I can convert the graphic
section to text using the OCR converter. Sometimes the pdf file is protected
and the protection feature soesn't allow the conversion to text. there are
lots of pdf file type and you can't make any generalizations.

"Dave Peterson" wrote:

I put a bunch of random numbers generated with =randbetween(1,1000) in a A1:A30
of a new worksheet.

I used PrimoPDF to print this page.

I opened the resulting .pdf file in UltraEdit (a very nice text editor). I
couldn't find the first string that represented the number in A1 (347). There
were no three contiguous characters that showed 347.

And there were lots of numeric control characters.

I would not use this technique to try to rescue numerical data. For me, it
doesn't come close to being useful. (You may have better results.)

On the other hand, I have used this technique to try to save text from damaged
binary files -- not just .pdf's. But then I could run it through a spell
checker and use lots of checking.



Joel wrote:

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

DAve: Some pdf files you can search for word, others you can't. Some you can
select text and copy the text to word. for the copy and paste into word, the
files must contain text.

"Dave Peterson" wrote:

I don't recall ever seeing a .pdf file that was a text file. Even the .pdf
files that contain no graphics have been binary (non-text) files.



Joel wrote:

Dave: there are different forms of pdf files. Some are text files and some
are graphic. You can tell the differences if you try to select text and you
can't then that portion of the pdf file is not test, but a graphic.

when working with pdf files I ofter can't select text. I have Adobe
proffesional at work and sometimes with these files I can convert the graphic
section to text using the OCR converter. Sometimes the pdf file is protected
and the protection feature soesn't allow the conversion to text. there are
lots of pdf file type and you can't make any generalizations.

"Dave Peterson" wrote:

I put a bunch of random numbers generated with =randbetween(1,1000) in a A1:A30
of a new worksheet.

I used PrimoPDF to print this page.

I opened the resulting .pdf file in UltraEdit (a very nice text editor). I
couldn't find the first string that represented the number in A1 (347). There
were no three contiguous characters that showed 347.

And there were lots of numeric control characters.

I would not use this technique to try to rescue numerical data. For me, it
doesn't come close to being useful. (You may have better results.)

On the other hand, I have used this technique to try to save text from damaged
binary files -- not just .pdf's. But then I could run it through a spell
checker and use lots of checking.



Joel wrote:

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'm not writing about selecting text, searching for text or copying from the
..pdf.

I'm saying that I've never seen code like your suggested code work well enough
to recover numeric data from a .pdf file.

Joel wrote:

DAve: Some pdf files you can search for word, others you can't. Some you can
select text and copy the text to word. for the copy and paste into word, the
files must contain text.

"Dave Peterson" wrote:

I don't recall ever seeing a .pdf file that was a text file. Even the .pdf
files that contain no graphics have been binary (non-text) files.



Joel wrote:

Dave: there are different forms of pdf files. Some are text files and some
are graphic. You can tell the differences if you try to select text and you
can't then that portion of the pdf file is not test, but a graphic.

when working with pdf files I ofter can't select text. I have Adobe
proffesional at work and sometimes with these files I can convert the graphic
section to text using the OCR converter. Sometimes the pdf file is protected
and the protection feature soesn't allow the conversion to text. there are
lots of pdf file type and you can't make any generalizations.

"Dave Peterson" wrote:

I put a bunch of random numbers generated with =randbetween(1,1000) in a A1:A30
of a new worksheet.

I used PrimoPDF to print this page.

I opened the resulting .pdf file in UltraEdit (a very nice text editor). I
couldn't find the first string that represented the number in A1 (347). There
were no three contiguous characters that showed 347.

And there were lots of numeric control characters.

I would not use this technique to try to rescue numerical data. For me, it
doesn't come close to being useful. (You may have better results.)

On the other hand, I have used this technique to try to save text from damaged
binary files -- not just .pdf's. But then I could run it through a spell
checker and use lots of checking.



Joel wrote:

Dave: I agree with your comments. I know how it will work. I'm very familar
with the data in a pdf file. The macro is very dependant on the data that is
inside the pdf file. I don't know how Parusky go the data that was posted.
I think it was only a portion of the pdf file that contained the numbers that
was needed. Not the whole PDF file.

I have on occasion did a cut and paste of a section of a pdf file so I
didn't have to re-type the document. I have to manual remive control
characters to get the text, but it worked. Open a pdf file with NOTEPAD and
you will see the text and control characters.

"Dave Peterson" wrote:

I don't think that renaming a real .pdf file to .txt will help this.

If you test this with any real .pdf, you'll find that it doesn't work the way
you want.

Joel wrote:

Dave: I tested with the data that was posted. You can always rename the pdf
to txtt if you think this would be a problem. There are lots of control data
in a pdf file which makes it look like a binary file. Not sure where to
start reading the real data because it was not posted. The code will still
work. There just may be lots of data (rows) that has to be deleted.

I'm reading data one character at a time (tsread.read(1)) which is the way
you should treat a binary file. Lets see the results before we make any more
comments.

"Dave Peterson" wrote:

Did this work on the pdf file you tested?

..Pdf's are different than .txt files.

Joel wrote:

Parusky: You will love this solution. Everybody does.
This macro will convert your text file (pdf) to a csv file. Then you can
open the csv and all your data will be in one column.

Change these three line ine the code below as needed.
Const MyPath = "C:\temp\"
ReadFileName = "text.txt"
WriteFileName = "text.csv"

Sub Getfixedtext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text.txt"
WriteFileName = "text.csv"

'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
Do While tsread.atendofstream = False

mychar = tsread.Read(1)

If mychar < " " Then

OutputLine = OutputLine + mychar
Else
If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If
End If

Loop

If OutputLine < "" Then
tswrite.WriteLine OutputLine
OutputLine = ""
End If

tswrite.Close
tsread.Close

End Sub

"Parusky" wrote:

Dear Joel,

The data is a copy and paste from a PDF so its not worth converting it to
another form. This data is text in a single cell column for each row and
each text item contains 12 groups of data. The data looks like this after a
paste(Which wraps in this mail format for some of the bigger numbers):

7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965
870 855 735 1050 455 455 420 775 60 0 35 420
116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717
118983
74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894
27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

The keystrokes I wrote down work fine for parsing it into columns since it
is all fixed width delimited.

Whenever a macro is recorded however it records a different array for each
row. If you try relative positioning it applys the array to the next cell
down. If that array is different then the results are in error.

The macro recorder will not work unless the data groups are all the exact
same length.

Parusky











"Joel" wrote:

Can you post the text file at this website. Open the text file with
notebook. sometimes it is easier to make the conversions on the text file
instead doing it on the worksheet.

I like converting the text files to CVS files and then reading them into
excel.

"Parusky" wrote:

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of
fixed width data from text to columns sequentially for multiple rows. The
macro recorder will not work due its nature with arrays. The simple keyboard
commands that do work are as follows:
Alt D
Alt e
Enter
Enter
Enter
Arrow down
Got the books on VBA but can't seem to get started on this simple repetitive
onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky






--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson