Paste each page in a word document to a cell in excel

[scottmmckay]

ok, I need to find the best way to get each page of a word document
into it's own cell in excel...

I don't know if this requires a macro or if there is another way to
automate this. I have roughly 80 or 90 Doc files with around 50 - 1000
pages per...

any suggestions?

 

[Jezebel]

Do you mean 'page' as data (ie a unit of content) or as a graphic element?

 

[scottmmckay]

Do you mean 'page' as data (ie a unit of content) or as a graphic element?

at every page break

 

[Doug Robbins - Word MVP]

You should be able to modify the following code:

Sub splitter()
'
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each page of a document
' as a separate file with the name Page#.DOC
'
Dim Counter As Long, Source As Document, Target As Document
Set Source = ActiveDocument
Selection.HomeKey Unit:=wdStory
Pages = Source.BuiltInDocumentProperties(wdPropertyPages)
Counter = 0
While Counter < Pages
Counter = Counter + 1
DocName = "Page" & Format(Counter)
Source.Bookmarks("\Page").Range.Cut
Set Target = Documents.Add
Target.Range.Paste
Target.SaveAs FileName:=DocName
Target.Close
Wend
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[scottmmckay]

Well, I've gotten to this point...

but it's pasting images wrather than the text into cells which is what
I would like...

any suggestions?


Sub splitter()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim Counter As Long, Source As Document, Target As Document
Set Source = ActiveDocument
Selection.HomeKey Unit:=wdStory
Pages = Source.BuiltInDocumentProperties(wdPropertyPages)
Counter = 0
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or


While Counter < Pages
Counter = Counter + 1
DocName = "Page" & Format(Counter)
Source.Bookmarks("\Page").Range.Cut


With xlWB.Worksheets(1)
For i = 1 To 1
xlApp.Cells(i, 1).Insert
xlApp.Cells(i, 1).PasteSpecial Values = True

Next i
End With




Wend

End Sub

 

[Doug Robbins - Word MVP]

Use:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim i As Long, Source As Document
Set Source = ActiveDocument
Selection.HomeKey Unit:=wdStory
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
Set xlSht = xlApp.Sheets(1)
xlApp.Visible = True
For i = 1 To Source.BuiltInDocumentProperties(wdPropertyPages)
xlSht.Range("A" & i).Value = Source.Bookmarks("\Page").Range.Text
Source.Bookmarks("\Page").Range.Cut
Next i
Set xlSht = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

But you must realise that the maximum amount of text that can be inserted
into a cell is 1028 characters. That's the value in Excel 2007. It may be
less in earlier versions.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP