Help with VBA for document formatting.

[JRC]

Dear all:


I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.


JRC

 

[Rob Schneider]

Sorry, but I can't help you with VBA for Word 2004. I never used it at all.

I will say, though, that if you already know how to use Excel to do
then, then use Excel. There are so many different tools that could be
used to do this. For example, my favourite would be to use Python (which
is included in Mac OS X) and it's probably only a few (5 or 10?) lines
of code. Or Perl (also in Mac OS x), or C, or .... the list goes on and
one. No tool will work if not known how to use. That's why I say use
Excel which I presume you many know?

The key is not really the tool/languge, the key is to figure out the
algorithm. Something like:-

: Read in the file
: open a new file for writing (or in Excel use a different part of
spreadsheet)
: Loop through all lines of the file
::On each line read in all text up to and before the semi colon. Put
that text into a field 'title'. put text to right of semi colon in a
field called, say 'data'
::loop through all text in 'data' splitting by the full stop '.'.Count
the number of those fields.
::write out to file as many tab characters (Asci code 9) as counted
::write the 'title' field, write a semi colon, write the 'data' field
: close the write file

Towards the bottom of your email you say you want this automated. While
I assume you don't know how Python works, it is, in my view, better fit
for this purpose than using Word or Excel. But whatever you do, it will
be using the sort of algorithm explained above.

(Note: I did not debug the above algorithm ... surely there will be a
better way discovered).

--rms

www.rmschneider.com

Dear all:


I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.


JRC

 

[Rob Schneider]

I had a few spare moments. Here is the simple Python program (without
any error checking and without any warranty!!) to do all that I think
you say you need. It ended up a little longer than estimated. Sorry.

#!/usr/bin/env python
# encoding: utf-8
ifh=open("input.txt",'r')
ofh=open("output.txt","w")
inputtext=ifh.readlines()
ifh.close
tab='\t'
for line in inputtext:
t1=line.split(';')
title=t1[0]
data=t1[1][:-1]
cnt=len(t1[1].split('.'))
ofh.write(tab*cnt+title+' ['+data+']\n')
ofh.close()

(We used to do a lot of data "munging" and it all became easier when we
discovered Python. Happily, it's provided in OS X.)

--rms

www.rmschneider.com

Sorry, but I can't help you with VBA for Word 2004. I never used it at all.

I will say, though, that if you already know how to use Excel to do
then, then use Excel. There are so many different tools that could be
used to do this. For example, my favourite would be to use Python (which
is included in Mac OS X) and it's probably only a few (5 or 10?) lines
of code. Or Perl (also in Mac OS x), or C, or .... the list goes on and
one. No tool will work if not known how to use. That's why I say use
Excel which I presume you many know?

The key is not really the tool/languge, the key is to figure out the
algorithm. Something like:-

: Read in the file
: open a new file for writing (or in Excel use a different part of
spreadsheet)
: Loop through all lines of the file
::On each line read in all text up to and before the semi colon. Put
that text into a field 'title'. put text to right of semi colon in a
field called, say 'data'
::loop through all text in 'data' splitting by the full stop '.'.Count
the number of those fields.
::write out to file as many tab characters (Asci code 9) as counted
::write the 'title' field, write a semi colon, write the 'data' field
: close the write file

Towards the bottom of your email you say you want this automated. While
I assume you don't know how Python works, it is, in my view, better fit
for this purpose than using Word or Excel. But whatever you do, it will
be using the sort of algorithm explained above.

(Note: I did not debug the above algorithm ... surely there will be a
better way discovered).

--rms

www.rmschneider.com

Dear all:


I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.


JRC

 

[John McGhie]

Hi:

Yes, you can do it in VBA, and if you do do it in VBA, it doesn't matter
whether you do that in Word or Excel.

The VBA will be slightly simpler in Excel, which is cell-based by nature.
But it will take you at least a month to learn enough programming to do it
in VBA, assuming you do not already have some programming ability.

However, I believe you can do this in a combination of Excel and Word
without ANY programming. Let's see:

You can split the string at the semicolon, and move the text to the right to
the cell to the right. Then for each row, you can determine the number of
tabs by evaluating the length of the text in column B (cell 2).

1) If the file is currently a Text File, when you import it into Excel (use
Data>Get External Data>Import Text File) you can specify the delimiter to be
a semicolon, then Excel will automatically split the file into two columns
for you.

2) You then manually add two columns, one to the left of the name, and one
to the right of where the semicolon was. So the text is in column B and the
Alphanumerics are in column D

3) You then create an Excel formula that evaluates the length of the data in
alphanumeric column and writes an integer into the left-most column
specifying the number of tabs you want.

Of course, it's the number of ALFA groups you want, not the number of
periods. Simplest thing is to COPY column D into Column C, then Select
column C and use Edit>Replace to Find periods and replace them with nothing.

Assuming the data begins in Row 1, the formula in A1 then becomes:
=(LEN(C1)/3)-1

We actually want a value that is one less than the number of three-character
groups in the string, because the first one has no tab. Fill-down the
entire column with that formula.

Now we want to remove the junk from column C, but first we need to fix the
values in column A so they can't change.

4) Select the whole of column A, and COPY, then choose Edit>Paste
Special>Values. That replaces the formula results with their text values.

