Global Macros to be used on all Word Docs 2

[gtaborda]

Hi there

I have hundreds of Word Documents.

I am trying to run a couple of Macros on all of them, but manually.

I would like to put a couple of global buttons on the top part of Word, where all buttons etc are located or the Selection Bar(File, View, Tools, etc)
Makes no difference one or the other.

I have created the two macros VB code and put it on normal.dot


However I am now stuck, I don't know how to link the buttons/Bar selection to the macros so, when any Word document is opened, I can then push the button/select and the Macro will run.

Any ideas how to do this ?

Thank you

 

[gtaborda]

Hi there
Note that I have worked it out!

However my VB code doesn't work
Among other things, I have many references to tables in each Word Document like this:

xxxx = Trim(ActiveDocument.Tables(i).Cell(1, 1))

This code worked correctly if typed directly on each Word Document, however it doesn't seem to work when run from Normal.Dot

Any ideas how to refer in VB to the Currently Opened Word Document?

Thanks

 

[gtaborda]

No problem

I finally found a solution

thanks

 

[fumei]

Currently open does NOT mean Active.

The Active document is ActiveDocument.

But first, your macros. It would be better to put code into a global template, but NOT Normal.dot.

I have the majority of my procedures in a file (VBAXTools,dot) This is loaded as a global template. To load a global for ALL sessions of Word, put it in the Startup folder.

Globals may also be loaded dynamically. In other words, you can load and unload them at will.

Procedures (macros) in a loaded global are available for ANY document open in Word. Note that a loaded global does NOT load the file. The file is NOT open. Word loads the procedures and sets pointers to them. The file itself is NOT open. However, I put a button on the global toolbar (which IS loaded into every file) that opens the global file itself, so I can edit it if I choose. Note also any change made to a open global file are immediately available.

Back to your document.

The currently ACTIVE document is ActiveDocument. All other open documents are part of the Documents collection, and are easily accessible from there.

Regarding tables, while it is true you can access a table by its index number (in your case you are using the variable i), you can also loop through tables in a document by the use of objects.

Dim aTable As Table
For Each aTable In ActiveDocument.Tables
   xxxx = Trim(aTable.Cells(1,1))
   ' other stuff???
Next

This would loop through all tables resetting xxxx to be cell(1,1)

