How to copy header from Word to Excel

This will loop through Word docs in a folder, open them, then copy and paste the header, content and footer into Sheet1 of the Excel file. Each time the code pastes the contents, the code will find the last row with data, move to that row, then copy + paste and repeat. Feel free to update to your needs.

Dim ws As Worksheet Private Sub DocTextToExcel() Dim i As Long Dim j As Long Dim WordApp As Object Dim WordDoc As Object Dim WordDocName As String Dim WordDocFullName As String Const DocPath = "C:\Users\Desktop\Data\" Application.ScreenUpdating = False Set WordApp = CreateObject("Word.Application") WordApp.Visible = False Set ws = Sheet1 WordDocName = Dir(DocPath & "*.doc") While WordDocName <> "" WordDocFullName = DocPath & WordDocName WordApp.Documents.Open Filename:=WordDocFullName Set WordDoc = WordApp.ActiveDocument For i = 1 To WordDoc.Sections.Count For j = 1 To WordDoc.Sections(i).Headers.Count WordDoc.Sections(i).Headers(j).Range.Copy Call pasteToExcel Next j WordDoc.Content.Copy Call pasteToExcel For j = 1 To WordDoc.Sections(i).Footers.Count WordDoc.Sections(i).Footers(j).Range.Copy Call pasteToExcel Next j Next i WordDoc.Close savechanges:=False WordDocName = Dir Wend WordApp.Quit Set WordDoc = Nothing Set WordApp = Nothing Application.ScreenUpdating = True End Sub Function pasteToExcel() Dim lastRow As Long lastRow = 0 On Error Resume Next lastRow = ws.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row On Error GoTo 0 If lastRow < 1 Then lastRow = 1 Else lastRow = lastRow + 1 End If Application.Goto ws.Range("A" & lastRow), Scroll:=True Application.Wait Now() + TimeSerial(0, 0, 2) ws.PasteSpecial Format:="Unicode Text", _ Link:=False, DisplayAsIcon:=False End Function

Ed

unread,

Mar 18, 2009, 7:45:35 PM3/18/09

to

I've set up an Excel 2008 spreadsheet to print Column & Row Headings.
I now want to paste this spreadsheet into a Word 2008 file with the
Column & Row Headings visible. I've tried various copying and pasting
routines, but none work.

How can accomplish this seemingly simple task?

Thanks for your help.

Ed

CyberTaz

unread,

Mar 19, 2009, 12:34:46 AM3/19/09

to

Sorry, Ed, but I don't think you'll get that to happen. The Headings bars,
scroll bars, etc. are a part of the window structure not the data area. Thus
they din't get copied. Your only options are to use a screen capture or take
the tome & trouble to set it up for PDF output.

HTH |:>)
Bob Jones
[MVP] Office:Mac

On 3/18/09 7:45 PM, in article
, "Ed"

Ed

unread,

Mar 19, 2009, 7:17:27 AM3/19/09

to

On Mar 19, 12:34 am, CyberTaz <> wrote:
> Sorry, Ed, but I don't think you'll get that to happen. The Headings bars,
> scroll bars, etc. are a part of the window structure not the data area. Thus
> they din't get copied. Your only options are to use a screen capture or take
> the tome & trouble to set it up for PDF output.
>
> HTH |:>)
> Bob Jones
> [MVP] Office:Mac
>
> On 3/18/09 7:45 PM, in article

> , "Ed"

>
> <> wrote:
> > I've set up an Excel 2008 spreadsheet to print Column & Row Headings.
> > I now want to paste this spreadsheet into a Word 2008 file with the
> > Column & Row Headings visible. I've tried various copying and pasting
> > routines, but none work.
>
> > How can accomplish this seemingly simple task?
>
> > Thanks for your help.
>
> > Ed

Thanks. Ed

macropod

unread,

Mar 19, 2009, 11:11:01 AM3/19/09

to

Hi Ed,

As Bob says, you can't do that. However, if you're prepared to paste the Excel data into Word as a Word table, there is a way to
simulate what you're after:
1. Insert the table
2. Add a new 1st row to the table
3. Insert A, B, C for the column headins
4. Add a new left column to the table
5. number from the 2nd row down.

You can then shade the left column & top row to simulate the Excel row/column headings.

If you prefer, you can use SEQ fields to generate the row/column headings:
For the 1st column, code the field as: {SEQ Col \* alphabetic \* Upper}
For the 2nd row, colde the field as: {SEQ Row}
Then simply copy across/down, respectively. When you've finished, select the table and press F9.

Note: The field brace pairs (ie '{ }') for the above example are created via Cmd-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]


"Ed" <> wrote in message news:...

Carl Witthoft

unread,

