Need some help with VBA (Word 2003)

Zeon

Ive got a couple problems with some little VBA macros ive been tagged to write for work. Basically i was asked to write a VBA macro that does 12 things, ive got 11 of them figured out, but the last one is kicking my ass.

Basically what i need to do is have the macro run through the file and pull all the header/footer text into the main body of the document. If possible, id like to have it replace the header/footer on each page that it appeared, but if i can only get it to extract it and place it on the first page it appeared, thats fine too.

Currently, ive been able to have the macro run through and pull the first header/footer it finds, and place them BOTH wherever the cursor happens to be when the macro is run. However, i cant for the life of me figure out how to have it put the header at the top, footer at the bottom, and move on to find the next header or footer and do the same thing there.

The second problem is minor (i think). Im trying to get the macros ive written to be accessible from the toolbar. However, when i create a new toolbar and try and put my macro in there, it is not selectable from the list. I know this feature isnt somehow locked out because the standard macros we have loaded on all the terminals are all selectable, however, anything new i create is not for some reason. The weird part is if i go to Tools -> Macro -> Run, all my macros show up and can be run from there. Does anyone know why this would be? Do I have to save it in a certain way for it to be accessable from the toolbar? Ive tried looking at the other macros that are loaded in for some sort of guidance, but i cant see anything thats different from mine.

Alternatively, if this isnt the best place to ask, can someone point me to a decent vba help forum? I tried searching around but most of the stuff i find are newsgroups and the like.

whuppins

Most of my experience with VBA is in Excel, but I might be able to help with some of this.

Regarding the toolbar thing, my first guess would be that the macros are being set up as private subroutines. This would keep them off the list. If your code for each macro starts out like:

Private Sub MyMacro()

or even just:

Sub MyMacro()

you should change it to:

Public Sub MyMacro()

As for the other problem, I don't know that there's a way for Word to work with individual printed pages as discrete units. There are lines, paragraphs, sections, rows, and columns, as well as "stories" which are basically the document as a whole. But perhaps because printed page counts can change from computer to computer and from printer to printer, there's not a lot of support for working with printed pages.

I think you could have a solution to at least put your data at the very beginning and very end of the document, but I'm not sure that would help you because it sounds like you're describing a document where different pages have different types of info in the headers and footers. Is this correct? Could you be a little more specific about how your headers and footers are currently set up?

Edit: Like I said, it probably won't help you, but since I mentioned it, the following lines will put your cursor at the very beginning and very end of the document, respectively:

Selection.HomeKey Unit:=wdStory

Selection.EndKey Unit:=wdStory

Zeon

Well, i already tried setting one of the macros to be public, and that didnt seem to help. Is there any other reason why they wouldnt show up?

As for the headers/footers, well, it really depends. The macro is going to be used to clean up files before we run them through another program, so how the headers/footers are set up is really going to depend on the file. They may be set up properly, oddly, or not even exist at all. In plain english though, what id like to have the macro do is this:

1) Find the first instance of a header or footer

2) Take note of what page the header/footer is on and whether it is a header or a footer

3) Cut the entire contents of the header/footer

4) Place cursor at top or bottom of page noted in step 2, depending if header or footer is found

5) Paste text

6) Loop until no more headers/footers exist

Now, from what ive found out so far, word treats both headers and footers differently, so i may have to write 2 different macros, one to deal with the headers and one to deal with the footers. Im ok with that.

Hell, even as a last resort, if anyone knows how to just plain turn OFF the headers/footers with a VBA macro, thatd be fine too, as long as theres pretty much no way to do what i originally want to do. Im just having problems finding any way to really address headers/footers as an entirety in word, they seem to really be like seperate entities.

And incase anyone isnt sure what i mean by headers and footers, theyre the grayed out text at the top/bottom of the page, that you need to double click to get into and edit. They look kinda like this:

----------------------------------
This is the text in the header, at the very top of every page, it will run through the document until stopped
----------------------------------

this is the main body text
this is the main body text
this is the main body text
this is the main body text
this is the main body text
this is the main body text
this is the main body text





----------------------------------
This is the text in the footer, at the very bottom of the page, it will run through the document until stopped
----------------------------------

whuppins

In Tools > Macro > Macros, change the drop-down box that says "Macros in:" to "Normal.dot (global template)". Do all the macros appear in the list (both the standard working ones and your new ones)? If not, under which selections do each group appear?

Edit: Also, try going to Tools > Customize, then clicking the Keyboard button. When you select the "Macros" category, do your macros show up in the list? If so, you can use this dialog to assign a new shortcut key combination (make sure you're saving changes to Normal.dot so your macros will be active for all the different files you'll be working with).

whuppins

Update: I have what may be a workable solution, but first I want to make sure we're clear on how headers and footers work in Word.

Word documents are made up of smaller units called sections. The author of a document is expected to break up his document into sections by manually inserting section breaks, which work pretty much the same as page breaks.