5) Now enter the character "[" in cell C1. Copy it, then select all the
rest of the cells in column C and Paste. Do the same in Column E, using the
character "]"

Now we want to replace the integers in Column a with the appropriate number
of tabs. We can't do tabs easily in Excel, so we use a replacement
character. We don't want to use tabs or anything else that will be
recognised as a delimiter, because we're about to use them for something
else. Assuming the data does not contain any % characters (make certain of
this, we're going to convert them all to tabs) I would use a % character.

6) Select all of Column A, and Find 0 and replace it with "nothing". Then
find "1" and replace it with a single % sign. Then 2 with %%, 3 with %%%
and so on.

6) Now save the file from Excel as "Text, Tab-delimited" and open that file
in Word.

Now we use some fancy wild-card searches to produce the final result.

The first thing we do is get rid of the tabs, because we don't want any of
them.

7) In Word, Edit>Find and search for ^t (which is the symbol for a tab
character) and replace it with nothing (check the Replace With box really is
empty...).

8) Now Search for [ and replace it with " [" (space and left bracket).

9) Now search for % and replace it with ^t (the tab character).

There you are, you've done it. Here is what I made from the text you sent,
while writing this post (the tabs won't show up, but trust me, they're
there...)

Body Regions [A01]
Abdomen [A01.047]
Abdominal Cavity [A01.047.025]
Peritoneum [A01.047.025.600]
Douglas' Pouch [A01.047.025.600.225]
Mesentery [A01.047.025.600.451]
Mesocolon [A01.047.025.600.451.535]
Omentum [A01.047.025.600.573]
Peritoneal Cavity [A01.047.025.600.678]
Peritoneal Stomata [A01.047.025.600.700]
Retroperitoneal Space [A01.047.025.750]
Abdominal Wall [A01.047.050]
Groin [A01.047.365]
Inguinal Canal [A01.047.412]
Umbilicus [A01.047.849]
Back [A01.176]
Lumbosacral Region [A01.176.519]
Sacrococcygeal Region [A01.176.780]
Breast [A01.236]
"Mammary Glands, Human" [A01.236.249]

Hope this helps

Dear all:


I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.


JRC

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[macropod]

Hi JRC,

I already answered this some days ago - in the Excel context in microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.

--
Cheers
macropod
[Microsoft MVP - Word]

Dear all:


I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.


JRC

 

[JRC]

HiJRC,

I already answered this some days ago - in the Excel context in microsoft..public.mac.office.excel, but you've given no feedback
there to the solution I posted.

--
Cheers
macropod
[Microsoft MVP - Word]

Dear all:

I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
    Abdomen;A01.047
         Abdominal Cavity;A01.047.025
              Peritoneum;A01.047.025.600
                   Douglas' Pouch;A01.047.025.600.225
                   Mesentery;A01.047.025.600.451
                        Mesocolon;A01.047.025.600.451.535
                   Omentum;A01.047.025.600.573
                        Peritoneal Cavity;A01.047.025.600.678
                   Peritoneal Stomata;A01.047.025.600.700
              Retroperitoneal Space;A01.047.025.750
         Abdominal Wall;A01.047.050
         Groin;A01.047.365
         Inguinal Canal;A01.047.412
         Umbilicus;A01.047.849
    Back;A01.176
         Lumbosacral Region;A01.176.519
         Sacrococcygeal Region;A01.176.780
    Breast;A01.236
         Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
    Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
         Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
              Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything  from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.

JRC

Hi Macropod.

I am sorry as I thought I had posted a reply to your post on the Excel
forum.

As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.

After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.

Do you know why this might be happening ?

I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?

Thanks,


JRC

 

[John McGhie]

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

HiJRC,

I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback

there to the solution I posted.

--
Cheers
macropod
[Microsoft MVP - Word]



message...

Dear all:

I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
    Abdomen;A01.047
         Abdominal Cavity;A01.047.025
              Peritoneum;A01.047.025.600
                   Douglas' Pouch;A01.047.025.600.225
                   Mesentery;A01.047.025.600.451
                        Mesocolon;A01.047.025.600.451.535
                   Omentum;A01.047.025.600.573
                        Peritoneal Cavity;A01.047.025.600.678
                   Peritoneal Stomata;A01.047.025.600.700
              Retroperitoneal Space;A01.047.025.750
         Abdominal Wall;A01.047.050
         Groin;A01.047.365
         Inguinal Canal;A01.047.412
         Umbilicus;A01.047.849
    Back;A01.176
         Lumbosacral Region;A01.176.519
         Sacrococcygeal Region;A01.176.780
    Breast;A01.236
         Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
    Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
         Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
              Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything  from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.

JRC

Hi Macropod.

I am sorry as I thought I had posted a reply to your post on the Excel
forum.

As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.

After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.

Do you know why this might be happening ?

I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?

Thanks,


JRC

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[JRC]

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

