Help on inserting a table into a word doc by VBA from Excel 1

[Anna007]

I have this Amortization Table (a range with 5 columns and about 40 rows) that I need to insert into a word document, if some conditions are met. I don't know how to do this.
Please help me with this. I'm desaparately in need of help asap.

Thanx....

 

[TonyJollans]

Hi Anna,

Can you give a bit more information please.

You say you want to do this from Excel. Are you currently building the Word document from Excel and just want to know how to set up a table? Do you just want a snapshot of your Excel data in a Word table? Or what?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Anna007]

Hi Tony,

The Amortization Table is created in Excel. I have word documents separate from Excel that I have bookmarked and I populate the fields via VBA code. This is all working fine. However, I want to do a similar thing for this table but don't want to create soooo many bookmarks and then.. etc.
I have to just somehow insert this table into this word document that is not created via excel.
So, not sure what the property is. is it copy and paste or insert or.. ??
The table is dynamic also, so it could have anywhere btwn 30 to 40 rows.
So, I have 6 documents and if document 3 or 6 are selected, I need to insert this table on the document.
Hope this is enough information.

Thanx,
Anita

 

[PHV]

Have you tried to play with the macrorecorder in Word and/or Excel ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MattGreer]

I'm following this thread with great interest. I opened up Excel and started the macro recorder. I then opened up Word, went back to excel, copied a cell, went to Word, pasted it, and then stopped the recorder to see what had transpired. The only thing that was recorded was the copy.

I'm guessing that this is going to be more involved than just recording the macro in Excel, huh? I'll have to do some sort of file.open.new.word.document type of method and such, but then when I go to insert a table in Word, I'm going to have to use Word to do that, correct? Does Excel VBA provide facilities to draw tables in Word?

I apologize for hijacking the thread, if you want I'll start a new thread but this seemed to be on topic.

Thanks!!


Matt

 

[TonyJollans]

Hi Anita,

From the sound of it, you know how to access your Word Document from Excel, so all you need to do is copy the Excel Range (I assume you have a dynamic range name or similar) and paste it into Word, something like ..

[blue][i]YourExcelrange[/i].Copy
[i]YourWordDocument[/i].Bookmarks("[i]YourBokmark[/i]").Range.Paste[/blue]

and that should paste it as a Word table.

Please come back if that's not enough.

Matt,

Not so sure of what your situation is. Yes you can create Word tables from Excel but the copy/paste should do it automatically. Come back if you need more.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[MattGreer]

Well, what I'm looking to do is have it done automatically from Excel.

I have six columns of information in Excel; each row I want to be put into a table that does not have six columns. For example:

In Excel:
Company Name, Client Name, Client Location

Would then go into Word like this:
-empty cell- Company Name -empty cell- -empty cell-
-empty cell- Client Location -empty cell- -empty cell-
-empty cell- Client Name -empty cell- -empty cell-

And so on... That is not exactly how I want it done, but basically I'm creating a plan of customer visits in Excel, and then I want to export each row into a table in Word to which I will later add more information as I get things scheduled.

I hope that makes sense. I can get Word to spawn from Excel by using the following:

Sub CreateSchedule()
'
Dim WordDoc As Word.Application
    Set WordDoc = CreateObject("Word.Application")
    WordDoc.Visible = True
    Range("A2").Select
    Selection.Copy
'What code do I use to paste into Word?
'How do I draw a table in Word from Excel?
'How do I format the table so that some lines are 'clear'
'but others have color?
End Sub

But I don't know how to get the 'paste' into Word...

All help is greatly appreciated!

Thanks!!


Matt

 

[PHV]

But I don't know how to get the 'paste' into Word
I suggested to play with the macrorecorder in Word too ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Matt,

Without knowing exactly what reformatting you want it's hard to say exactly but, from your example, I doubt whether cutting and pasting is the right way to do it and I suspect the logic would be something like ..

For each row
If first time create table with 3 rows (and 4 columns)
Else add 3 rows to existing table
Populate the cells in the word table

Some (very) rough code for this would be ..

