macro works then fails
David Wheeler
QP10 10.0.0.800 Win2K all patches applied
I'm trying to write a macro which parses some text. After recording my
actions I get the following:
{SelectBlock A:B}
{BlockInsert.Columns A:B;Entire}
{BlockInsert.Columns A:B;Entire}
{SelectBlock A:A}
{ParseExpert.RowDelimiterReturn 1}
{ParseExpert.Go}
{ParseExpert.InputType Block}
{ParseExpert.InputBlock A:A}
{ParseExpert.OutputBlock A:B}
{ParseExpert.DataType Delimited}
{ParseExpert.ApplyFormatting 1}
{ParseExpert.ColumnWidths 1}
{ParseExpert.TextQualifier SingleQuote}
{ParseExpert.CellDelimiterOther 1}
{ParseExpert.CellDelimiter /}
{ParseExpert.RowDelimiterReturn 1}
{ParseExpert.Go}
{SelectBlock A:C1..A:C1}
{PutCell2 "Mass"}
{SelectBlock A:A}
{BlockDelete.Columns A:A;Entire}
{SelectBlock A:A1}
When I run this for the first time after recording it, it works fine.
After that the parsing step does not run. I've tried doing a find and
replace first to change all the "/" in the text to ";" as the semicolon
is a standard seperator but still no joy. I've also tried placing the
macro in my Macro.qpw library and changing all the sheet references from
A to P(0) but again no joy.
Have I run into a bug or am I doing something wrong. Would upgrading to
SP4 help?
thanks,
David
lemoto
David:
> I'm trying to write a macro which parses some text.
> When I run this for the first time after recording it, it works fine.
> After that the parsing step does not run.
>
Looks as if you have.-(}
Using your macro or my recording, it does not always work here after a
File|Close, File|Open either, nor after Close|Open QP.
> Would upgrading to SP4 help?
That is what I have (though the upgrade is recommended).
You can get the info out of eg: A2 by using in eg: B2..D2 -
@FIELD(A2,1,"/")|@FIELD(A2,2,"/")|@FIELD(A2,3,"/")|
and, if you wish, then use Edit|Convert to Values.
--
Good wishes!
Roy Lewis
lemoto
> You can get the info out of eg: A2 by using
- alternatively, this macro.
In J27..K27
@CELLPOINTER("row") | @STRING(J27,0)
and in J29..J36
{SelectBlock A2}
{ReCalc J27..K34}
+"{IF A"&K27&"=""""}{QUIT}"
+"{PutBlock @FIELD(A"&K27&",1,""/""), B"&K27&"}"
+"{PutBlock @FIELD(A"&K27&",2,""/""), C"&K27&"}"
+"{PutBlock @FIELD(A"&K27&",3,""/""), D"&K27&"}"
{Down}
{BRANCH J30}
If your data starts in other than Row 2, J29 needs editing.
The macro stops at the first blank Cell, which may or may not
suit your situation.
The macro cannot insert the two Columns; it could do so if
edited so that the BRANCH command becomes to L30 (which after
the Cols appear is the new target)
and so that the ReCalc covers J..M.
The macro is likely not to cope if the number of digits in the
Row ID increases, eg: 99, 100 or 999, 1000
A kludge would be to pass control to a second/third macro at
this point; or, a more complex structure can be constructed that
does cope properly.
Chris D
Probably not the problem, but your QP is not completely patched to date.
ftp://ftp.corel.com/pub/WordPerfect/wpwin/10/english/WPO2002SP4EN.exe
After installing SP4 you will have build 10.0.0.990.
lemoto
David:
> It works OK from A2..A9 but fails at A10. The contents of
> cell A10 are changed to "0" and a dialogue appears which says "Command
> equivalents not allowed in this context. Macro execution terminated.
> {PutBlock}{A:J33}"
I did warn of this:
> The macro is likely not to cope if the number of digits in the
> Row ID increases, eg: 99, 100 or 999, 1000
The simple answer, if it is acceptable, is just to have your data start
in Row 10. Then you are OK through Row 99. Or start at 100 and be OK
through 999.
If that is not acceptable, then the problem can be circumvented thus:
This problem arises because of your line that changes length: the errmsg
suggests that this is J33 (but it may be that the fault is created
earlier and its result becomes apparent in J33).
When the Row value changes from 99 to 100 (or 9=>10, 999+1000 &c)
the formula produced by the expression changes in length.
This causes QP, after performing one command, to look for the start of
the next command not at the beginning of the next, but at the last (or
earlier, depending on how many <99=>100>s there are) character of the
current command. That is where QP had previously determined that the next
command started, and that is where it will carry on.
Consequently, it does not find { but } or maybe a " or 9
- in your case, a zero (the last character in <10>).
When the macro encounters such a character, it types it as if you had
typed it, supposing that this is what you wish to do, as usually it is.
That puts QP into Edit mode, and macro command equivalents are indeed not
allowed in that context.
There is a workaround.
Have the Cell ("XX##") that calculates the command elsewhere in the
Sheet. Then after a {ReCalc...} use {BlockValues...} to copy the
up-to-date formula from "XX##" to the cell in the main macro path. It is
now a regular macro text command that does not change its length, and the
macro crosses from eg: 99 to 100 without fuss.
You could achieve a similar result by using (scribble):
@RIGHT{"0000"&@STRING(whatever,0),4)
which would keep the length of the command constant until the value
reached 10000. But it would make the command as typed out rather
unwieldy.
David Wheeler
lemoto wrote:
> David:
>
>>It works OK from A2..A9 but fails at A10. The contents of
>>cell A10 are changed to "0" and a dialogue appears which says "Command
>>equivalents not allowed in this context. Macro execution terminated.
>>{PutBlock}{A:J33}"
>
>
> I did warn of this:
>
I now have a macro which works fine if it is on the sheet containing the
data I wish to parse but will not work from my 'Macro' workbook. So I
thought of using a macro which copyies the parsing macro onto the
relevant sheet and then runs it but I'm having trouble with syntax.
Before doing the parsing I need to copy the data from the .csv file the
instrument creates (and which I don't wish to modify) into the next
empty sheet in the data workbook. I use
{SelectBlock A1..HE50}
{EditCopy}
{Activate "D:\MSData\Data_Files\Carbonate_Data_#101-200.qpw"}
{EditGoto?}
{EditPaste}
where D:\MSData\Data_Files\Carbonate_Data_#101-200.qpw is the current
data file.
The first line relies on there always being <51 samples in a batch of
data. The third line works correctly but I would prefer to put the name
of the current data file in a cell outside the macro and refernce that
cell. Something like
{Activate "&B2&"}
where B2 contains D:\MSData\Data_Files\Carbonate_Data_#101-200.qpw
I then use GoTo? to manually select the next blank sheet. Is there a
better option?
After that the macro does some formatting which works OK. Then I need
to place the parsing macro on the data sheet and run it.
I want to use something like
{BlockCopy E2..E13, A100..A112}
{A100}
where E2..E13 is on the macro sheet and A100..A112 is on the data sheet
(below my data).
Can someone please correct my syntax??
thank you,
David
PS Syntax is my major problem but I can't find any comprehensive guide
to it. Any ideas?
lemoto
David:
> I now have a macro which works fine if it is on the sheet containing the
> data I wish to parse but will not work from my 'Macro' workbook. So I
> thought of using a macro which copyies the parsing macro onto the
> relevant sheet and then runs it but I'm having trouble with syntax.
Yes, you can do that. That is what the macro I posted expected to be the
case. But it can be edited to work on whatever Sheet is in the foreground.
You need to check all the following ideas. (I always have to <s>.)
You would edit the macro so that addresses internal to the macro specify
its Sheet name.
{SelectBlock A1..HE50}
selects that block on the active sheet.
So if the macro needs to eg: store some value, you would use
{PutBlock "w", MacroSheet:A3}
> Before doing the parsing I need to copy the data from the .csv file the
> instrument creates (and which I don't wish to modify) into the next
> empty sheet in the data workbook. I use
>
> {SelectBlock A1..HE50}
> {EditCopy}
> {Activate "D:\MSData\Data_Files\Carbonate_Data_#101-200.qpw"}
> {EditGoto?}
> {EditPaste}
> The first line relies on there always being <51 samples in a batch of
> data.
Instead:
{SelectBlock A1}
{End}{Shift+Down}
- if the data are contiguous.
The third line works correctly but I would prefer to put the name
> of the current data file in a cell outside the macro and refernce that
> cell.
+"{Activate "&G9&"}"
> I then use GoTo? to manually select the next blank sheet.
+"EditGoTo "&@FIRSTBLANKPAGE(A1)&":A1}"
It has been reported that QP9ff in fact go to the first page after the last
used page, which I think will suffice here.
> After that the macro does some formatting which works OK. Then I need
> to place the parsing macro on the data sheet and run it.
> I want to use something like
>
> {BlockCopy E2..E13, A100..A112}
> {A100}
{BlockCopy E2..E13, A100}
I do not understand the purpose of {A100} - it does nothing.
> PS Syntax is my major problem but I can't find any comprehensive guide
> to it. Any ideas?
Use the Help files. The macro language was written by many people, so that
(as with the functions) there are some variations from what you will
identify as the norm. You will see that the semicolon, full stop/period and
underscore have consistently the same significances (though I rarely
remember what they are).
Ask here. I have to double-check everything, but there are folk around who
just roll out accurate and complex functions and commands with little
apparent effort.
--
Good wishes!
Roy Lewis
C_Tech volunteer
(UK)
lemoto
> the semicolon, full stop/period and
> underscore
Perhaps I should treble-check.-)}
For semicolon, read: comma.
David Wheeler
lemoto wrote:
> David:
>
>>I now have a macro which works fine if it is on the sheet containing the
>>data I wish to parse but will not work from my 'Macro' workbook. So I
>>thought of using a macro which copyies the parsing macro onto the
>>relevant sheet and then runs it but I'm having trouble with syntax.
>
>
> Yes, you can do that. That is what the macro I posted expected to be the
> case. But it can be edited to work on whatever Sheet is in the foreground.
>
> You need to check all the following ideas. (I always have to <s>.)
>
> You would edit the macro so that addresses internal to the macro specify
> its Sheet name.
> {SelectBlock A1..HE50}
> selects that block on the active sheet.
> So if the macro needs to eg: store some value, you would use
> {PutBlock "w", MacroSheet:A3}
>
E2..E3
@CELLPOINTER("row")
@STRING(E2,0)
and then in E10..E16
{SelectBlock P(0):A2}
{ReCalc E2..E17}
+"{IF P(0):A"&E3&"=""""}{RETURN}"
+"{PutBlock @FIELD(P(0):A"&E3&",1,""/""), P(0):B"&E3&"}"
+"{PutBlock @FIELD(P(0):A"&E3&",2,""/""), P(0):C"&E3&"}"
+"{IF P(0):C"&E3&"=""ERR""}{SELECTBLOCK P(0):C"&E3&"}{Del}{SELECTBLOCK
P(0):A"&E3&"}"
{Down}
{BRANCH E11}
I start the macro with the data file as the active (front) sheet. I
correctly moves the cursor to A2 on that sheet and the ReCalc changes
the value in E2..E3 to 2. However the IF statement in cell E12 looks at
cell A2 on the macro sheet and (since that is empty) the macro
terminates. If I put text in eg A2..A4 on the macro sheet execution
continues (nearly correctly) until it looks at A5. The statements in
E13..E14 work correctly (ie reference the active sheet), but E15 doesn't
work. It appears that the PutBlock commands correctly interpret P(0):
but the IF statments don't. I've tried putting an Activate statement
after the ReCalc to make sure the data file is active, but that doesn't
help.
Once again this is probably some syntax I don't understand.
Another matter. I don't understand the "w" in the {PutBlock "w",
MacroSheet:A3} command above.
cheers,
David
lemoto
David:
> OK I've tried this and still hid a snag.
What you describe (and what I see) is not what I expected.
Rather than dive into some macros here to check my memory, I suggest the cast-iron coding below.
Add in E5..E7:
@PROPERTY("Active_Block.Selection")
@CELLPOINTER("sheet")
@INDEXTOLETTER(E6-1)
In E10..E17:
{SelectBlock P(0):A2}
{ReCalc E2..E17}
+"{IF "&E5&"=""""}{RETURN}"
+"{PutBlock @FIELD("&E7&":A"&E3&",1,""/""), "&E7&":B"&E3&"}"
+"{PutBlock @FIELD("&E7&":A"&E3&",2,""/""), "&E7&":C"&E3&"}"
+"{IF "&E7&":C"&E3&"=""ERR""}{SELECTBLOCK "&E7&":C"&E3&"}{Del}{SELECTBLOCK "&E7&":A"&E3&"}"
{Down}
{BRANCH E11}
Beware wordwrap! (and the loss of a space that may ensue).
David Wheeler
lemoto wrote:
> David:
>
>>OK I've tried this and still hid a snag.
>
>
> What you describe (and what I see) is not what I expected.
> Rather than dive into some macros here to check my memory, I suggest the cast-iron coding below.
Sorry but that didn't work either. What you suggested correctly
returned the sheet name and cell location but not the file name.
Therefore the IF statements would look at eg F:A3 in the macro workbook,
not the data workbook. I've got around this by including a macro sheet
in the data workbook. I'm now coding happily and will post new problems
under their own thread.
thanks for trying,
David
lemoto
David:
> What you suggested correctly
> returned the sheet name and cell location but not the file name.
> Therefore the IF statements would look at eg F:A3 in the macro workbook,
> not the data workbook.
I did not realise that the macro was to operate in another file.
Using not Sheet:CellAddress but []Sheet:CellAddress might fix it.