Each section -- not the document as a whole, as many people think -- has a single header and a single footer. This header and footer data is applied to all pages within that section. So if you're looking at a 22-page document where headers and footers seem to stop on page 6 and pick back up on page 13, what's probably happened is the author created three different sections: 1-5, 6-12, and 13-22. Sections 1 and 3 have header/footer data, and section 2 does not.

The document in the above example only has 3 sets of header/footer data. Even though there may be a different copy of the first section's header on each of the first 5 pages, Word still only treats it as a single entity for the most part. So when you mention going to the "next header", I'm a little confused as to what you're trying to accomplish. For example, in the above doc, do you want section 1's header/footer data to appear on each of the first 5 pages, or only on page 1 (where it is first found)?

I went ahead and assumed that you wanted each page that actually displays a header/footer to essentially move that value from the header/footer into the body text. In other words, header/footer info should show up not only on the first page of the section, but for all pages in the section. If I was wrong and you only want each header/footer to show up once, let me know. It makes things much simpler on the backend.

The code shown below goes through the following steps:

1.) Forces a view in which the window shows exactly one page length (This is a cheat to make navigating through printed pages easier -- as I mentioned before, there isn't much support for pages as discrete units in Word VBA).

2.) Puts the cursor at the beginning of the document.

3.) Un-hides header/footer data and stores the current page's header and footer text in memory.

4.) Re-hides header/footer data and writes the stored data to the top of the page.

5.) Pages down to the same position on the following page.

6.) Re-sets the cursor to the top of the current page.

7.) Repeats steps 3-6 for all pages in the document.

8.) Deletes header data for the first section of the document.

9.) Deletes footer data for the first section of the document.

10.) Repeats steps 8-9 for all sections in the document.

Here's the code:

ActiveWindow.View.Type = wdPrintView
ActiveWindow.ActivePane.View.Zoom.PageFit = wdPageFitFullPage
Selection.HomeKey Unit:=wdStory
curpg = 1
Do
    ActiveDocument.ActiveWindow.View.SeekView = wdSeekCurrentPageHeader
    Selection.WholeStory
    hdrtext = Selection.Text
    ActiveDocument.ActiveWindow.View.SeekView = wdSeekCurrentPageFooter
    Selection.WholeStory
    ftrtext = Selection.Text
    ActiveDocument.ActiveWindow.View.SeekView = wdSeekMainDocument
    Selection.TypeText Text:=Chr(13) & Chr(13) & hdrtext & Chr(13) & ftrtext & Chr(13)
    Selection.MoveDown Unit:=wdScreen, Count:=1
    Selection.MoveDown Unit:=wdWindow, Count:=-1
    curpg = curpg + 1
Loop Until curpg = ActiveDocument.ActiveWindow.Panes(1).Pages.Count + 1
cursec = 1
Do
    ActiveDocument.Sections(cursec).Headers(wdHeaderFooterPrimary).Range.Delete
    ActiveDocument.Sections(cursec).Footers(wdHeaderFooterPrimary).Range.Delete
    cursec = cursec + 1
Loop Until cursec = ActiveDocument.Sections.Last.Index + 1

This code has several limitations. It's some of the sloppiest VBA I've written, but I had to learn as I went and the end product seems to get the job done.

The major difference between what you requested and what this does is that it puts footer data at the top, beneath the header data. This is intentional, and it's not really due to a VBA limitation so much as it's due to the slippery way that Word will always shift your text around in an attempt to make pages break evenly. I can spend some more time trying to explain it if you like, but for now I'm hoping you just take my word for it: Trying to force text into the last line of each printed page would increase the complexity of this VBA by an order of magnitude. I'm sure there's some way to do it, but I'm not sure how at the moment.

So, except for that, this more or less does what I think you want. If you'd prefer to eschew footers altogether rather than place them at the top with the headers, just remove lines 9-11 and erase everything after the third "Chr(13)" in line 12. If the results are not good enough and you just want to clear all header data, just run the last 6 lines by themselves.

One last thing: This is obviously a 'quick and dirty' solution. If you want to do things like alter fonts and colors, or maybe insert a line between the pseudo-header/footer values and the 'real' body text, it can certainly be done in the VBA. If you have trouble with it, let me know and I can help you refine the code to get what you want.

Hope this helps. I know it's ugly and inefficient, but it may be about as good as it gets, given that inserting random lines into the body of your text will always wreak havoc on your page breaks, whether you're automating it through VBA or not.

Edit: I forgot to mention, another limitation of this is that it really only works for documents which are mostly text. If you throw it at a doc with large images, tables, etc. that can't be broken up across different pages, it probably won't work. Sorry, like I said, this is a really tricky problem to solve because every time you make a change, you're also changing the parameters that are telling you how the next change needs to be made.

Zeon

Whuppins, that is insanely helpful. I really appreciate you taking the time to help me out.

