fill table in MS-words by using data from ms-excel

ashu khan · **Thanked:** 0 time(s) **Posted:** Thu Sep 17, 2015 10:57 am

please anybody :ugeek:

give me suggestion on how can i make macro
so that i can fill table in MS - WORDS,
using data from MS - EXCEL,
:?:

Syrifina · **Thanked:** 1 time(s) **Posted:** Thu Sep 17, 2015 8:55 pm

Here's an example that shows the easiest, but longest way to use BEM to accomplish this...I'm using this example because it's the easiest. If your more experienced with BEM, you can use other instructions/criteria that your comfortable with to push/pull data.

Also, your script is dependent on how many columns/rows you are using, but it's easily changed (or automated if your more experienced). This example uses a simple 3 column by 3 row Excel spreadsheet. It pulls the data from the first row, one column at a time, pastes it into Word (tabbing in between to simulate your use of tables), then presses enter to start pulling from the next row...and cycles through the next 2 rows.

Code:

 begin
     Window.Bring to front("Excel", "no")
     Window.Set location("Excel", "no", "125", "25")
     Variable.Set("x", "183")
     Variable.Set("y", "222")
     Variable.Set("Count", "1")
     Macro.Pause("100")
     begin loop("9")
          Function.Execute("Copy")
          Function.Execute("Paste")
     end
 end

function("Copy")
     begin
          Window.Bring to front("Excel", "no")
          Macro.Pause("100")
          if  Variable.Is greater than (Math)("Count", "3")
               begin
                    Variable.Set("x", "183")
                    Variable.Add (Math)("y", "20")
                    Variable.Set("Count", "1")
               end
          Mouse.Click at coordinate("{x}", "{y}", "Left")
          Macro.Pause("100")
          Keyboard.Press keys at once("{<lcontrolkey>}c")
          Macro.Pause("100")
          Variable.Add (Math)("x", "70")
     end
function

function("Paste")
     begin
          Window.Bring to front("Document", "no")
          Mouse.Click at coordinate("1603", "679", "Left")
          Keyboard.Press keys at once("{<lcontrolkey>}v")
          Macro.Pause("250")
          Keyboard.Press key("{<tab>}")
          Variable.Add (Math)("Count", "1")
          if  Variable.Is greater than (Math)("Count", "3")
               begin
                    Keyboard.Press key("{<enter>}")
               end
     end
function

Set up a 3 column/3 row spreadsheet in Excel, open Word, and give it a try. It's written for 1920x1080 resolution. Give it a try and see if it works for you or if you have any questions.

ashu khan · **Thanked:** 0 time(s) **Posted:** Sat Sep 19, 2015 6:44 pm

thank you Syrifina :ugeek:

but this macro didn't work well on my system...

it appears to be copying from excel but didnt paste anything in ms words

i have to make lots of certificates for my school in ms-words using data from ms-excel....
my data is in the form of 8 coloumn and 50 rows at least

like 1 student data in 1 row

plz suggest me something........ :?:

Syrifina · **Thanked:** 0 time(s) **Posted:** Sat Sep 19, 2015 8:38 pm

First you just need to figure out why it's not pasting in Word. The main issue is probably when BEM switches between the windows, the cursor is not on the Word page so you need to click on the page somewhere to be able to type or (in this case) paste. This should actually be better for you since your using tables...use Investigator to find the coordinates of the table and click in that before you paste.

Then just modify the code for your use. It sounds like you are only going to be using 1 column per row (the name of the person) or maybe 2 (first and last name). Then you could either automate it to save the certificate (or print), then erase and add the next person...or page down (or however you have it set up for the 50 certificates).

There's lots of features in BEM to help you out with this. Window location is important so you don't have to change coordinates and you can set it up for both documents...and you can use it in the future without having to do any setup, it will be automated for you. You can also automate dragging (mouse.drag) and erasing names, or double-clicking (mouse.double click) the table and erase, or tabbing between the tables (keyboard.press key {<tab>}).

ashu khan · **Thanked:** 0 time(s) **Posted:** Sun Sep 20, 2015 2:37 pm

thankyou
this BEM worked :idea:

little editing helped me......

can u plz suggest me ............
how to set coordinates in ms-words
so that data from excel can be pasted on required location :?:

Syrifina · **Thanked:** 1 time(s) **Posted:** Sun Sep 20, 2015 7:00 pm

Use the Investigator tool in BEM. You can find buttons to open it from any part of BEM and it will pull up an smaller link at the end of lines which you could use it...see image 3. The small icon at the end is a link to the investigator tool and that one will automatically fill in your coordinates for you.

When you launch the Investigator tool, just hit ctrl-s or the print-screen button and it will take a screen shot. After Investigator has a screen shot, you can click anywhere on it and it will tell the colors and location (coordinates) of where you clicked (at the bottom). In your example your interested in the coordinates, but for some scripts you can BEM find colors, pixel patterns, or images. If you used the small icon (from image 3), a "import" button will be displayed beside the coordinates...clicking on that will automatically import your coordinates to your script.

Blue Eye Macro

fill table in MS-words by using data from ms-excel