Help with VBA for document formatting.
JRC
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 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
Rob Schneider
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
John McGhie
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
On 4/12/09 8:54 PM, in article
d3fe7c7a-b93a-479a...@u18g2000pro.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
--
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:jo...@mcghie.name
macropod
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]
"JRC" <drjcham...@gmail.com> wrote in message news:d3fe7c7a-b93a-479a...@u18g2000pro.googlegroups.com...
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]
>
>
>
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
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
541285fb-ce57-4815...@g1g2000pra.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
--
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
>
> On 10/12/09 7:22 PM, in article
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
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
On 11/12/09 10:30 AM, in article
10eedc9d-3f6b-487c...@u16g2000pru.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
--
+61 4 1209 1410, mailto:jo...@mcghie.name
Rob Schneider
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
>>>>> 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
John McGhie
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
On 12/12/09 5:09 PM, in article OtH1gGve...@TK2MSFTNGP05.phx.gbl, "Rob
Schneider" <rmschne@yahoo_but_not_often_checked.com> wrote:
--
+61 4 1209 1410, mailto:jo...@mcghie.name
Rob Schneider
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
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
--
Jim Gordon
Mac MVP
Co-author of Office 2008 for Mac All-in-One For Dummies
http://tinyurl.com/Office-2008-for-Dummies
macropod
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]
"JRC" <drjcham...@gmail.com> wrote in message news:10eedc9d-3f6b-487c...@u16g2000pru.googlegroups.com...
Rob Schneider
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/2007/05/08/using-python-and-applescript-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
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
>
>
> 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:j...@mcghie.name
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
<rmschne@yahoo_but_not_often_checked.com> wrote:
> 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.
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
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
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
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!
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
macropod
When doing the Find/Replace operation (in Word), did you have the 'use wildcards' option checked?
--
Cheers
macropod
[Microsoft MVP - Word]
"JRC" <drjcham...@gmail.com> wrote in message news:727a698f-d452-49d2...@y32g2000prd.googlegroups.com...
Rob Schneider
The version of Python that is provided as standard in OS X (2.61 on my
box) is just fine. It is not a "simple" version as there is no such
thing. You've been mislead by internet pages. Sorry. Don't install
anything else. Use what's there.
I know that you are not familiar with the Python code I gave you, but
those few lines of code do what you said you need done. It's fit for
purpose and does the job. It took about 5 minutes to write the code and
running on your 50,000 line text file (my recollection) should take just
a few more minutes.
While I understand the need to stay with familiar tools thinking that is
the best course, I've learned in my old age that it's often more
important and valuable to learn when to reject the familiar for the
tools which are more fit for purpose. "When all one has is a hammer,
then the hammer is appropriate for every job."
--rms
JRC
John:
It still won't work. I believe I have done everything right so I am
puzzled as to why this is happening.
Is there any way I can send you a small image of a partial screen
capture so that you can see exactly what I am looking at ?
Please let me know.
Thank you,
Joe
John McGhie
Email me the Excel Workbook you are working on (address in the .sig).
The code runs fine here: I am wondering whether it is "running" fine there
but that you have not selected the correct cells before running it, or if
the data does not follow the format the code expects, or that you may have
put the macro into the wrong workbook.
Cheers
On 15/12/09 5:51 PM, in article
8e33c3ce-141f-428d...@u25g2000prh.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
>
> John:
>
> It still won't work. I believe I have done everything right so I am
> puzzled as to why this is happening.
>
> Is there any way I can send you a small image of a partial screen
> capture so that you can see exactly what I am looking at ?
>
> Please let me know.
>
> 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:jo...@mcghie.name
JRC
Hi, John.
Thank you very much for your kind offer.
I've e-mailed you the excel worksheet I have been working on along
with two small screen captures showing the error messages I have been
getting when trying to run the code.
It is probably something I haven't done right but I just can't figure
it out.
Thanks again for your help.
Joe
JRC
>
> read more »
Hello, Rob.
I just tried your Python routine but got an error message stating the
following:
/System/Library/Frameworks/Python.framework/Versions/2.5/Resources/
Python.app/Contents/MacOS/Python: can't open file 'processtext.py':
[Errno 2] No such file or directory
At first I thought it had something to do with the fact that it might
be looking for the file 'processtext.py' in the main directory while I
had it on my desktop. So I transferred the files to the main directory
but nothing changed - the result was the same error message.
Is there something I am missing ?
Thank you,
Joe
Rob Schneider
>
> Hello, Rob.
>
> I just tried your Python routine but got an error message stating the
> following:
>
> /System/Library/Frameworks/Python.framework/Versions/2.5/Resources/
> Python.app/Contents/MacOS/Python: can't open file 'processtext.py':
> [Errno 2] No such file or directory
>
> At first I thought it had something to do with the fact that it might
> be looking for the file 'processtext.py' in the main directory while I
> had it on my desktop. So I transferred the files to the main directory
> but nothing changed - the result was the same error message.
>
> Is there something I am missing ?
>
> Thank you,
>
> Joe
Joe,
It means that you launched Python and asked it to load the
'processtext.py' file and it can't find it. Probably because when you
launched Python the file processtext.py and the input file was not in
the current directory. It has to be in the 'current directory' simply
because you did not give a full path to the actual file location. The
computer has to be told what to do. It cannot read the user's mind.
I don't know what you mean by "main directory".
'processtext.py' is the name I suggested you give to the little
programme file. Is this the file name you gave it?
The error message suggests that it can't find the file; so on that that
basis let's go through the steps to take. I will be *very* explicit.
While there are a lot of words here, it is simple.
1. Launch the Mac program "TextEdit" using Finder and from the
Applications Folder.
2. Copy/paste the program I provided previously into TextEdit. Edit
the name of the input file name and output file name you will use. I
suggest "input.txt" and "output.txt". Use whatever you like. Ensure
that the indented portions of the program are, as mentioned previously,
all four spaces (or if not four then must all be the same, e.g. three).
Do not use tabs, do not use inconsistent indents.
3. Save the file into the "Documents" folder as 'processtext.py'. (You
can put it in any folder you like, including Desktop ... I just say
Documents just so that I can write this procedure and pick an existing
folder to avoid creating a new folder which adds more steps to the
procedure).
4. Copy the file which holds your text that you wish to transform into
the "Documents" folder. Use Finder.
5. Open the Mac "Terminal" program. You'll probably find it on the
"Dock" but not there, launch it via Finder in the Applications/Utilities
folder (help at Google "launch mac terminal")
6. Unless you changed something, the Mac will put you into terminal
mode in the /Users/[youruserid] folder. Change to the Documents folder
by issuing the command (without the quote marks), "cd Documents" then
press Return Key.
7.The "current directory" will be /Users/[youruserid/Documents". Check
this by issuing the command "pwd" to see the *p*present *w*working
*d*iretory.
8.Confirm that your "processtext.py is there by issuing the command "ls
passwordtext.py" and if it is the Mac will respond by by showing you the
file name. If the file is not present, go back to steps 1, 2, and 3.
This file must be in this folder simply because we keep it simple by not
putting it elsewhere which would require giving a full path name when
launching the program.
9.Confirm that your input file (called "input.txt"???) is in that folder
by issuing the "ls input.txt" file. If it is not there, then go to step
4 above. This file must be in this folder simply because we keep it
simple by not putting it elsewhere which would require giving a full
path name in the programm.
10. Issue the command "python processtext.py" which will a. launch
Python, b. load the "processtext.py" file from the current directory
(Documents), then run that program. It should create the file
output.text (or whatever you called it in step 2 above) into the current
folder (Documents). It should only take a few seconds (depending on
the size of the input file).
11. With Word or TextEdit, look at the output.txt file and see if it
did what you wanted.
12. If it did not, then you need to adjust the Python program to do
what you actually want if you want to use Python. You may also find
that the input data format/pattern is not exactly as expected and
therefore the program may not work. You can then chose to change the
data format/pattern, or make the program more sophisticated to enable
transforming the unexpected format/patter. That's just life when
munging data.
--rms
John McGhie
OK, I'll let you off this time :-)
There was nothing wrong with your code, it's your data that's the problem.
The code depends on the presence of a semicolon somewhere after the first
character in the cell to do its thing. It was blowing up on the first cell
that did not have one, because there was no error-checking in the code.
Error-checking is the sort of "nice to have" that you get in
commercially-written code that you pay for -- you can't expect someone who
does you a favour by running up a few lines in a newsgroup to make the code
robust for you :-)
Here's a new version, this one will turn red any cell that does not satisfy
the input requirements, so you can find them easily.
You should be aware that this code is NOT "restartable". You need to remove
the rows it has processed before re-running it, or the already-processed
rows will turn red.
Hope this helps
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, ";")
If nCount > 0 And nSemi > 0 Then
.Value = Left(sTabs, nCount) & Trim(Left(.Text, nSemi - 1)) & "
[" & Trim(Mid(.Text, nSemi + 1)) & "]"
Else
rCell.Interior.ColorIndex = 3
End If
End With
Next rCell
End Sub
On 16/12/09 1:48 PM, in article
a3cdb8b1-bf6b-4f87...@x5g2000prf.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
--
JRC
>
>
>
>
>
> <drjchamberl...@gmail.com> wrote:
> > On Dec 15, 5:54 am, John McGhie <j...@mcghie.name> wrote:
> >> Hi Joe:
>
> >> Email me the Excel Workbook you are working on (address in the .sig).
>
> >> The code runs fine here: I am wondering whether it is "running" fine there
> >> but that you have not selected the correct cells before running it, or if
> >> the data does not follow the format the code expects, or that you may have
> >> put the macro into the wrong workbook.
>
> >> Cheers
>
> >> On 15/12/09 5:51 PM, in article
>
> >> <drjchamberl...@gmail.com> wrote:
>
> >>> John:
>
> >>> It still won't work. I believe I have done everything right so I am
> >>> puzzled as to why this is happening.
>
> >>> Is there any way I can send you a small image of a partial screen
> >>> capture so that you can see exactly what I am looking at ?
>
> >>> Please let me know.
>
> >>> 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
>
> > Hi, John.
>
> > Thank you very much for your kind offer.
>
> > I've e-mailed you the excel worksheet I have been working on along
> > with two small screen captures showing the error messages I have been
> > getting when trying to run the code.
>
> > It is probably something I haven't done right but I just can't figure
> > it out.
>
> > Thanks again for your help.
>
> > Joe
>
> This email is my business email -- Please do not email me about forum
> matters 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
Hi, John.
I just tried running this latest subroutine you provided and this time
it ran without any error messages.
The problem is the fact that (1) all records were highlighted in red
which made the entire column A red from the first record all the way
down to the last one, and (2) the data didn't move to the
corresponding cell according to the size of the alphanumeric character
(the number of tabs).
The file generated after the subroutine or macro in Excel will have to
be exported as a tab-delimited text file in order to be used
elsewhere. I chose to use Excel because it is cell-based and it would
make it visually easier to see the number of tabs by looking at what
column the line had been moved to. The subroutine you provided seems
to have applied tabs to the lines while they all remained in the first
column. In case I export this document as a tab-delimited file the
formatting won't be right and I believe the tabs will disappear.
Perhaps I wasn't clear in my initial posts and I apologize if that was
the case. My understanding was that in working with text files
imported into Excel as tab-delimited files tabs applied to the text
would imply the line content being shifted one or more cells to the
right. Is this not the case ?
What command can I use so that instead of applying tabs the text will
actually be moved a number of cells to the right corresponding to the
lenght of the alphanumeric string contained in the line ?
Thank you very much again for all your help with this issue.
Joe
macropod
Now that you know how to get vba code from newsgroups to run, perhaps it's time to reconsider something based on my earlier
suggestions for Excel:
Sub Indenter()
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 1 To Len(.Cells(i, 1).Value) - Len(Replace(.Cells(i, 1).Value, ".", ""))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
or
Sub Indenter()
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
For j = 1 To Len(.Cells(i, 1).Value) - Len(Application.Substitute(.Cells(i, 1).Value, ".", ""))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
Very quick and will not affect any records that have already been indented.
--
Cheers
macropod
[Microsoft MVP - Word]
"JRC" <drjcham...@gmail.com> wrote in message news:1a7561f4-9a35-4e9b...@b36g2000prf.googlegroups.com...
JRC
Hi, Rob.
I just tried the Python program as you suggested. Thank you for the
detailed instructions that made every step easy to follow.
I am note sure if the program runs or doesn't. After I press enter
Terminal immediately displays the next line waiting for input and
doesn't show any error message. However, as I go into the Documents
folder I simply can't find the "output.txt" file that the program
should have saved. I also tried a spotlight search but couldn't locate
any file in my system with the name "output.txt" (I did this just to
make sure it had not saved the program to a different location
although I can see that Terminal is looking at the "right" folder).
What do you think may have happened ?
Thank you very much for your help and for the detailed explanation and
instructions.
Joe
macropod
Sub Indenter()
Dim i, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
If InStr(.Cells(i, 1).Value, ";") > 0 Then _
.Cells(i, 1).Value = Replace(.Cells(i, 1).Value, ";", " [") & "]"
For j = 1 To Len(.Cells(i, 1).Value) - Len(Replace(.Cells(i, 1).Value, ".", ""))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
or
Sub Indenter()
Dim i, j As Integer
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
If InStr(.Cells(i, 1).Value, ";") > 0 Then _
.Cells(i, 1).Value = Application.Substitute(.Cells(i, 1).Value, ";", " [") & "]"
For j = 1 To Len(.Cells(i, 1).Value) - Len(Application.Substitute(.Cells(i, 1).Value, ".", ""))
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
--
Cheers
macropod
[Microsoft MVP - Word]
"JRC" <drjcham...@gmail.com> wrote in message news:1a7561f4-9a35-4e9b...@b36g2000prf.googlegroups.com...
Rob Schneider
Joe,
I haven't a clue what happened on your computer and why this is so hard
for you. What do you think is wrong?
I don't have a clue if Spotlight will find a recently-created file. Why
do you think the program will have chosen on it's own to save it
somewhere other than the current directory. It will only do that if you
told it to. You don't say that you ran the program from the Documents
folder. The program, as provided, will put the output file in the same
folder as the input file.
As you can see in the original source code I put in no statements to
show progress of work. This is something that you could have done to
debug it. From the symptoms you provide the program did work and there
were no errors and the output file should have been created in the
current directory. But I don't know where you told the program to put
the file.
Here is a *more* sophisticated version of the program (with a *lot* of
additional exception checking and output statements to show it's
working. It's the same program just with added extras (watch that lines
don't get garbled. remember the four space indent rule):
=====================
#!/usr/bin/env python
# encoding: utf-8
# change these if you want. without folder names,
# the current directory assumed
ifn="input.txt" # inputfile name
ofn="output.txt" # outputfile name
try:
ifh=open("input.txt",'r')
except:
print "EXCEPTION: can't open input file."
try:
ofh=open("output.txt","w")
except:
print "EXCEPTION: can't open output file."
try:
inputtext=ifh.readlines()
except:
print "EXCEPTION: can't read input file."
ifh.close
tab='\t'
for line in inputtext:
t1=line.split(';')
if len(t1) < 2:
print "ERROR: no ';' in input line. Skipping.",t1
else:
title=t1[0]
data=t1[1][:-1]
cnt=len(t1[1].split('.'))
print "...will put %i tab stops in %s;%s" % (cnt,title,data)
try:
ofh.write(tab*cnt+title+' ['+data+']\n')
except:
print "EXCEPTION: can't write output file."
try:
ofh.close()
except:
print "EXCEPTION. Can't close output file."
print "\nDone. input from:%s output to: %s." % (ifn,ofn)
print "Check with the command: 'ls %s'" % (ofn)
===============
Here is the input file I used to test (with one error introduced to
check the error checking):
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
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
Back;A01.176
Lumbosacral Region;A01.176.519
Sacrococcygeal Region;A01.176.780
BreastA01.236
Mammary Glands, Human;A01.236.249
================
Here is the output printed to the screen for this test:
================
MacBook:documents rmschne$ python transform.py
...will put 1 tab stops in Body Regions;A01
...will put 2 tab stops in Abdomen;A01.047
...will put 3 tab stops in Abdominal Cavity;A01.047.025
...will put 6 tab stops in Mesocolon;A01.047.025.600.451.535
...will put 5 tab stops in Omentum;A01.047.025.600.573
...will put 5 tab stops in Peritoneal Cavity;A01.047.025.600.678
...will put 5 tab stops in Peritoneal Stomata;A01.047.025.600.700
...will put 4 tab stops in Retroperitoneal Space;A01.047.025.750
...will put 2 tab stops in Back;A01.176
...will put 3 tab stops in Lumbosacral Region;A01.176.519
...will put 3 tab stops in Sacrococcygeal Region;A01.176.780
ERROR: no ';' in input line. Skipping. ['BreastA01.236\n']
...will put 3 tab stops in Mammary Glands, Human;A01.236.24
Done. input from:input.txt output to: output.txt.
Check with the command: 'ls -l output.txt'
=================
Here is the result of the ls command:
=================
MacBook:documents rmschne$ ls -l output.txt
-rw-r--r-- 1 rmschne staff 416 18 Dec 08:09 output.txt
=================
JRC
Hi, macropod.
That did it. It worked and I was able to make the formatting changes I
was trying to make.
I noticed that Excel took quite a long time to run the program. It
wasn't as fast as I expected it would be. Also I tried the first
program you submitted but it would run. The second program you labeled
"And for a complete solution:" was the one that did the job. It ran
the first time without a problem.
I have one more question to ask: How can I make a slight modified
version of the file ? I just noticed as I looked at the application
where I will use this file that I need yet another version of this
file in a different format. This second format needs to be indented
just as the first but the portion after the label (name) needs to be
removed from the line (in other words the alphanumeric portion needs
to be removed). Let me be more specific:
This is what we started with:
Body Regions;A01
Abdomen;A01.047
Abdominal Cavity;A01.047.025
This is what your program did:
Body Regions [A01]
Abdomen [A01.047]
Abdominal Cavity [A01.047.025]
The second version of the file I needs to be formatted like this:
Body Regions
Abdomen
Abdominal Cavity
How could I modify your program so that it will perform this task ?
Can you point me in the right direction ? Perhaps if I understand what
the proper algorightm must be I can then try to research the proper
functions and make the necessary changes to your program.
Thank you very much for all your help and for taking the time to work
with me on this project.
Joe
macropod
Try:
Sub CleanUp_Indenter()
Dim i, j, k As Long
Application.ScreenUpdating = False
With ActiveSheet
For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
k = Len(.Cells(i, 1).Value) - Len(Replace(.Cells(i, 1).Value, ".", ""))
If InStr(.Cells(i, 1).Value, ";") > 0 Then _
.Cells(i, 1).Value = Left(.Cells(i, 1).Value, InStr(.Cells(i, 1).Value, ";") - 1)
For j = 1 To k
.Cells(i, 1).Insert Shift:=xlToRight
Next
Next
End With
Application.ScreenUpdating = True
End Sub
or, for progress feeback via the status bar:
Sub CleanUp_Indenter()
Dim i, j, k, l As Long
Dim SBar As Boolean
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
With ActiveSheet
l = .Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To l
k = Len(.Cells(i, 1).Value) - Len(Replace(.Cells(i, 1).Value, ".", ""))
If InStr(.Cells(i, 1).Value, ";") > 0 Then _
.Cells(i, 1).Value = Left(.Cells(i, 1).Value, InStr(.Cells(i, 1).Value, ";") - 1)
For j = 1 To k
.Cells(i, 1).Insert Shift:=xlToRight
Next
Application.StatusBar = i & " of " & l "records processed."
Next
End With
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub
Note: the progress report will slow things down slightly.
--
Cheers
macropod
[Microsoft MVP - Word]
"JRC" <drjcham...@gmail.com> wrote in message news:9b891648-1f8d-476c...@u16g2000pru.googlegroups.com...
Rob Schneider
several orders of magnitude faster, if not faster.
Rob Schneider
--rms
On 18/12/09 01:45, JRC wrote:
JRC
Hi, macropod.
I tried the both subroutines above and this time I got an error
message. With both the Replace function becomes highlighted when I try
to run it and I get a message that says the highlighted function is
not defined. I checked the spelling to make sure I had not mistyped
any of the commands but it all appears to be just as you wrote it.
Any ideas of what might be happening ?
Thanks again for all your help.
Joe
JRC
Hi, Rob.
I just tried to run this second version of the Python program you
provided and it gave me an error message. This is what I got on the
Terminal window:
File "processtext.py", line 3
try:
ifh=open("input.txt",'r')
except:
print
"EXCEPTION: can't open input file."
try:
ofh=open
("output.txt","w")
except:
print "EXCEPTION: can't open output
file."
try:
inputtext=ifh.readlines()
except:
print
"EXCEPTION: can't read input file."
ifh.close
^
SyntaxError: invalid syntax
The file was saved using TextEdit using UTF-8 encoding. It seems
Python couldn't open the file for some reason. Could it possibly have
something to do with the formatting ?
I followed your instructions to the letter. Changed from the root
directory to the Documents folder to make it the working directory,
issued the ls command and verified that both files were indeed in the
Documents directory. Everything seems to be in the right place
following your instructions.
What should I do ?
Thank you,
Joe
JRC
Hi, Rob.
On a separate note I was at Barnes and Noble yesterday to look at some
books. I decided to look at some on programming and chose to look at
Python first. I must be honest and tell you I was a little discouraged
to see how complex Python is. One of the books from O'Reilly media on
Python is about the size of a large Phone directory (yellow pages
even). I also read last night while looking at Mailman that the whole
Mailman application was written with Python (and some C added for
security, as the developers state on their own page). This seems like
a very robust language one would need sometime to learn. No ???
Thanks,
Joe
macropod
OK, change 'Replace' to 'Application.Substitute'.
--
Cheers
macropod
[Microsoft MVP - Word]
"JRC" <drjcham...@gmail.com> wrote in message news:9ef5ee0b-90f7-4a2e...@13g2000prl.googlegroups.com...
Rob Schneider
--rms
>>>> all four spaces (or if not four then must all be the same, e.g. three)..
>>>> Do not use tabs, do not use inconsistent indents.
>>
>>>> 3. Save the file into the "Documents" folder as 'processtext.py'.. (You
Joe
The error is *exactly* as it says. There is "invalid syntax" with the
command on Line 3. Sorry, but it is *not* as you conclude that Python
could not open the file, nor the formatting. Error messages should be
believed.
From the error message I conclude that you have formatting the program
such that there are no line endings after the lines. I conclude this
because the error message as concatenated the dozen or so lines after
the first line into one line. I do not know how this happened. You are
not copy/pasting right.
Remember.
1. one line per line of code
2. indents are four spaces increments, (four, eight, or twelve).
I do not know how this flaw in the text file you created happened. I
have never saved a file using "UTF-8 encoding". Setting on my machine
in "automatic".
You ask what you should do.
1. Since copy/paste did not work for you, retype the program *exactly*
as written. Hit the *return* key (with the big arrow) at the end of
each line. Put in four spaces (with space bar) as indents. When
indents are bigger, use the incremental 4, 8, 12, etc.
2. Save in Text Edit with 'automatic' for plain text encoding. (i have
no clue what other settings do.
3. If you get error messages: read them and think what they mean. Read
the Python documentation. It's free.
3. Or you can give up on this. Since Excel works for you. Go with it.
However, excel will take several orders of magnitude more time to
process the files of the size you say you have.
Python works. I do not know why this so hard for you. My offer of a web
conference stands, but please take up this offer soonest.
Rob Schneider
complexity for doing simple things. I have given you a very short
program (the first one) that will run. It is simple enough. I frankly
think it's simpler than most any other language. You do not need to
learn more than what an hour or so of reading the first few chapters of
the "Learning Python" book I recommended. It covers everything I gave
you (opening files, writing files, processing text strings, loops). You
do not need to know the entire book! How did you conclude that?
Yes, it probably would take sometime to learn Python. I've been using
it for 10 years (as a tool to solve business and data problems ... not
as a programmer) and I still don't know it all. Never will. It's like
saying someone knows all of Excel and Word. Always something to learn
(as long as one is willing to learn).
While simple, it allows complex solutions and applications applications.
That's what make great tools stand-out.
Yes, it is robust and industrial strength and can do some mighty
impressive things. That's why Google uses it for a lot of their things.
I made my program I gave you a little more complicated when you were
unable to make it work and claimed that it did not work. I put in some
exception detection to help you make it work. Even that did not work for
you.
I really like Python because it is relatively simple to do simple things
like you are doing. It is pretty much exactly what you need (a fast way
to process huge text files and do it in an automated way for future
use). But you can't make it work.
So I recommend you stick with Excel. (Have you ever seen a thing book
about Excel? Excel is also capable of making "big" applications. by
that logic ... )
--rms
JRC
> Hi JRC,
>
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
Hello, macropod.
I tried the subroutine as you suggested replacing the 'Replace'
function with 'Application.Substitute' and this time it ran just fine
and with an added level of sophistication I must add for having the
status bar displaying the progress of the conversion. It was quite
impressive to see that and allowed me to keep an eye on my computer to
see just how much more I still had to go.
Thank you very much indeed for all the help and guidance you have
offered me with this important project.
I also want to thank both John McGhie and Rob Schneider for their help
as they made it simpler for me to understand the process and make it
work.
I wish those who celebrate it a Merry Christmas and Happy New Year
extensive to your families and loved ones. For those that don't
celebrate these holidays I wish a happy holiday season.
Best regards,
Joe
JRC
Rob:
Please don't take me wrong. I am grateful and really appreciate the
time you have dedicated to helping me with this issue. I have finally
reached the result I needed to reach by being able to edit the file
and achieve the formatting required for my application. But now that I
have done so and no longer have to worry about meeting a deadline I
would like to consider learning a programming language as this project
has shown me how useful it can be to know one for future projects.
I've heard and read of Python before and have no doubt it is a very
powerful language. Basic seems to have its own appeal since this is a
language I've used before so the structure feels far more familiar.
Also the syntax being closer to that of our own human language would
seem faster to learn. Do you agree ? But I understand Basic is
probably not as powerful as Python, requires far more code written to
perform the same tasks and probably can't do some things that Python
can. Is this right ?
Since I'll have a little more available time during the holiday season
I will get me a copy of the Python book you recommended and see if I
can read the first chapters to get more acquainted with the language,
its structure and syntax. I am also going to review and try this last
program you offered me again to make sure I didn't make any mistakes.
Things didn't run and I am sure it is not your fault but it must be
related to something I didn't do right.
I want to thank you again for sharing all the information you shared
with me and for getting me interested in Python. You provided great
references to learning Python and I will use them during the holidays
to get me started.
Thanks again for all your help and happy holidays.
Joe
John McGhie
Don't confuse "BASIC" with "Visual Basic", and don't confuse "Visual Basic"
with "Visual Basic for Applications", and don't confuse "Visual Basic .Net"
with any of the others :-)
Visual Basic is really NOTHING like BASIC. Some of the very old commands
remain, and will still work, but shouldn't be used because there is
generally a better way of doing things. For example: "Go To" and "Go Sub"
and line numbers should never appear in a VB program :-)
BASIC is an interpreted language (it converts to CPU instructions while it
is running). Unlike BASIC, VB and VB.Net: they are "compiled" down to
machine code. VB is really "just another high-level language" and you can
write anything you like in it, however, it is full of "Libraries" that make
it much easier to write Windows programs. There are widgets in there that
handle all of the operating system calls and draw all the dialog boxes for
you, so if you are good you can do a lot with 20 lines of code.
VB.Net is the upgraded equivalent. It's strongly object-oriented, and would
be one of the languages of choice for writing cross-application distributed
applications, especially if they are going anywhere near the Internet. The
.Net stuff calls in a whole raft of things that make it easy to write
internet-enabled applications.
If you wanted to start learning one of the "Basics", I would suggest Visual
Basic for Applications (VBA). The difference is that VBA sits "inside" a
running application like Word, so it automatically knows which application
it is supposed to be talking to, which documents are open, and what is in
those documents.
You can do things in a few lines in VBA that would take a couple of
screenfuls of code in VB, because everything the application says and does
is already "known" to VBA. And anything your program does in VBA is
automatically correct in the file, because it is the "application" that does
it. For example: the VBA we sent you for your Excel issue did not have to
worry about finding the correct place in the binary file to insert the
characters you wanted, it could simply call Excel to inject the result
directly into the cell in the native file format. Because it is running
inside Excel, it already knows about Excel and Workbooks and Cells and
Columns.
I suspect that VBA may be easier to learn in one respect: you already know
how the application you are programming for works. Another way of saying
that is "If you don't know how the application you are programming for
works, you haven't a prayer of writing VBA!"
And the things you write in VBA are immediately useful: because they apply
to the work you are doing in Microsoft Office today. One restriction with
Python is that it is not necessarily very easy to do anything unless you can
render the input file into plain text.
But as Rob points out, Python is VERY handy if you want to mash text
directly :-)
You decide your interests, and choose your weapon accordingly. If you were
heading off for a career as a commercial programmer, I would say definitely
start with VBA, because that leads directly to VB.Net, which leads directly
to C#, which is where a lot of the action is in the current Internet Bubble
:-)
I suspect that if you wanted to learn to be a good "programmer" you might
want to start with C, then C++ or Objective C, which is what Apple writes
in.
Cheers
On 23/12/09 7:34 PM, in article
30be6309-374d-4b2f...@u18g2000pro.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
--
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:jo...@mcghie.name
Rob Schneider
--rms
On 23/12/09 08:34, JRC wrote:
> On Dec 20, 11:50 pm, Rob Schneider
> <rmschne@yahoo_but_not_often_checked.com> wrote:
>> I do not agree the the size of the book about a subject related to it's
>> complexity for doing simple things. I have given you a very short
>> program (the first one) that will run. It is simple enough. I frankly
>> think it's simpler than most any other language. You do not need to
>> learn more than what an hour or so of reading the first few chapters of
>> the "Learning Python" book I recommended. It covers everything I gave
>> you (opening files, writing files, processing text strings, loops). You
>> do not need to know the entire book! How did you conclude that?
>>
>> Yes, it probably would take sometime to learn Python. I've been using
>> it for 10 years (as a tool to solve business and data problems ... not
>> as a programmer) and I still don't know it all. Never will. It's like
>> saying someone knows all of Excel and Word. Always something to learn
>> (as long as one is willing to learn).
>>
>> While simple, it allows complex solutions and applications applications.
>> That's what make great tools stand-out.
>>
>> Yes, it is robust and industrial strength and can do some mighty
>> impressive things. That's why Google uses it for a lot of their things..
Joe,
There is no debate about Basic being easier than Python to learn. They
both can be as complex or as simple as the other. And debate about it
being close to our own "human language", in my opinion, is not a reason
to consider a tool for using on a computer! (and what "human language"
would you pick? English? Chinese? Russian? Welsh?).
Python is perfect for the problem you were doing. Pending the quality of
the data (which takes time sometimes to sort out), it was a 5 minute
programming job to create a programme that is several orders of
magnitude faster than your Excel solution and set you up for future
automation (a goal you said you had). I realize that 5 minutes was
several weeks ago with many hours of work later. Pity. The point using
computers is to save time; when much time is taken it is clue that the
approach may be flawed.
IMHO, learn Basic if you want to do programming in Excel, Word, etc. (I
don't know what the language will be in next years's version of Office).
Do not fool yourself because of the name that Basic is 'simple' and
'easy'. it is as complex/complicated as any other language. The issue
is solving problems, and problems sometimes are wicked and complicated.
IMHO, because I first learned FORTRAN in the 1960's, langauges since
then have come and gone and some I have "understood" and others passed
me by. Lots of Apple Basic in the early 1980's. I could not figure out
C or C++. Word Basic became a staple in the early 1990's. Dabbled in
Visual Basic. Perl is incompressible to me--but it is powerful with a
rich library and used by millions. But like I said, I'm not a
programmer. I just have to use computers to do the work I do and since
I'm basically lazy, if I "program" the solutions in code it reduces
re-work, my time, and becomes much more profitable.
Python makes good sense to me how it works. Just look at that very
short program I gave you! How much simpler can that be? (Hope you can
make it work.)
The *main* value is not the language but all the libraries and full
functionality provided. The capabilities provided by the standard
install of Python (and Perl for that matter) which *are now* on your
Apple Mac dwarf what's possible in Basic.
You'd have spend fortune in 3rd party add-ins to get more capability.
Becuase of libraries, you can find/lookup a provided-function to fit a
need and solve the problem in minutes! To have to do it yourself in
Basic, if not impossible, would surely cost a lot of money and time.
I don't think you have Basic on your Mac outside of what's provided in
the older versions of Office for Mac. Not sure how you'll find something
that is sustainable in Basic on the Mac. There probably are 3rd party
products you can buy.
As this thread now has nothing to do with Word Mac, time to stop.
Good luck on you exploration.
Clive Huggan
02cf77b7-f818-4444...@13g2000prl.googlegroups.com, "JRC"
<drjcham...@gmail.com> wrote:
<snip>
> I wish those who celebrate it a Merry Christmas and Happy New Year
> extensive to your families and loved ones.
<snip>
Joe,
How culturally sensitive you are to we Australians who all still call it
Christmas down here -- at the barbecue, in the golden sun, while imitation
snow, tinsel and reindeer decks the retail outlets! ;-))
And all the best to you too!
Cheers,
Clive Huggan
=============
CyberTaz
Merry Christmas |:>)
Bob Jones
[MVP] Office:Mac
On 12/24/09 4:01 AM, in article
C759792F.462B0%REMOVETH...@ANDTHISstrategists.com.au, "Clive Huggan"
macropod
--
Cheers
macropod
[Microsoft MVP - Word]
"CyberTaz" <onlygen...@com.cast.net> wrote in message news:C758CAB7.590F3%onlygen...@com.cast.net...
Phillip Jones, C.E.T.
macropod wrote:
> Nah, reindeer can't handle the heat. Here it's six white boomers (kangaroos).
>
--
Phillip M. Jones, C.E.T. "If it's Fixed, Don't Break it"
http://www.phillipmjones.net http://www.vpea.org
mailto:pjo...@kimbanet.com
John McGhie
every chimney for a beer. After 20,000,000 chimneys, Rudolph was the only
one still standing, and even he was a little directionally uncertain...
On 24/12/09 11:37 PM, in article
C758CAB7.590F3%onlygen...@com.cast.net, "CyberTaz"
<onlygen...@com.cast.net> wrote:
--
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:jo...@mcghie.name