HiJRC,
I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.
--
Cheers
macropod
[Microsoft MVP - Word]
message...
Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.
My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).
Below is an example of the type of file I now have:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:
Body Regions;A01
    Abdomen;A01.047
         Abdominal Cavity;A01.047.025
              Peritoneum;A01.047.025.600
                   Douglas' Pouch;A01.047.025.600..225
                   Mesentery;A01.047.025.600.451
                        Mesocolon;A01.047.025..600.451.535
                   Omentum;A01.047.025.600.573
                        Peritoneal Cavity;A01..047.025.600.678
                   Peritoneal Stomata;A01.047.025..600.700
              Retroperitoneal Space;A01.047.025.750
         Abdominal Wall;A01.047.050
         Groin;A01.047.365
         Inguinal Canal;A01.047.412
         Umbilicus;A01.047.849
    Back;A01.176
         Lumbosacral Region;A01.176.519
         Sacrococcygeal Region;A01.176.780
    Breast;A01.236
         Mammary Glands, Human;A01.236.249
As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01
Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
    Abdomen;A01.047
Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
         Abdominal Cavity;A01.047.025
Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
              Peritoneum;A01.047.025.600
And so on, and so forth.
Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:
Abdominal Cavity;A01.047.025
Where the term is followed by a semicolon and alphanumeric code.
To the following:
Abdominal Cavity [A01.047.025]
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.
Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.
Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything  from A to Z but with an approach that makes it easy fora
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.
Thank you in advance for your help.
JRC

Hi Macropod.

I am sorry as I thought I had posted a reply to your post on the Excel
forum.

As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.

After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.

Do you know why this might be happening ?

I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?

JRC

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

 

[John McGhie]

Line length

If you want to split a command in VBA, you must use a "continuation
character" at the end of each line.

Type a space then an underscore at the end of each line that you want
shorter than it was shown in the original. Or simply run the line out to an
unlimited length: the compiler doesn't care. But it will not accept
line-breaks in the middle of commands.

If it is wrong, the editor will turn the whole command red, to show you that
it cannot compile it. So you can see these very quickly.

Cheers

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

HiJRC,

I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback

there to the solution I posted.

messageom
...
Dear all:

I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.

My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).

Below is an example of the type of file I now have:

Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249

This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:

Body Regions;A01
    Abdomen;A01.047
         Abdominal Cavity;A01.047.025
              Peritoneum;A01.047.025.600
                   Douglas' Pouch;A01.047.025.600.225
                   Mesentery;A01.047.025.600.451
                        Mesocolon;A01.047.025.600.451.535
                   Omentum;A01.047.025.600.573
                        Peritoneal Cavity;A01.047.025.600.678
                   Peritoneal Stomata;A01.047.025.600.700
              Retroperitoneal Space;A01.047.025.750
         Abdominal Wall;A01.047.050
         Groin;A01.047.365
         Inguinal Canal;A01.047.412
         Umbilicus;A01.047.849
    Back;A01.176
         Lumbosacral Region;A01.176.519
         Sacrococcygeal Region;A01.176.780
    Breast;A01.236
         Mammary Glands, Human;A01.236.249

As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01

Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
    Abdomen;A01.047

Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
         Abdominal Cavity;A01.047.025

Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
              Peritoneum;A01.047.025.600

And so on, and so forth.

Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:

Abdominal Cavity;A01.047.025

Where the term is followed by a semicolon and alphanumeric code.

To the following:

Abdominal Cavity [A01.047.025]

Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.

Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.

How can these two processes be accomplished with the use of a macro ?

I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.

Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything  from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.

Thank you in advance for your help.

Hi Macropod.

I am sorry as I thought I had posted a reply to your post on the Excel
forum.

As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.

After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.

Do you know why this might be happening ?

I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?

JRC

 --

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Rob Schneider]

JRC,

I'm curious why you didn't try the little Python program I gave you?
Saving those few lines into a file as "convert.py" or something, editing
the input/output file names, and you'd be done in minutes. And you'd
have something which will allow the automatation (for future file
conversions) which you wanted. Just curious.

Python is provided in Mac OS X ... they this because it's useful for
many things, including exactly the sort of think you want to do.

--rms

www.rmschneider.com

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

HiJRC,
I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.
--
Cheers
macropod
[Microsoft MVP - Word]
message...
Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.
My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).
Below is an example of the type of file I now have:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600..225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025..600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01..047.025.600.678
Peritoneal Stomata;A01.047.025..600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01
Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047
Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025
Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600
And so on, and so forth.
Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:
Abdominal Cavity;A01.047.025
Where the term is followed by a semicolon and alphanumeric code.
To the following:
Abdominal Cavity [A01.047.025]
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.
Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.
Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.
Thank you in advance for your help.
JRC
Hi Macropod.
I am sorry as I thought I had posted a reply to your post on the Excel
forum.
As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.
After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.
Do you know why this might be happening ?
I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?
Thanks,
JRC

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

 

[John McGhie]

Hi Rob:

Because you didn't tell him how to install Python, initialise the
environment and how to use the script.

If you want to take our users out of the GUI, you have to start from the GUI
and tell them how to bring up Terminal and login, step-by-step.

And then they're not going to do it We have to use detailed
instructions for getting VBA macros to run.

We have those on the website: http://word.mvps.org/Mac/InstallMacro.html

If you have similarly-detailed step-by-step instructions for Python,
starting with a running GUI and the install DVDs for OS 10.6.0, send them
along: I am the webmaster, address is in the .sig

Cheers

JRC,

