Placing objects in Word using Excel as source of info 2

[gorty56]

Hi All,

I am wondering if anyone can help me, I am looking for a way to place objects on a page using Excel as a source of no of objects,size,text,etc. I have been told I have to use macros to simplify the task, but I need some help,

Phil

 

[white605]

I am looking for a way to place objects on a page

What kind of page?

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[gorty56]

Hi,

I am trying to arrange object using Excel data onto Word pages, the number and size of the objects vary as does the text content beneath them, but all the information is conyained in an Excel spreadsheet,

Phil

 

[SkipVought]

It just might help if you disclose some pertinent information.

Please post some data that you have in Excel and what you expect the results to be in Word.

Also do you have ANY code at all that you are currently working with?

Please answer both items above clearly, concisely and campletely. Let's not make this a guessing game.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gorty56]

Hi,

Attached is a small section of the spreadsheet that I am using, also attached are pdfs of the results I am trying to achieve. These were done with a dtp program: Serif PagePlus X3.Sorry about the website setup, bit of a rushed thing.

My idea is, if it is at all possible, to use the spreadsheet to be able to design pages (like the pdfs), putting in all the boxes and text in their respective positions, with the program able to position each item, and space them out in the pages. For instance, by using AD1 - AD12 from the sreadsheet, the program would automatically space things out as in pages 1 and 2.

I have seen a program that does what I want to do, but it doesn't use the spreadsheet that I have built. I also have plans for other spreadsheets as well.

As far as code, well I really am a complete novice at this and don't have any code as yet, perhaps someone could guide me please,

Phil

 

[SkipVought]

Your link did nothing to clarify your question.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gorty56]

Hi Skip,

I'm sorry,I'll try and clarify .

The result I am trying to achieve is as per the pages on the Home page in the link.

The data for the box sizes, no of boxes and the desciption to go in the text boxes and headers is contained in the Excel spreadsheet in page 2 of the link. Using this data, is it possible to write a program to position the boxes and text boxes and place in the relevant text boxes and headers the text contained in the relevant fields in the spreadsheet, for example the name of country from the Country column, description from Description column etc.

The positioning of the boxes and their size would depend on certain parameters such as the overall size of the work area, how close each box is from the others, size of box, number in a row and column.

As I mentioned, there is a program on the market, but it deals with a completely different spreadsheet, so I was wondering if, with the help of those more experienced in these matters,I could write a program to do it.

If you need anymore info, please let me know and I will do my best to forward it to you,

Phil

 

[SkipVought]

Is it so difficult to COPY a sample of the data you have on your sheet and PASTE it here, including column headings.

THEN with relation to this data, explain exactly what you expect to see in Word.

PLEASE!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gorty56]

Hi Skip,

Thanks for your patience, here is the spreadsheet:

Stamp_ID Country Cat No Specialist Simple Class_ID
Shape_ID Set_Title Description Face_Value
Issue_Day Issue_Month Issue_Year Colour Width Height
Watermark_ID Printer_ID Print Method Paper Type Gum
Type Plate/Cyl Number Perforation Sheet_Layout Print
Quantity Designer Variety


Aden



Aden AD1 1 1 1 1 Definitive
Dhow ½a 1 4 1937 Yellow-Green 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD2 1 1 1 1 Definitive
Dhow 9p 1 4 1937 Deep Grey Green 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD3 1 1 1 1 Definitive
Dhow 1a 1 4 1937 Sepia 35 28 Sideways
Multi Script CA De La Rue Recess 13
x 11¾ C 5 x 10

Aden AD4 1 1 1 1 Definitive
Dhow 2a 1 4 1937 Scarlet 35 28 Sideways
Multi Script CA De La Rue Recess 13
x 11¾ C 5 x 10

Aden AD5 1 1 1 1 Definitive
Dhow 2½a 1 4 1937 Bright Blue 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD6 1 1 1 1 Definitive
Dhow 3a 1 4 1937 Carmine Rose 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD7 1 1 1 1 Definitive
Dhow 3½a 1 4 1937 Dull Grey Blue 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD8 1 1 1 1 Definitive
Dhow 8a 1 4 1937 Pale Reddish Purple 35
28 Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD9 1 1 1 1 Definitive
Dhow 1r 1 4 1937 Brown 35 28 Sideways
Multi Script CA De La Rue Recess 13
x 11¾ C 5 x 10