Yeah, i take it you guessed im pretty new at working with word/vba. Its true, i really suck at this too haha. I only realised last night at work after staring at the screen for 5 hours that you can only have 1 header/footer pair per section (disregarding first page headers) so it would be easiest to address it on a section by section basis. However, i couldnt for the life of me write the code to advance through the sections, let alone move the cursor through the sections. I really, really appreciate the help here this does almost exactly what i want and is about 20x better than what id written so far.

As for the macros, the problem is, they show up when i go to tools->macros->macros, and i can run them all from there. They DONT show up if i right click the menubar and go to customize, so i cant actually add them to any new menus or toolbars manually. However, i worked around this, to some degree, by writing an autoexec macro that creates a new temporary toolbar whenever the template is launched. This is apparently the standard way we load toolbars/menu items for word. I just gotta see if it works properly, because after 2 hours of fucking with the autoexec code, i read that word wont run autoexec macros from the template that youre opening, only templates in the STARTUP folder (which is retarded, because excel does no problem). And unfortunately for me, i dont have write priviledges to that folder on my terminal so i had to request them. I tried changing the path in the options menu, but it didnt really work, because it was using my new folder and the old folder to load templates. So my macro loaded, but so did about 100 others, twice, so im not really sure if id call that a success.

Zeon

Ok i just have one more quick question for anyone that knows. How the hell do i get Excel macros to automatically load with excel? Basically i have a macro that i need have available to ALL worksheets and workbooks. In Word, you write an autoexec script and save it as a template in the STARTUP folder. Of course, excel doesnt work this way. I tried save it as a template and putting it in XLSTART, that didnt work. I tried saving it as a regular workbook and saving it in XLSTART, that works except it also opens the workbook, which i dont want. I read that you could save the macros as an addin, but that doesnt sound like what i want to do because they may or may not be available to all workbooks? I couldnt find any clear documentation on that.

whuppins

Make sure your template file is named book.xlt, and that it's located in that XLSTART folder. There are ways to call different templates if you want to get fancy, but if you just want a default template that applies to everything, make sure you use book.xlt.

Zeon

Youre full of wicked information. Thanks a bunch, ill try that tonight when i go in.

Zeon

Ok, book.xlt didnt actually work. If i name it book.xls, it loads my macros, but it also opens the book.xls spreadsheet at the same time, which i dont want. I think it may have something to do with how im calling my macro out, currently i have it in the "This Workbook" section, and im calling it out as Private Sub Workbook_Open(). I think that means it only runs when the workbook is opened, which wont happen if im just loading the template... However i looked through the VBA help files, and i search for a good solid 3 hours online and cant find any other reference to auto running excel macros, since most people seem to be content with just opening up their workbooks, or only running a macro when a spreadsheet opens... i need mine to run when excel opens regardless of if a spreadsheet is being opened or not. It seems like im looking for a command like Template_Load() but fucked if i can find it.

This is literally the last peice of my project and its driving me absolutely nuts.

whuppins

Sorry, I had a long, computer-free weekend and I didn't think to check this thread to see if you got it working.

It turns out I was a bit rusty on Excel templating and I gave you some bad information. Book.xlt stores actual spreadsheet settings like column widths, fonts, and such. VBA is stored in personal.xls, in the same XLSTART folder. I think Excel will actually open anything named .xls in that folder (as you seem to have found out on your own), but for the sake of keeping things simple you should probably only have one .xls file there.

So, re-name your book.xls to personal.xls. Any code in personal.xls's Workbook_Open sub will now run when Excel starts up. As you mentioned, this means personal.xls itself will also open up on startup. There is no way around this, since any and all VBA code must be stored against some workbook or template -- you can't store VBA code against the program itself. Even add-ins are basically the same thing; .xla files that are opened when Excel runs.

There is an easy way around this, however. Once you've opened Excel and personal.xls has opened itself up, just switch to it and go to Window > Hide. Close Excel and save changes to personal.xls on the way out (note the special Save dialog for personal.xls). Once you've done this, personal.xls will always load itself 'behind the scenes' and execute whatever code is under Workbook_Open whenever you run Excel.

Aesthetically, your problem should be solved. You'll be able to run whatever macros you want at startup without any additional workbooks popping up. Technically, though, you'll always have personal.xls open; like I said, no way around that. I don't know the nature of your problem with additional workbooks loading, but if it was a technical one as opposed to an aesthetic one, I'm sure there's a solution to that too. If there's some technical aspect that is getting gummed up by having multiple workbooks loaded, let me know and I'll try to help.

Zeon

That will probably do exactly what i want, ill try that as soon as i get in tonight..

And yeah its definately an aesthetic thing, basically i want it to look to the end user like nothing has changed, because this has to be rolled out platform wide and i dont really want a barrage of emails asking "Whats this personal.xls file?!!?"

I appreciate the help ill let you know if it works. I also appreciate the PM letting me know you replied Thanks again.