I'm curious why you didn't try the little Python program I gave you?
Saving those few lines into a file as "convert.py" or something, editing
the input/output file names, and you'd be done in minutes. And you'd
have something which will allow the automatation (for future file
conversions) which you wanted. Just curious.

Python is provided in Mac OS X ... they this because it's useful for
many things, including exactly the sort of think you want to do.

--rms

www.rmschneider.com

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

On 10/12/09 7:22 PM, in article
(e-mail address removed), "JRC"





HiJRC,
I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.
--
Cheers
macropod
[Microsoft MVP - Word]
messagecom
...
Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.
My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).
Below is an example of the type of file I now have:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600..225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025..600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01..047.025.600.678
Peritoneal Stomata;A01.047.025..600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01
Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047
Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025
Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600
And so on, and so forth.
Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:
Abdominal Cavity;A01.047.025
Where the term is followed by a semicolon and alphanumeric code.
To the following:
Abdominal Cavity [A01.047.025]
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.
Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.
Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.
Thank you in advance for your help.
JRC
Hi Macropod.
I am sorry as I thought I had posted a reply to your post on the Excel
forum.
As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.
After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.
Do you know why this might be happening ?
I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?
Thanks,
JRC
--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Rob Schneider]

Oh. My fault, I guess. Apologies to all. I prefer 5 minute solutions to
working on things for days on end for no good reason.

The full instructions for using Python already written and easily
available. I do not have the energy or time to re-write and re-publish
especially on an un-billed basis.

Please put a link on your web site to: http://www.python.org/download/mac/

My understanding is that Python installed by standard as part of OS X. I
have no recollection of having to install it. It's just "there."

(to run the script I gave them:

1. Save the script to a file named "processtext.py" (without the
quotation marks) with a text editor.
2. with the text editor change the name of the input/output file
3. go to the Mac OSX "terminal" program type command "python
processtext.py" (without quotation marks)

The program will process the input file as prescribed, making the output
file. Done.


--rms

www.rmschneider.com

Hi Rob:

Because you didn't tell him how to install Python, initialise the
environment and how to use the script.

If you want to take our users out of the GUI, you have to start from the GUI
and tell them how to bring up Terminal and login, step-by-step.

And then they're not going to do it We have to use detailed
instructions for getting VBA macros to run.

We have those on the website: http://word.mvps.org/Mac/InstallMacro.html

If you have similarly-detailed step-by-step instructions for Python,
starting with a running GUI and the install DVDs for OS 10.6.0, send them
along: I am the webmaster, address is in the .sig

Cheers

JRC,

I'm curious why you didn't try the little Python program I gave you?
Saving those few lines into a file as "convert.py" or something, editing
the input/output file names, and you'd be done in minutes. And you'd
have something which will allow the automatation (for future file
conversions) which you wanted. Just curious.

Python is provided in Mac OS X ... they this because it's useful for
many things, including exactly the sort of think you want to do.

--rms

www.rmschneider.com

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

On 10/12/09 7:22 PM, in article
(e-mail address removed), "JRC"





HiJRC,
I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.
--
Cheers
macropod
[Microsoft MVP - Word]
messagecom
...
Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.
My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).
Below is an example of the type of file I now have:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600..225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025..600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01..047.025.600.678
Peritoneal Stomata;A01.047.025..600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01
Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047
Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025
Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600
And so on, and so forth.
Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:
Abdominal Cavity;A01.047.025
Where the term is followed by a semicolon and alphanumeric code.
To the following:
Abdominal Cavity [A01.047.025]
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.
Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.
Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.
Thank you in advance for your help.
JRC
Hi Macropod.
I am sorry as I thought I had posted a reply to your post on the Excel
forum.
As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.
After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.
Do you know why this might be happening ?
I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?
Thanks,
JRC
--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]
Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[Jim Gordon Mac MVP]

Oh. My fault, I guess. Apologies to all. I prefer 5 minute solutions to
working on things for days on end for no good reason.

The full instructions for using Python already written and easily
available. I do not have the energy or time to re-write and re-publish
especially on an un-billed basis.

Please put a link on your web site to: http://www.python.org/download/mac/

My understanding is that Python installed by standard as part of OS X. I
have no recollection of having to install it. It's just "there."

(to run the script I gave them:

1. Save the script to a file named "processtext.py" (without the
quotation marks) with a text editor.
2. with the text editor change the name of the input/output file
3. go to the Mac OSX "terminal" program type command "python
processtext.py" (without quotation marks)

The program will process the input file as prescribed, making the output
file. Done.

Hi Rob

A little documentation would also be of great interest. What do each of
the following commands do?

#
ifh
ofh
split
tl

Is input.txt the file name of the source file?
Is output.txt the file name of the destination file? Does the utility
create output.txt or does it have to already exist before starting?

It looks like ifh is tied to the input file and ofh is tied to the
output file. Is my assumption correct?


#!/usr/bin/env python
# encoding: utf-8
ifh=open("input.txt",'r')
ofh=open("output.txt","w")
inputtext=ifh.readlines()
ifh.close
tab='\t'
for line in inputtext:
t1=line.split(';')
title=t1[0]
data=t1[1][:-1]
cnt=len(t1[1].split('.'))
ofh.write(tab*cnt+title+' ['+data+']\n')
ofh.close()

Your is the first posting I've noticed about Python working with Office
documents. What sorts of things do you see Python useful for (obviously
text manipulations, but what else)?

Can python scripts be called via AppleScript?

Thank you for opening up an entirely new direction for office automation.

-Jim

 

[macropod]

Hi JRC,

If you're unable to get the macros working, you could use a series of Find/Replace operations in Word. With the 'use wildcards'
option checked, you could have the:
1. first set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3})
Replace \1^t\2
2. second set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3}.[0-9]{3})
Replace \1^t\2
3. third set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3}.[0-9]{3}.[0-9]{3})
Replace \1^t\2
and so on, simply increasing the number of '.[0-9]{3}' Find variables until all are done. The data you posted suggest only 5 levels,
but you can go to 6 levels with this approach.

