Delphi and MS Word VBA 1

[lespaul]

I have the following code, that allows a user to upload a user specified file. Before uploading, a unique number is generated. I need to print that unique identifier onto a label as a barcode. I have created a Word application that creates a new document. Then it creates another new document with the label format that I need to be able to print to. I need to select the cell at Row 1 Column 1 and put the first number from my barcode() array, I then need to move to row 2 column 1. There are only 20 labels in a column so when I reach 20 I check to see if I'm in the last usable column (7). If I am, I need to create another page of labels (still unknown how to accomplish that!). If I'm not in column 7, then add 2 to the column, reset the row back to 1 and continue filling in the table. Unfortunately, I keep getting error messages that Tables is not a method. I have tried calling it from my Word application variant and the document variant, but it just won't work! I have copied extensive examples from the help and still no love!! If there is anyone out there with OLE VBA knowledge, I beg you to share with me!! Thanks for any assistance!

(I previously was asking about doing this procedure with Delphi - but was completely lost! So I switched to Word - Thanks again for any help).

Leslie


procedure TfrmMainMenu.UploadVenire1Click(Sender: TObject);
var
F: TextFile;
S: string;
FullName, LastName: string;
j, i, k, JurNum: integer;
barcode: array[1..350] of integer;
Word, wrdDoc, wrdSelection, myCell, wrdTable: Variant;
begin
if OpenDialog1.Execute then { Display Open dialog box }
begin
AssignFile(F, OpenDialog1.FileName); { File selected in dialog }
Reset(F);
qryMaxJuror.Active := True;
JurNum := qryMaxJuror.FieldByName('JURNUM').AsInteger;
{JurNum := JurNum + 1;}
JurNum := 1;
for j := 1 to 350 do
begin
Readln(F, S); { Read first line of file }
FullName := Copy(S, 160,30); // extract name
i := Pos( ',', FullName);
qryVenireUpdate.ParamByName('LASTNAME').AsString := Copy(FullName,0,i-1);
qryVenireUpdate.ParamByName('FIRSTNAME').AsString := Copy(FullName, (i+2), (30 - Length(LastName)));
qryVenireUpdate.ParamByName('ADDRESS').AsString := copy(S,190,25); // extract address
qryVenireUpdate.ParamByName('CITY').AsString := copy(S,215,15); //extract city
qryVenireUpdate.ParamByName('STATE').AsString := copy(S,230,2);
qryVenireUpdate.ParamByName('ZIPCODE').AsString := copy(S,232,9);
qryVenireUpdate.ParamByName('SSN').AsString := copy(S,151,9);
qryVenireUpdate.ParamByName('TERMDATE').AsInteger := StrToInt ('20' + (copy(S,16,6)));
qryVenireUpdate.ParamByName('ORGPOST').AsInteger := StrToInt('20' + (copy(S,16,6)));
qryVenireUpdate.ParamByName('JURNUM').AsInteger := JurNum;
qryVenireUpdate.ParamByName('STATUSCD').AsString := 'NR';
qryVenireUpdate.ParamByName('UPDATE').AsString := 'F';
qryVenireUpdate.ParamByName('PUBEMP').AsString := 'F';
qryVenireUpdate.ExecSQL;
barcode[j] := JurNum;
JurNum := JurNum + 1;
end;
CloseFile(F);
Word := CreateOleObject('Word.Application');
Word.Documents.Add();
Word.Visible := True;
wrdDoc := Word.MailingLabel.CreateNewDocument (Name:='5167');
wrdDoc.Activate;
wrdTable := wrdDoc.Tables(1);
j := 1;
i := 1;
k := 1;
for j := 1 to 350 do
begin
for i := 1 to 20 do
begin
myCell := wrdTable.Cell(Row:=i, Column:= k);
myCell.Font.Name := 'Code 39';
myCell.Font.Size := 14;
myCell.Value := barcode[j];
end;
if i = 20 then
begin
if k = 7 then
begin
k := -1;
end;
i := 1;
k := k + 2;
end;
end;
end;
end;

 

[svanels]

I suggest that you look at How to export data to Excel faq102-1562 in the FAQ Area.

Don't focus on excell but the method using the macro recorder.

Hope this helps

Steven van Els
SAvanEls@cq-link.sr

 

[RSi2]

brrr, ugly code
in the step-by-step mode ... on wich line the error occurs ?

 

[lespaul]