Dim wa As Object
Dim wd As Object
Dim wt As Object
Set wa = CreateObject("Word.application")
Set wd = wa.documents.Add
wa.Visible = True

For r = 1 To 10
If r = 1 Then
  wro = 0
  Set wt = wd.tables.Add(wd.Range, 3, 4)
Else
  wro = wt.Rows.Count
  wt.cell(wro, 1).Select
  wa.Selection.insertrowsbelow 3
End If
wro = wro + 1
wt.cell(wro, 2) = Cells(r, 1)
wt.cell(wro + 1, 2) = Cells(r, 2)
wt.cell(wro + 2, 2) = Cells(r, 3)
Next

wd.Close False
wa.Quit
Set wt = Nothing
Set wd = Nothing
Set wa = Nothing

Incidentally in your code you set variable Word[red]Doc[/red] to a Word Application - see my code (wa is the app and wd the doc)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Anna007]

Thank you, Tony. The 2 lines of code you gave me helped, except I had to do PasteSpecial Instead of Paste into word:
YourExcelrange.Copy
YourWordDocument.Bookmarks("YourBokmark").Range.PasteSpecial

Need help with this now:
Now, I have another question:
I have a table with 10 rows and 3 columns in a word doc again, that are book-marked with different bookmark id's for each field. So, they all hold different values like the table below:
After Populated, looks like this, for example:
Code Description Price
1 xxxx 10
2 yyyy 4
...
10 qqqq 6

So, now, I like to delete rows in the word doc that have no values in them.
So, either.. after the bookmarks have been populated, I can go through the doc and delete rows that are blank or I can do it while populating them.

Do you know how to do this?

Thanx soooooo much,
Anita

 

[Anna007]

I have another question.

When I populate my table in the word document via vba in excel, I am populating a field with an interest rate. Now, this field in Excel is formatted as Percentage, for example like 9.00%. But when I populate the book-marked field, it is as 0.09. This is not good. It needs to be formatted as 9.00%. How do I do this???
I'm desparate....

Thanx soooooooooooo much again.

Anna

 

[TonyJollans]

Hi Anna/Anita,

I like the way your name changes but I don't know how to address you

A very quick reply at the moment. If you're copying from and running from Excel, its probably easier to do your manipulation in Excel before you copy. Take a copy of your table within Excel (on a new sheet, perhaps) and delete the blank rows. Also change your percentage formatting to actually put the percentage (as text) in the cells before copying - unless you have a requirement for the numbers to be numbers in Word.

I will be back later (if the site is accessible - as there seem to be some problems at the moment) and will post more fully if need be - or answer any more questions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Phaed]

what u mean "bookmarked field"? if form field (which shows as bookmarks), can set format of form field by setting a Type:Number, and Number Format as %.

 

[Anna007]

Thank you, Tony again!
I guess it depends on my mood..
My name is Anita actually but pp call me Anna or Ani as well.. so, you can call me any of those..

Ok. I changed the formatting of my % field to text and it's fine now. Thanx soooo much!!

Now, my Amortization Table has 60 rows and 5 columns and I have to write VBA code to hide the zero rows of this table (so, only show the non-zero rows) and then, copy and paste it into word. This is what I need help with. I think this is the best way..

Thanx again,
Anna

 

[Anna007]

I wanted to let you know that I have been able to hide the zero rows in my Amortization table by:

For iCounter = 0 To 60
If Data.Offset(iCounter + 1, 0).Value = 0 Then
Data.Offset(iCounter + 1, 0).EntireRow.Hidden = True

End If
Next

Thanx anyways!

 

[TonyJollans]

Hi Anna,

You could also hide rows with an AutoFilter which would be more efficient if you had more rows but won't make any difference with 60.

I take it from your last post that you're all sorted now. Well done!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Anna007]

Yes, I thought about filtering actually but wasn't working for me so I decided to go this route.
Thanx for your help again.
I've not released yet and I will be in the next week sometime; so, I've been working hard. I'm still not all sorted but almost.
I'll post any more questions I might have then.

Thanx again,
Anna