--
Cheers
macropod
[Microsoft MVP - Word]

When you paste from a browser, it converts "spaces" to non-breaking spaces.

That is correct for HTML, but it is fatal in the compiler, where a
non-breaking space is a literal character, not a "blank"

Instead, paste the code into TextEdit.

In TextEdit, use the menu to "Make plain text".

Then copy in TextEdit and paste THAT into the VBA Editor.

Then it will compile and run

Cheers

HiJRC,
I already answered this some days ago - in the Excel context in
microsoft.public.mac.office.excel, but you've given no feedback
there to the solution I posted.
--
Cheers
macropod
[Microsoft MVP - Word]
message...
Dear all:
I am currently working on a rather large document that I need to
format a certain specific way. The document includes nearly 57,000
lines of plain text that need to be indented according to a simple
rule so that it can then be imported into an application as a tab-
delimited file. A member from another forum suggested this might be
more easily done with Word instead of Excel so I decided to post this
questions here.
My system is a Mac G5 running the latest version of Leopard (not Snow
Leopard) which is 10.5.8 and I have Microsoft Office 2004 installed
(haven't upgraded to 2008 and will probably wait until next version to
upgrade).
Below is an example of the type of file I now have:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
This is just a small portion of the file I am working with. This is a
"flat" text file but what I need is a "hierarchical" or "tree
structure" file where each of these lines is indented with a number of
"tab keystrokes" corresponding to its level as depicted by the
alphanumeric code following the term. Example:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
Peritoneum;A01.047.025.600
Douglas' Pouch;A01.047.025.600.225
Mesentery;A01.047.025.600.451
Mesocolon;A01.047.025.600.451.535
Omentum;A01.047.025.600.573
Peritoneal Cavity;A01.047.025.600.678
Peritoneal Stomata;A01.047.025.600.700
Retroperitoneal Space;A01.047.025.750
Abdominal Wall;A01.047.050
Groin;A01.047.365
Inguinal Canal;A01.047.412
Umbilicus;A01.047.849
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
Breast;A01.236
Mammary Glands, Human;A01.236.249
As you will notice each line has been indented using the tab key a
number of times corresponding to the size of the alphanumeric code.
Lines with an alphanumeric code containing only 3 characters stay on
the left most position (no tabs). Example:
Body Regions;A01
Lines with an alphanumeric code containing 7 characters (example:
A01.047) are indented with one single tab keystroke. Example:
Abdomen;A01.047
Lines with an alphanumeric code containing 11 characters (example:
A01.047.025) are indented with two tab keystrokes. Example:
Abdominal Cavity;A01.047.025
Lines with an alphanumeric code containing 15 characters (example:
A01.047.025.600) are indented with three tab keystrokes. Example:
Peritoneum;A01.047.025.600
And so on, and so forth.
Once this step is finished and the file has been edited from a flat
text to one with a hierarchical or tree structure using tab entries
(to derive a tab-delimited file) the second part of the project
involves the removal of certain characters and replacement with
others. Basically I need the format to go from the existing:
Abdominal Cavity;A01.047.025
Where the term is followed by a semicolon and alphanumeric code.
To the following:
Abdominal Cavity [A01.047.025]
Where a space is added after the term, the semicolon is removed and
brackets are placed at the beginning and end of the alphanumeric
code.
Considering the size of my document as I stated (little less than
57,000 entries) I would like to find a way to automate the process.
How can these two processes be accomplished with the use of a macro ?
I have never worked with macros before and any help would be extremely
valuable and appreciated. I have both Excel 04 and Word 04 as part of
the Office 2004 package in case the information is relevant. I am not
sure which of the applications would be better suited for this task
but am open to your suggestions.
Also I would appreciate suggestions on textbooks that would cover VBA
for Excel and Word. I would like to find books that would cover
everything from A to Z but with an approach that makes it easy for a
beginner to understand and work with VBA on Microsoft Office for the
Mac. Any recommendations will be appreciated.
Thank you in advance for your help.
JRC

Hi Macropod.

I am sorry as I thought I had posted a reply to your post on the Excel
forum.

As I mentioned on some of my posts in that forum I tried your
subroutine as well as a few others that were posted but kept (and
still keep) getting error messages as I try to run them.

After copying and pasting your subroutine (and the others as well) I
tried to run them and Excel gave me an error message. The first line
appears highlighted in yellow and an arrow appears to the left of that
first line.