Sorry it's ugly, I started using Delphi about 2 weeks ago and I'm still trying to figure it all out! Doesn't help that I'm trying to run VBA (which I barely know) from inside a program that I know not at all!!! I did finally figure out most of this! So, my delphi program opens word, creates a new document, adds a table with 7 columns (same set up of label 5167 auto format) on the first page, and fills in the first column of label fields with the correct barcode[j] array item. Only two difficulties remain with the table construction - the Row Height needs to be 1/2" and the alignment needs to be centered, but I was having real problems trying to get the syntax correct on those two VBA calls. My programming problem is that it doesn't move to the second label column (actually the third column in the table). So, what I need to do now is if my row count = 20 and my column count is less than 7, reset the row count to 1 and the column count to +2. If the column count is 7, then I need to reset both row & column to 1 ON A NEW PAGE WITH THE SAME LABEL FORMAT! So below is more ugly code (LOL), if you have any suggestions for making my code beautiful , please pass it along - I need all the Delphi help I can get because at this point I get to do the happy programmer dance very rarely!

Thanks!

procedure TfrmMainMenu.UploadVenire1Click(Sender: TObject);
var
F: TextFile;
S: string;
FullName, LastName: string;
j, i, k, JurNum: integer;
barcode: array[1..350] of integer;
Word, wrdDoc, wrdSelection, myCell, wrdTable: Variant;
begin
if OpenDialog1.Execute then { Display Open dialog box }
begin
AssignFile(F, OpenDialog1.FileName); { File selected in dialog }
Reset(F);
qryMaxJuror.Active := True;
JurNum := qryMaxJuror.FieldByName('JURNUM').AsInteger;
{JurNum := JurNum + 1;}
JurNum := 1;
for j := 1 to 350 do
begin
{Readln(F, S);} { Read first line of file }
{FullName := Copy(S, 160,30); // extract name
i := Pos( ',', FullName);
qryVenireUpdate.ParamByName('LASTNAME').AsString := Copy(FullName,0,i-1);
qryVenireUpdate.ParamByName('FIRSTNAME').AsString := Copy(FullName, (i+2), (30 - Length(LastName)));
qryVenireUpdate.ParamByName('ADDRESS').AsString := copy(S,190,25); // extract address
qryVenireUpdate.ParamByName('CITY').AsString := copy(S,215,15); //extract city
qryVenireUpdate.ParamByName('STATE').AsString := copy(S,230,2);
qryVenireUpdate.ParamByName('ZIPCODE').AsString := copy(S,232,9);
qryVenireUpdate.ParamByName('SSN').AsString := copy(S,151,9);
qryVenireUpdate.ParamByName('TERMDATE').AsInteger := StrToInt ('20' + (copy(S,16,6)));
qryVenireUpdate.ParamByName('ORGPOST').AsInteger := StrToInt('20' + (copy(S,16,6)));
qryVenireUpdate.ParamByName('JURNUM').AsInteger := JurNum;
qryVenireUpdate.ParamByName('STATUSCD').AsString := 'NR';
qryVenireUpdate.ParamByName('UPDATE').AsString := 'F';
qryVenireUpdate.ParamByName('PUBEMP').AsString := 'F';
qryVenireUpdate.ExecSQL;}
barcode[j] := JurNum;
JurNum := JurNum + 1;
end;
CloseFile(F);
Word := CreateOleObject('Word.Application');
Word.Visible := True;
wrdDoc := Word.Documents.Add();
wrdSelection := Word.Selection;
wrdDoc.PageSetup.RightMargin := InchesToPoint(0.28);
wrdDoc.PageSetup.LeftMargin := InchesToPoint(0.28);
wrdDoc.PageSetup.TopMargin := InchesToPoint(0.5);
wrdDoc.PageSetup.BottomMargin := InchesToPoint(0.0);
wrdSelection.Font.Name := 'Code 39';
wrdSelection.Font.Size := 14;
wrdDoc.Tables.Add(Range:=wrdSelection.Range, NumRows:=20, NumColumns:=7);
wrdDoc.Tables.Item(1).Columns.Item(1).SetWidth(InchestoPoint(1.75),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(2).SetWidth(InchestoPoint(0.31),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(3).SetWidth(InchestoPoint(1.75),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(4).SetWidth(InchestoPoint(0.31),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(5).SetWidth(InchestoPoint(1.75),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(6).SetWidth(InchestoPoint(0.31),'wdAdjustNone');
wrdDoc.Tables.Item(1).Columns.Item(7).SetWidth(InchestoPoint(1.75),'wdAdjustNone');
wrdSelection.Tables.Item(1).Rows.Alignment := 'wdAlignRowCenter';
{wrdDoc.Tables.Item(1).Rows.SetHeight RowHeight:=InchesToPoint(0.5), HeightRule:='wdRowHeightExactly';}
wrdDoc.Tables.Item(1).Borders.InsideLineStyle := False;
wrdDoc.Tables.Item(1).Borders.OutsideLineStyle := False;
k := 1;
j := 1;
for i := 1 to 20 do
begin
myCell := wrdDoc.Tables.Item(1).Cell(Row:=i, Column:= k);
myCell.Select;
myCell.Range.Text := barcode[j];
j := j + 1;
end;
if i > 20 then
begin
if k = 7 then
begin
k := -1;
end;
i := 1;
k := k + 2;
end;
end;
end;

 

[RSi2]

For the alignment

Cell.Range.ParagraphFormat.Alignment = wdAlignParagraphJustify
or wdAlignParagraphCenter

Rem: wdAlignParagraphCenter = 1
justify = 3

For the rows heght :

Table.Rows.Height = 20 //20 points
Table.Rows.DistributeHeight // same height for all the cell in the table

TIP:
open Object Explorer (to see the syntaxes for this things) , u can find the object explorer in word/excel/access ... View -> Tollbars -> Visual Basic
go in the Visual Basic Editor ... on this toolbar you'll find the Object Explorer
PS. maybe you'll have to go to Utils -> References
and check the Microsoft Word 8.0 .....

hope i didn't scared u

 

[RSi2]

For the alignment

Cell.Range.ParagraphFormat.Alignment = wdAlignParagraphJustify
or wdAlignParagraphCenter

Rem: wdAlignParagraphCenter = 1
justify = 3

For the rows height :

Table.Rows.Height = 20 //20 points
Table.Rows.DistributeHeight // same height for all the cell in the table

TIP:
open Object Explorer (to see the syntaxes for this things) , u can find the object explorer in word/excel/access ... View -> Tollbars -> Visual Basic
go in the Visual Basic Editor ... on this toolbar you'll find the Object Explorer
PS. maybe you'll have to go to Utils -> References
and check the Microsoft Word 8.0 .....

hope i didn't scared u

 

[lespaul]

RSi2 - I don't scare easily - if I did I'd never have learned how to program!! Thanks for the Row Setting help! I am very familiar with the Visual Basic Editor, my VBA programming would be severely handicapped if I couldn't record a macro and see the code!!!

Any suggestion on the new page and change column problems?

Thanks and have a great day!

Leslie

ps feel free to contact me with any other helpful suggestions at: landrews@metrocourt.state.nm.us

 

[lespaul]

RSi2 - Can you tell me where to find what value each of the constants for the parameters (ie wdAlignParagraphCenter = 1
justify = 3). I've looked through the VBEditor help and can find a few of the constant values, but not very many.

Thanks!

Leslie

 

[lespaul]

One last thing!!!

Everything (but one) works!! And I'm sure it's REALLY UGLY UGLY code!! But I don't care!

I am trying to close the Word and wrdDoc variants when I'm done printing and keep getting the "Save As" dialog box! I have tried setting:
Word.DisplayAlerts = false;
Word.DisplayAlerts = 0;
wrdDoc.close.SaveChanges = 0;

and several other things! does anybody know the correct syntax!?

Since this was the only question I decided to spare RSi2 the ulginess!!(LOL) If you'd really like to see the code, let me know!

Thanks!
Leslie

 

[RSi2]

try wrdDoc.Close(0):
or wrdDoc.Close(False);

 

[RSi2]

btw, your e-mail don't work

 

[lespaul]

Thanks for the code help! Do you do lots of VBA from Delphi? I have another Delphi procedure that opens an existing document and the Word Document has a VBA sub that does a find and replace function on two variables that I need to pass from Delphi (ie - Delphi has date = 08/19/2002 and the word sub needs that input so it can find the word date in the document and replace it with 08/19/2002) Where in the Word Project Structure do I need to place that sub and what would the syntax be (if I can indeed call it from Delphi) or should I just put the find and replace code into Delphi like I did with the label stuff?

What happened when you tried the e-mail? I haven't had any other problems. And I checked it, it's right in the posting. Why don't you try again?

Thanks!

leslie

 

[lespaul]

The wrdDoc.close(0) worked! Thanks!

leslie