Mar 19, 2009, 5:03:44 PM3/19/09

to

There is a different way, but it requires you to paste a Picture rather
than an editable table. I happen to be of the opinion that this is a
far better way, because it avoids getting the Excel workbook embedded
into the Word doc, but that's more or less a matter of preference (and
lack of security).

So, in Excel, use PageSetup to "print row/column headers" , then (at
least prior to 2008), hold the shift key and select Edit/CopyPicture,
click "as printed" and you're all set.


In article <>,
"macropod" <> wrote:

> Hi Ed,
>
> As Bob says, you can't do that. However, if you're prepared to paste the
> Excel data into Word as a Word table, there is a way to
> simulate what you're after:
> 1. Insert the table
> 2. Add a new 1st row to the table
> 3. Insert A, B, C for the column headins
> 4. Add a new left column to the table
> 5. number from the 2nd row down.
>
> You can then shade the left column & top row to simulate the Excel row/column
> headings.
>
> If you prefer, you can use SEQ fields to generate the row/column headings:
> For the 1st column, code the field as: {SEQ Col \* alphabetic \* Upper}
> For the 2nd row, colde the field as: {SEQ Row}
> Then simply copy across/down, respectively. When you've finished, select the
> table and press F9.
>
> Note: The field brace pairs (ie '{ }') for the above example are created via
> Cmd-F9 - you can't simply type them or copy & paste
> them from this message.

--
Team EM to the rescue! //www.team-em.com

CyberTaz

unread,

Mar 19, 2009, 6:45:40 PM3/19/09

to

Yep - the feature is still there in 2008, I just didn't think of it :-}
Thanks for recharging the memory bank!

Regards |:>)


Bob Jones
[MVP] Office:Mac

On 3/19/09 5:03 PM, in article
, "Carl Witthoft"

Ed

unread,

Mar 19, 2009, 7:03:10 PM3/19/09

to

On Mar 19, 5:03 pm, Carl Witthoft <> wrote:
> There is a different way, but it requires you to paste a Picture rather
> than an editable table.  I happen to be of the opinion that this is a
> far better way, because it avoids getting the Excel workbook embedded
> into the Word doc, but that's more or less a matter of preference (and
> lack of security).
>
> So, in Excel,  use PageSetup to "print row/column headers"  , then (at
> least prior to 2008), hold  the shift key and select Edit/CopyPicture,  
> click "as printed" and you're all set.
>

> In article <>,

>
>
>
>  "macropod" <> wrote:
> > Hi Ed,
>
> > As Bob says, you can't do that. However, if you're prepared to paste the
> > Excel data into Word as a Word table, there is a way to
> > simulate what you're after:
> > 1. Insert the table
> > 2. Add a new 1st row to the table
> > 3. Insert A, B, C for the column headins
> > 4. Add a new left column to the table
> > 5. number from the 2nd row down.
>
> > You can then shade the left column & top row to simulate the Excel row/column
> > headings.
>
> > If you prefer, you can use SEQ fields to generate the row/column headings:
> > For the 1st column, code the field as: {SEQ Col \* alphabetic \* Upper}
> > For the 2nd row, colde the field as: {SEQ Row}
> > Then simply copy across/down, respectively. When you've finished, select the
> > table and press F9.
>
> > Note: The field brace pairs (ie '{ }') for the above example are created via
> > Cmd-F9 - you can't simply type them or copy & paste
> > them from this message.
>
> --
> Team EM to the rescue!    //www.team-em.com

Carl,
The approach you suggest no longer works in Office 2008.
Ed

How do I import a header into Excel?

On the Insert tab, in the Text group, click Header & Footer. Excel displays the Page Setup dialog box. Click Custom Header or Custom Footer. Use the buttons in the Header or Footer dialog box to insert specific header and footer elements.

How do you copy and paste a header in Excel?

In the Header & Footer Tools section, click on Edit Header or Edit Footer. Select all of the text and images in the header or footer. Press Ctrl + C (Windows) or Command + C (Mac) to copy. Click on the worksheet tab for the sheet you want to paste the headers/footers into.

How do I copy a letterhead from Word to Excel?

How to Put Letterheads in Excel.
Check the file extension of the file containing the letterhead you wish to use for your Excel spreadsheet. ... .
Launch Excel. ... .
Browse your computer to locate your letterhead file, select it, then click "Insert." Your letterhead appears in your spreadsheet within a few seconds..

How do I copy an entire header in Word?

Use the controls in the Navigation group to display the header or footer you want to copy. Select all the elements (text and graphics) in the header or footer. Press Ctrl+C. This copies the header or footer information to the Clipboard.

Postingan terbaru

LIHAT SEMUA