Do you know why this might be happening ?

I tried looking at a VBA book at Barnes and Noble but couldn't find
one specific for the Mac. There are several for Windows but I am not
sure what version of VBA on Windows would be equivalent to the one I
am running on the Mac (Office Mac 2004). Which of the Windows versions
of Excel with VBA would be equivalent to Office Mac 2004: the 2003 or
2007 version ?

JRC

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.


Thank you very much for your reply to my post and really helpful
explanations.

I tried everything as you suggested. While I am able to copy and paste
into TextEdit and then copy the plain text when Excel won't allow me
to paste into the VBA window. I tried it numerous times and it simply
won't work.

This time I tried typing the subroutines you provided instead of
copying and pasting. While it worked fine I ran into an error on a
specific line, and this line appears on both the tab and indent
subroutines you suggested on the Excel forum. I checked my spelling to
make sure it wasn't the problem but apparently it is something else.

Can we post images to this group ? I have a couple of screen captures
that show what happened when I tried to run the subroutine.

Any ideas as to what might be causing the problem ?

TIA,


Joe

 

[Rob Schneider]

Jim,

Python is a terrific and useful "language". Used widely and
extensively, even by Microsoft, Google, Yahoo, etc. Apple includes it in
OS X; hence it's easily available on all Mac machines. Can be installed
on most all operating systems

I've used it often for so-called "data munging" (which is what the OP
was doing), and I use it to create much of the HTML for a couple of web
sites driven from data stored in a database (provides lots of automation
and saves me time). Just this week I used Python to transform monthly
world temperature data released by the UK's Met Office as 1,700 text
files into a relational database. Data is much easier to work with when
you get it out of "documents" (they released the data as text files!)
and into databases.

I can't do the language justice here, but I will elaborate on the the
little program I provided to the OP. It really was a five minute job to
create the script. Then five minutes to process the data. Then "done"
and on to more valuable things.

The best first step to learn Python, I think, would be to run and
experiment with this program. Use the input data as defined by the OP
(original poster) as a test case. Insert "print" after any variable of
interest, e.g. "print title".

To learn Python--a lot of books are available. "Dive into Python" is
available both as free download, or to buy at
http://www.diveintopython.org/. I mention it first only becuase it's
free. I recommend starting with "Learning Python" by Mark Lutz published
by O'Reilly. Further, full documentation and lot of other resources
given at www.python.org. IBM has a full series of "learning" and
example documents on the web for free. Get lots of working programs and
scripts from ActiveState. If you need to use databases, I've had great
success using Django (also free) as th

Re using Python with Applescript. I have never gotten into Applescript
as the need has not arisen. However I found
http://macdevcenter.com/pub/a/mac/2...lescript-to-get-the-most-out-of-your-mac.html
which indicates that not only it is possible, but i can see some
possibilities. Python is hugely more powerful than Applescript; but
Applescript probably provides easiest access to bespoke software and
data on the Mac. Nice to see there is guidance available. Perhaps I'll
recognise this approach for something I need to do in future.

The OP wanted to "munge" the text data (not a document) from one format
to other based on described pattern. I saw that pattern as an algorithm
easily implementable in Python. Once recognised took about 5 minutes to
write the code. Did not test how long it would take to transform the
real data but could probably process many thousands of records per
minute or faster.

Word documents, as I understand, are XML files. Python provides
extensive XML support. My hunch is that Micrsoft's XML spec for Word
documents is not simple; but surely could be made to work. Google
"python word docx" for clues.

See below for comments written into the code, using # as the prefix
which makes it a comment file for keeping in the programm.


#!/usr/bin/env python
# encoding: utf-8

# create an input file handle 'ifh' for reading the input file
# which here is called 'input.txt'. can be whatever.
# 'r' tells the open command to open file for reading
ifh=open("input.txt",'r')

# create an output file handle. File name here shown as
# 'output.txt' but it can be anything. 'w' means write.
ofh=open("output.txt","w")

# from the input file read all the lines of text into a
# list. Python has 'lists' which are sequences of arbitrary
# objects. In this case the objects are strings.
# Lists are at the heart of Python's power. Makes a lot
# of things much simpler. Python also has other objects
# called dictionaries and tuples which have different purpose
# but equally as powerful. Can have dictionaries of lists
# or lists of dictionaries ... but I digress. Read about
# Python elsewhere to learn this.
inputtext=ifh.readlines()

# close the input file. We are done with it since we have
# read all the data into a list
ifh.close

# put the tab character into a variable called (funny
# enough), 'tab'
tab='\t'


# loop through every 'line' in the inputtext 'list'.
# must indent each line in the 'for' loop by a consistent
# number of spaces. Convention is to use four spaces
for line in inputtext:

# use the function 'split' to take the line of text
# and search for a semi-colon. then 'split' the line
# of text into list of words where the semi-colon is
t1=line.split(';')

# take the first word (index=0) and make it the title
title=t1[0]

# take the second word (index=1) and make it the data
# the [:-1] is takes off the last character of the word string
# which is the trailing new line \n read in by readlines(s) above.
# Python's ability to use these index operators [] is at the
# heart of its power for string processing. Convered first in
# all the books about Python.
data=t1[1][:-1]

