PDA

View Full Version : Solved: Count number of cells and export to Word



LinkND
06-05-2008, 05:42 AM
I have a piece of code to import a sheet to Word. To do this, I am using an exisiting document, because I need to use this document as a template for later on. That's why I coded this into my macro with FilePath and FileName.


Sub ProcessWord()
'Dims.
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim FilePath As String
Dim FileName As String
'Path.
FilePath = ThisWorkbook.Path
FileName = "ManagementRapportage.doc"
'File.
Set wApp = New Word.Application
wApp.Visible = True
Set wDoc = wApp.Documents.Open(FilePath & "\" & FileName)
'Copy
ThisWorkbook.Sheets("Sheet1").Copy
wApp.Selection.PasteExcelTable False, False, False
End Sub

This code isn't really important to fix the following:

In my attached workbook I want to count the ammount of cells and put the total, using counta, below each block. If this small macro is finished for the whole column, I want to export this to Word.

Well, exporting it isn't really an issue (because I already tested this), but how do you tell Excel to export it to, let's say, page 3 of my Word file (and if possible), at the center of this page.

I posted this in the Excel forum because it's a macro I am using to filter data from Excel to Word, not counterwise. So correct me if I am wrong.

Thanks in advance.

mdmackillop
06-05-2008, 06:17 AM
Can you set up bookmarks in your document? If so you can paste into the required positions more easily.

LinkND
06-05-2008, 06:41 AM
Can you set up bookmarks in your document? If so you can paste into the required positions more easily.

Ah yes, adding bookmarks to my document is a way. Those are simple destination references, right? So, if I put my bookmark at the end of page number 3, I can tell Excel to copy (a sheet or chart I made) to that destination?

That would be very cool, because I need to have fixed destinations in my report. We are producing a report every month, so we will use this document as a template to export charts to (to the same destinations).

mdmackillop
06-05-2008, 07:01 AM
Record a macro in word pasting into bookmarks then copy it into excel and tweak it to suit. Also check out the KB.

LinkND
06-05-2008, 07:32 AM
Alright.. I'll try that out later on, if I am ready to export. Thanks for the tip.

As a second: could you give me some directions to fix the macro regarding my first question. This refers to the workbook in the attachement; counting cells in a column and displaying the number (total ammount of cells) at the end of each block.

mdmackillop
06-05-2008, 10:07 AM
Sub Counts()
Dim Rng As Range, Col As Long
Col = ActiveCell.Column
Set Rng = Cells(Rows.Count, Col).End(xlUp)
Do Until Rng.Row = 1
If Rng.Offset(-1) <> "" Then
Rng.Offset(1).Formula = "=counta(" & Rng.Address & ":" & Rng.End(xlUp).Address & ")"
Set Rng = Rng.End(xlUp).End(xlUp)
Else
Rng.Offset(1).Formula = "=counta(" & Rng.Address & ":" & Rng.Address & ")"
Set Rng = Rng.End(xlUp)
End If
Loop
End Sub

LinkND
06-10-2008, 01:18 AM
Thank you =) this works very nice.

Talking about the word file and the export: I managed to make this work by using the code from DRJ. But this isn't how I want my workbook and document to be handled.

My document, as you can see in the code, already exists. I gave it some bookmarks on different pages, because the whole file is already generated by hand (template). But this time I want to export charts from my Excel file to the positions in the document.

For example: I have bookmark 1, 2 and 3 on pages 1, 2 and 3 in Word. In my Excel file I have chart 1 and 2 on sheet 1 and chart 3 on sheet 2. By using a macro I want to automatically copy the charts to a bookmark I defined.

So, if I want chart 1 to be positioned on page 2, chart 2 on page 3 and chart 3 on page 1, the code needs to do this. The best way to do this is to search my whole workbook (all sheets) until the code finds Chart 1, if he finds it, he can copy it to the desired location in my document. After that, looping to Chart 2, etc... until all charts are copied.

When I managed to copy a chart to a bookmark, the chart wasn't in position though. I wanted to have this chart in the middle of page number x, but he placed it at the top of that page. Can it work with different positions? I mean, real positions (like: http://www.geekpedia.com/gallery/fullsize/Microsoft%20Word%202007.jpg) at the center of a page with text above and below it.

--- EDIT/UPDATE ---

After trying to think of something to make this happen, I came up with something simple.


Option Explicit

Sub ProcessCopyToWord()
'Vastellen van types in het geheugen voor later gebruik in de code.
Dim xWrd As Object
Dim xDoc As Object
Dim FilePath As String
Dim FileName As String
'Gooi updating uit voor de snelheid van de verwerking.
Application.ScreenUpdating = False
'Definieer paden van de te gebruiken bestanden, waaronder het standaard Word-document.
FilePath = ThisWorkbook.Path
FileName = "ManagementRapportage.doc"
On Error Resume Next
'Maak een Word-document en open het bestaande template-bestand voor de transacties.
Set xWrd = CreateObject("Word.Application")
Set xDoc = xWrd.Documents.Open(FilePath & "\" & FileName)
On Error GoTo 0
'Kopieer chart naar het document.
xDoc.Bookmarks("ChartTopPin1").Select
ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 3").Copy
xWrd.Selection.Paste
'Gooi updating weer aan.
Application.ScreenUpdating = True
xWrd.Visible = True
'Sluit Word af.
Set xWrd = Nothing
Set xDoc = Nothing
End Sub

To position the chart correctly, I used a textbox in Word with a white line and centered. The above code copies chart 3 in my workbook and places this on the bookmark in my document. It took me a while to figure this out, because he never copied something. If someone thinks the code is bad, please tell me ^^

The only downside to this code: if I want 10 charts to be copied to 10 different bookmarks, I need to place this part ten times in my code, which makes it kind of easy coded, instead of professional:



'Kopieer chart naar het document.
xDoc.Bookmarks("ChartTopPin1").Select
ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 3").Copy
xWrd.Selection.Paste