CODEWITHSUNDEEP
excelvbapdfms-word
MT32
MT32

Reputation: 677

Copy only certain pages from word doc into excel using VBA

1) I open a pdf using Microsoft word, through excel VBA.

2) From the word doc, I wish to copy only page 3 and page 4 (these two are tables without captions) into excel

3) at the moment, I could only copy the entire word doc into the excel, which can be troublesome.

below is my code:

Sub convertpdftowordthenexcel()

Dim wordapp As Word.Application
Dim input1 As String
input1 = "C:\Users\Me\Desktop\Fruitjuice.pdf"

'open pdf in word
Set wordapp = New Word.Application

wordapp.documents.Open Filename:=input1, Format:="PDF Files", ConfirmConversions:=False
wordapp.Visible = True
'copy the content of the word file
wordapp.ActiveDocument.Content.Copy     '<------this is where I want to change

'go to excel and paste it there
Workbooks("openpdfusingdoc.xlsm").Worksheets("Sheet1").Activate
Worksheets("Sheet1").Activate
Cells(1, 1).Select
ActiveSheet.PasteSpecial Format:="Text"

wordapp.Quit savechanges:=wdDoNotSaveChanges

End Sub

Any suggestion on how to do this?

Thanks so much guys!

Upvotes: 0

Views: 1385

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4714

You can access tables through the tables collection - you may need to workout what index number the two you want are, I've assumed they're the first two in the document

 Sub convertpdftowordthenexcel()

 Dim wordapp As Word.Application
 Dim input1 As String
 input1 = "C:\Users\Me\Desktop\Fruitjuice.pdf"

 'open pdf in word
 Set wordapp = New Word.Application

 wordapp.documents.Open Filename:=input1, Format:="PDF Files", ConfirmConversions:=False
 wordapp.Visible = True
'copy the first two tables of the word file
 wordapp.ActiveDocument.tables(1).range.Copy     

 'go to excel and paste it there
 with Workbooks("openpdfusingdoc.xlsm").Worksheets("Sheet1")
     .Cells(1, 1).PasteSpecial Format:="Text"
     wordapp.ActiveDocument.tables(2).range.Copy    
    .cells(.rows.count,1).end(xlup).offset(2,0).pastespecial format:="Text"
  end with
  wordapp.Quit savechanges:=wdDoNotSaveChanges

 End Sub

(PS Never use Select)

Upvotes: 1

Related Questions