# establish the count of bits of data and put into the cnt
# variable. Doing this by splitting the data field at the periods
# which is how the original data was formatted. The resulting
# number of words is the number of datapoints in the string
# following the title in the original data
cnt=len(t1[1].split('.'))

# Write the new formatted line (as prescribed by the OP)
# to the output file. Need preceed the title of the data
# by as many tabs as there are data fields. This is the
# tab*cnt computation which puts in as many tabs as equal
# to the cnt field. After the tabs, concatenate the title
# field, then the brackets, then the data string (which is
# the full string with periods". Finally, add a new line
# string at the end. Like a soft carriage return SHIFT-ENTER
# in Word.
ofh.write(tab*cnt+title+' ['+data+']\n')


# After the looping is complete, close the output file
ofh.close()

At this point you have a new text file with which you can do whatever
with, e.g. import into Word to make the documentation.

--rms

www.rmschneider.com

Oh. My fault, I guess. Apologies to all. I prefer 5 minute solutions to
working on things for days on end for no good reason.

The full instructions for using Python already written and easily
available. I do not have the energy or time to re-write and re-publish
especially on an un-billed basis.

Please put a link on your web site to:
http://www.python.org/download/mac/

My understanding is that Python installed by standard as part of OS X. I
have no recollection of having to install it. It's just "there."

(to run the script I gave them:

1. Save the script to a file named "processtext.py" (without the
quotation marks) with a text editor.
2. with the text editor change the name of the input/output file
3. go to the Mac OSX "terminal" program type command "python
processtext.py" (without quotation marks)

The program will process the input file as prescribed, making the output
file. Done.

Hi Rob

A little documentation would also be of great interest. What do each of
the following commands do?

#
ifh
ofh
split
tl

Is input.txt the file name of the source file?
Is output.txt the file name of the destination file? Does the utility
create output.txt or does it have to already exist before starting?

It looks like ifh is tied to the input file and ofh is tied to the
output file. Is my assumption correct?


#!/usr/bin/env python
# encoding: utf-8
ifh=open("input.txt",'r')
ofh=open("output.txt","w")
inputtext=ifh.readlines()
ifh.close
tab='\t'
for line in inputtext:
t1=line.split(';')
title=t1[0]
data=t1[1][:-1]
cnt=len(t1[1].split('.'))
ofh.write(tab*cnt+title+' ['+data+']\n')
ofh.close()

Your is the first posting I've noticed about Python working with Office
documents. What sorts of things do you see Python useful for (obviously
text manipulations, but what else)?

Can python scripts be called via AppleScript?

Thank you for opening up an entirely new direction for office automation.

-Jim

 

[Jim Gordon Mac MVP]

Jim,

Python is a terrific and useful "language". Used widely and extensively,
even by Microsoft, Google, Yahoo, etc. Apple includes it in OS X; hence
it's easily available on all Mac machines. Can be installed on most all
operating systems

Great stuff, Rob!

It will take a while to digest this. It sounds like AppleScript could be
a bridge between Python and VBA.

Thanks for replying. This is very helpful.

-Jim

 

[JRC]

Line length

If you want to split a command in VBA, you must use a "continuation
character" at the end of each line.

Type a space then an underscore at the end of each line that you want
shorter than it was shown in the original.  Or simply run the line out to an
unlimited length: the compiler doesn't care.  But it will not accept
line-breaks in the middle of commands.

If it is wrong, the editor will turn the whole command red, to show you that
it cannot compile it.  So you can see these very quickly.

Cheers


The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.

Thank you again for your help and detailed explanation.

I've tried everything just as you suggested and keep getting this
error message when I try to run VBA. I've tried both copying and
pasting into TextEdit and then Excel as you described in a previous
post and also tried typing the entire subroutine myself. In the end
the result is the same.

This is the subroutine you posted on the Excel forum:

One way (with tabs)

Public Sub TabIndentText()
Dim sTabs As String
Dim sTemp As String
Dim rCell As Range
Dim nCount As Long
Dim nSemi As Long
sTabs = String(10, vbTab)
For Each rCell In Selection
With rCell
nCount = Len(.Text) - _
Len(Application.Substitute(.Text, ".", ""))
nSemi = InStr(1, .Text, ";")
.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) &
_
" [" & Trim(Mid(.Text, nSemi + 1)) & "]"
End With
Next rCell
End Sub


This is the line where I keep getting the error message as it is now
in VBA:

..Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & "[" &
Trim(Mid(.Text, nSemi + 1)) & "]"

I've also tried typing the entire subroutine with and without indents
but it doesn't seem to make a difference.

I remember in one of your posts that you mentioned the "Split" command
not being a part of VBA in Office Mac 2004. Could the same be the case
for any of the commands in this line ?

This is really a very important project I need to finish and I didn't
anticipate it taking this long and being this complicated. Any help
you can offer in addition to all that you already have will be very
appreciated.

Thank you,


Joe

 

[JRC]

JRC,

I'm curious why you didn't try the little Python program I gave you?
Saving those few lines into a file as "convert.py" or something, editing
the input/output file names, and you'd be done in minutes. And you'd
have something which will allow the automatation (for future file
conversions) which you wanted.  Just curious.