I would like to point out that Trim(ActiveDocument.Tables(i).Cells(1,1) will NOT remove the terminated ASCII characters Chr(13) and Chr(7) - the end-of-cell marker.

If you want to make sure you get only the text values, you may want to use a quick Function, like this.

Function CellText(strIn As String)
   CellText = Left(strIn, Len(strIn) - 2)
End Function

This explicitly strips off the end-of-cell marker. The parameter strIn is the Range of a cell.

xxxx = CellText(ActiveDocument.Tables(i).Cells(1,1))

You could, if required, also use Trim.

xxxx = Trim(CellText(ActiveDocument.Tables(i).Cells(1,1)))

I also as a matter of course, name my tables by using bookmarks. While you could adjust the following to give different names to tables, this makes bookmarks of all the tables in a sequential naming.

Sub NameAllTables()
Dim aTable As Table
Dim r As Range
Dim j As Long
If ActiveDocument.Tables.Count = 0 Then
   MsgBox "No tables in document."
   Exit Sub
Else
   j = 1
   For Each aTable In ActiveDocument.Tables
      Set r = aTable.Range
      ActiveDocument.Bookmarks.Add Name:="Table" & j, _
         Range:=r
      j = j + 1
   Next
End If
End Sub

Now each table is bookmarked, and named Table1, Table2, Table3.....

They can now be called directly, by name. It is also handy using Document objects.

Dim ThisDoc As Document
Dim Table6 As Table
Set ThisDoc = ActiveDocument
Set Table6 = ThisDoc.Bookmarks("Table6").
xxxx = Table6.Range.Cell(1,1).Range.Text

If you want to name the table individually, then use an InputBox to get allow the input of a name during the loop. Good error traping should also be included to check you are not making duplicates....


Again, of course you could loop through by using a Table object (For Each Table In....)

As another example, supposed you want the LAST cell value of the SECOND table of all the open documents, and put those into an array.

Sub GetLastCellCrap()
Dim LastCellArray()
Dim aDoc As Document
Dim aTable As Table
Dim r As Range
Dim j As Long
Dim var
[COLOR=red] ' for every open document[/color red]
For Each aDoc In Documents
[COLOR=red]' check if it has tables
' if not, go to the next document[/color red]
   If aDoc.Tables.Count > 0 Then
      [COLOR=red]' set a Range object for Table # 2[/color red]
      Set aTable = aDoc.Tables(2)
      [COLOR=red]' get the cell count[/color red]
      j = aTable.Range.Cells.Count
      [COLOR=red]' set Range object for the LAST cell[/color red]
      Set r = aTable.Range.Cells(j).Range
      [COLOR=red]' add to the array[/color red]
      ReDim Preserve LastCellArray(var)
      LastCellArray(var) = CellText(r.Text)
      var = var + 1
   End If
Next
Set r = Nothing
var = 0
[COLOR=red]' for demo purposes, display values
' found in the LAST cell of Table # 2
' in ALL open documents[/color red]
For var = 0 To UBound(LastCellArray)
   MsgBox LastCellArray(var)
Next
End Sub

I use this, along with FileSearch, to get retrieve values from folders and subfolders of DOC files. Here is a recent real world example.

I was asked to compile a list of documents (reports) that a value in a specific cell in a table. The value had to be greater than X, if it was less, it could be ignored. These documents are scattered through 9 folders, and 28 subfolders.

I could post the code, but that would spoil the fun, but in with partial code AND pseudo-code...

Sub ProcessFolders()
Dim ThisDoc As Document
Dim Source As Document
Dim ThisTable As Table
Dim NumberFiles As Long
Dim StartLocation As String
Dim aCell as Cell
Dim r As Range
Dim var
ThisDoc = ActiveDocument

StartLocation = "C:\FinanceReports"
With Application.FileSearch
   .NewSearch
   .LookIn = StartLocation
   .FileName = "*Gerry*.doc"
   .FileType = msoFileTypeAllFiles
   .SearchSubFolders = True
End With
NumberFiles = Application.FileSearch.Execute()
[COLOR=red]' this returns the number of files found with
' "Gerry" in the filename
' make a table (code not shown) with two columns, 
' and NumberFiles rows and make table object of it[/color red]
Set ThisTable = Selection.Tables(1)
For var = 1 To NumberFiles
   Source = Documents.Open _
      (FileName:=Application.FileSearch.FoundFiles(var), _
            Visible:=False)
[COLOR=red] ' IF logic HERE (not shown) to check 
' if found file DOES indeed have a table, if not, 
' close it and go to next found file
' if it DOES have tables, keep on processing[/color red]

[COLOR=red]' set cell object to each row
' remember this is the table in the NEW document
' the one that will hold the found data[/color red]
   Set aCell = ThisTable.Cell(var, 1)
   [COLOR=red]' make hyperlink to found document
' in Cell column 1[/color red]
   ThisDoc.Hyperlinks.Add _
      Anchor:=aCell.Range, _
      Address:=Application.FileSearch.FoundFiles(var), _
      TextToDisplay:=Source.Name
[COLOR=red]' put the found value of the searched cell in column 2[/color red]
   ThisDoc.Cell(var, 2).Range.Text  = CellText(ThisTable)
[COLOR=red]' close the file[/color red]
   Source.Close
Next
End Sub

The function CellText is a modified function like the one above. It takes a table object as a parameter and returns the corrected text for the specified cell.

What I ended up with - here is a short example - is a new document with a table listing AS HYPERLINKS, all documents across a number of folders and subfolders, every document that:

1. had "Gerry" in the filename
2. had Value X in a secified cell in a specified table

T:\FinanceReports\AnnualGerryTravel.doc[/color blue] $3127.69
T:\FinanceReports\AnnualGerryCapitalCosts.doc[/color blue] $6,127.69
T:\FinanceReports\AnnualGerryTravel.doc[/color blue] $3,127.69
T:\FinanceReports\January\GerryTravel.doc[/color blue] $527.69
T:\FinanceReports\January\GerryCapitalCosts.doc[/color blue] $200.00
T:\FinanceReports\February\GerryTravel.doc[/color blue] $1,127.69
T:\FinanceReports\February\ConferenceGerry.doc[/color blue] $8,671.33
T:\FinanceReports\Conference\Communications\YaddaGerry.doc[/color blue] $467.91

Each found file hyperlinked so they can just click and go to the original document.

Ummm.....the file structure and amounts are bogus BTW.

The point to all this spounting is that this code lives in a global template, NOT in normal.dot, so it is still available to any document I want.

Too much coffee people.....

And of course FileSearch can be used to search not just filenames, but content WITHIN a file as well.


faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Oh, crap....I did not realize this was NOT in the VBA forum.

Ummmm, sorry. That was waaaaaaay too much information.

My apologies.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Oh, and before you Excel snobs <grin> get a chance to say so...yes, yes, yes...Excel would be better for this kind of stuff.

faq219-2884

Gerry
My paintings and sculpture

 

[white605]

Reguardless or the forum you are posting in, you should have a star for all that into.!
wjwjr

 

[rlp89]

Gerry,
I think your in depth post should be a FAQ if it isn't already. There is a lot of valuable inoformation in there. There is also good detail about the proper construct and business rules for programming.