Aden AD10 1 1 1 1 Definitive
Dhow 2r 1 4 1937 Orange Yellow 35 28
Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD11 1 1 1 1 Definitive
Dhow 5r 1 4 1937 Purple 35 28 Sideways
Multi Script CA De La Rue Recess 13
x 11¾ C 5 x 10

Aden AD11a 1 1 Definitive
Dhow 5r 1 4 1937 Bright Aniline Purple 35
28 Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD12 1 1 1 Definitive
Dhow 10r 1 4 1937 Pale Olive Green 35
28 Sideways Multi Script CA De La Rue
Recess 13 x 11¾ C 5 x 10

Aden AD13 1 1 2 Coronation
King George VI & Queen Elizabeth 1a 12 5 1937
Sepia 41 26 Multi Script CA De La Rue
Recess 13¾ x 14 C 5 x 16 De La
Rue

Aden AD14 1 1 2 Coronation
King George VI & Queen Elizabeth 2½a 12 5 1937
Light Blue 41 26 Multi Script CA De La Rue
Recess 13¾ x 14 C 5 x 16 De La Rue

Aden AD15 1 1 2 Coronation
King George VI & Queen Elizabeth 3½a 12 5 1937
Blue Grey 41 26 Multi Script CA De La Rue
Recess 13¾ x 14 C 5 x 16 De La Rue

Aden AD16 1 1 1 Definitive
Aidrus Mosque Crater ½a 19 1 1939 Yellowish Green
37 28 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 16

Aden AD16a 1 1 Definitive
Aidrus Mosque Crater ½a 7 1942 Green 37
28 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 16

Aden AD16b 1 1 Definitive
Aidrus Mosque Crater ½a 8 1948 Blue Green
37 28 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 16

Aden AD17 1 1 1 Definitive
Adenese Camel Corps ¾a 19 1 1939 Chocolate Brown
37 28 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 16

Aden AD17a 1 1 Definitive
Adenese Camel Corps ¾a 24 4 1946 Red Brown
37 28 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 16

Aden AD18 1 1 1 Definitive
Harbour 1a 19 1 1939 Pale Blue 37 28
Multi Script CA Waterlow & Sons Recess 12½ x 12½
L 5 X 16

Aden AD18a 1 1 Definitive
Harbour 1a 19 1 1939 Bright Blue 37 28
Multi Script CA Waterlow & Sons Recess 12½ x 12½
L 5 X 16

Aden AD19 1 1 1 Definitive
Ardenese Dhow 1½a 19 1 1939 Scarlet Red 37
29 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 16

Aden AD20 1 1 1 Definitive
Aidrus Mosque Crater 2a 19 1 1939 Sepia 37
29 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 16

Aden AD20a 1 1 Definitive
Aidrus Mosque Crater 2a 24 4 1946 Deep Sepia
37 28 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 16

Aden AD21 1 1 1 Definitive
Mukalla 2½a 19 1 1939 Deep Ultrmarine 37 29
Multi Script CA Waterlow & Sons Recess 12½ x 12½
L 5 X 16

Aden AD22 1 1 1 Definitive
Capture Of Aden 1839 3a 19 1 1939 Sepia & Rose
Carmine 37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 8 X 10

Aden AD23 1 1 1 Definitive
Mukalla 8a 19 1 1939 Orange 37 29 Multi
Script CA Waterlow & Sons Recess 12½ x 12½
L 5 X 16

Aden AD23a 1 1 Definitive
Mukalla 8a 19 1 1939 Red Orange 37 29
Multi Script CA Waterlow & Sons Recess 12½ x 12½
L 5 X 16

Aden AD24 1 1 1 Definitive
Capture Of Aden 1839 14a 15 1 1945 Sepia & Light
Blue 37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 8 X 10

Aden AD25 1 1 1 Definitive
Ardenese Dhow 1r 19 1 1939 Emerald Green 37
29 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 12

Aden AD25a 1 1 Definitive
Ardenese Dhow 1r 19 1 1939 Bright Green 37
29 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 12

Aden AD26 1 1 1 Definitive
Harbour 2r 19 1 1939 Indigo & magenta 37
29 Multi Script CA Waterlow & Sons Recess
12½ x 12½ L 5 X 12