Python is provided in Mac OS X ... they this because it's useful for
many things, including exactly the sort of think you want to do.

--rms

www.rmschneider.com

Hi, Rob.

Thank you very much for your post and really helpful suggestion.

I've already begun to do some research into what you suggested. This
will certainly be a useful tool to have for future projects. My
problem now is the fact that I am running short on time and have a
deadline to finish the project I am working on. I haven't yet been
able to make simpler solutions work such as the VBA subroutines John
and others have posted as well as macropods suggestion to use the Find
and Replace command. I don't think this would be the right time to
embrace something more complicated and with a greater learning curve
such as a new programming language.

I have looked at some of the sites you recommended but even finding
the right version of Python hasn't been simple and straightforward.
Depending on what site you look at some suggest that those running
Leopard should install the full version of Python while others state
that since Apple pre-installs a simple version of Python in OS X one
should not install another one as there could be some conflicts and
should only install some complement files.

This is clearly a powerful tool to use and I will definitely make an
attempt to learn it. But this is just not the right time and right
project.

Thank you very much again for your post and great suggestions.

Joe

 

[JRC]

HiJRC,

If you're unable to get the macros working, you could use a series of Find/Replace operations in Word. With the 'use wildcards'
option checked, you could have the:
1. first set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3})
Replace \1^t\2
2. second set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3}.[0-9]{3})
Replace \1^t\2
3. third set of Find/Replace parameters as
Find (^13)([!.]{1,}.[0-9]{3}.[0-9]{3}.[0-9]{3})
Replace \1^t\2
and so on, simply increasing the number of '.[0-9]{3}' Find variables until all are done. The data you posted suggest only 5 levels,
but you can go to 6 levels with this approach.

Hi, macropod.

This is the kind of simple solution I am looking for. Between what you
suggest here and John's subroutines I feel confident I may finally
(after weeks of frustrating failed attempts) resolve this issue.

I tried running the Find and Replace command with the text just as you
suggested above. The first time I did Word just froze and I had to do
a "Force Quit". The subsequent times (I tried a few more times) it
simply didn't work and gave a message that it couldn't find what I was
looking for.

Let me more specific:

When I push the "Find Next" button this is the message I get:

Word has finished searching the document. The search item was not
found.

Could it be something I am doing wrong ?

Thanks again for your help and really helpful suggestions.

Joe

 

[John McGhie]

Hi JRC:

Works here, in Excel 2004. You ARE aware that this is Excel code, right?
No ³cells² in Word, this code won¹t run in Word.

It¹s not my code, but there¹s nothing wrong with it.

Did you notice the space, underscore between the two lines? That's a
continuation character. Delete the line-breaks until it joins up with the
line above ending in an ampersand, and it will work fine. Here¹s the code
pasted out of my copy of Excel after successfully running it.

As soon as you begin programming, you have to set your mind to
character-for-character accuracy and attention to detail

Public Sub TabIndentText()
Dim sTabs As String
Dim sTemp As String
Dim rCell As Range
Dim nCount As Long
Dim nSemi As Long
sTabs = String(10, vbTab)
For Each rCell In Selection
With rCell
nCount = Len(.Text) - _
Len(Application.Substitute(.Text, ".", ""))
nSemi = InStr(1, .Text, ";")
.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & _
" [" & Trim(Mid(.Text, nSemi + 1)) & "]"
End With
Next rCell
End Sub

Line length

If you want to split a command in VBA, you must use a "continuation
character" at the end of each line.

Type a space then an underscore at the end of each line that you want
shorter than it was shown in the original.  Or simply run the line out to an
unlimited length: the compiler doesn't care.  But it will not accept
line-breaks in the middle of commands.

If it is wrong, the editor will turn the whole command red, to show you that
it cannot compile it.  So you can see these very quickly.

Cheers


The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

Hello, John.

Thank you again for your help and detailed explanation.

I've tried everything just as you suggested and keep getting this
error message when I try to run VBA. I've tried both copying and
pasting into TextEdit and then Excel as you described in a previous
post and also tried typing the entire subroutine myself. In the end
the result is the same.

This is the subroutine you posted on the Excel forum:

One way (with tabs)

Public Sub TabIndentText()
Dim sTabs As String
Dim sTemp As String
Dim rCell As Range
Dim nCount As Long
Dim nSemi As Long
sTabs = String(10, vbTab)
For Each rCell In Selection
With rCell
nCount = Len(.Text) - _
Len(Application.Substitute(.Text, ".", ""))
nSemi = InStr(1, .Text, ";")
.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) &
_
" [" & Trim(Mid(.Text, nSemi + 1)) & "]"
End With
Next rCell
End Sub


This is the line where I keep getting the error message as it is now
in VBA:

.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & "[" &

Trim(Mid(.Text, nSemi + 1)) & "]"

I've also tried typing the entire subroutine with and without indents
but it doesn't seem to make a difference.

I remember in one of your posts that you mentioned the "Split" command
not being a part of VBA in Office Mac 2004. Could the same be the case
for any of the commands in this line ?

This is really a very important project I need to finish and I didn't
anticipate it taking this long and being this complicated. Any help
you can offer in addition to all that you already have will be very
appreciated.

Thank you,


Joe

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]