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 FunctionEd
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