Aden AD26a 1 1 Definitive
Harbour 2r 1 1944 Blue Black & Deep Magenta
37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 12

Aden AD27 1 1 1 Definitive
Adenese Camel Corps 5r 19 1 1939 Lake Brown & Olive
Green 37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 12

Aden AD27a 1 1 Definitive
Adenese Camel Corps 5r 1 1944 Lake Brown & Deep
Olive 37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 12

Aden AD28 1 1 1 Definitive
Capture Of Aden 1839 10r 19 1 1939 Sepia & Violet
37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 12

Aden AD28a 1 1 Definitive
Capture Of Aden 1839 10r Bright Sepia &
Bright Violet 37 29 Multi Script CA Waterlow & Sons
Recess 12½ x 12½ L 5 X 12



Unfortunately it loses some of it's formatting.

All the pages would have the main title 'KG6 Unsimplified' and the border.

The sub heading would come from 'Country' column,the positioning of this and 'Set Title', 'Date', 'Printer ID', 'Print method', 'Designer', and 'Watermark' would remain constant.

The size for the printed 'Boxes' and the number of printed 'Boxes' would determine where these would be positioned on the page, thus the positions and number of the decriptive text boxes under the printed 'Boxes'. The content of these text boxes would come from the spreadsheet columns for 'Face Value', 'Colour', 'Perforation' and where applicable, 'Variety'.

The remaining columns are not needed for this project.

Please refer to the pages in the 'Home Page' of the attached
for a visual representation. Any of the six pages should do.

I have just learnt that the program that is 'on the market' was written in Visual Basic, if that helps,

Phil

 

[SkipVought]

OK, well and good. But how does this data relate to what's in your Word document?

Please take the row of data that relates to the first object in your display and explain in detail how each column relates, including where the OBJECT comes from that you are placing in the document. I do not want to GUESS at what you intend.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, what code do you have so far?

Where are your ex[eriencing problems?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

There is nothing in your table that describes...

1. what the text objects and graphic objects TOP and LEFT properties are (ie, where does each START)

2. why are there empty rows in your table. This will make it more difficult to process properly.

Does this need to be done in Word? Why Word?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

If you are a novice, please I mean no offence, but doing what (I think) you are asking is way beyond novice.

First of all, doing this at all will require extremely clear and precise logic, worked out to a level of exactness I do not think you are at right now. Again, I mean no offence.

Second, Word is NOT a layout application! It could approximate what I think you are asking about, but it simply is not capable of - or at least not very easily, even for a very experienced Word/VBA person - the kind of placement precision you appear to want.

By all means give it a try, but this - at least as far as I can tell - is not trivial. You will need major help, and you are not responding with the clear and concise information required. Again, I mean no offence. I just think (as far as I can tell) this is a major project, and beyond your scope right now.

Maybe try something smaller, and easier, to get going.

"I am looking for a way to place objects on a page"

That alone is tricky. For starters, Word does not really have pages. You may think it does, it seems like it does...but it really doesn't.

Gerry

 

[gorty56]

Hi Skip,

I havn't got any code at all yet, I am a complete novice at this.

I designed the pages using a desk top publishing programme, Serif PagePlus X3, the graphic and text objects are constructed using the centres as the datums.

The empty rows are only there as an indicator that there were different issues or sets and these were the breaks between.They can be removed as necessary.

I only said using Word because it had been suggested to me that I would have to use macros. I now realise that the program will probably be written in Visual Basic. So, no it does not need to be done in Word.

That said, I am fast realising that I need a good knowledge of Visual Basic and programing, and that this sort of a project is way beyond my abiliy. I have also just read the post from 'fumei', and he has just confirmed it. Thanks Gerry, and no offence taken.

So I must accept the fact that I need to start with something a lot smaller and simpler, so Skip, thanks for your patience so far, but if I carried on, it would probably end up with you pulling your hair out and sticking pins in a wax doll image of me, and thanks Gerry for helping me realise the 'Bleeding Obvious', I'll go back to my DTP programme, I can use that.....

Keep up the good work Guys,

Phil

 

[fumei]

I'll go back to my DTP programme,"

BRAVO!!!!! Good for you.

That is what they are for. Word is a word processor, it is not a layout app. If you need serious layout, then use the proper application.

Actually, it is a good idea to use the proper application for anything...

Gerry