llawrenceg
asked on
Embed 2 page Word Doc in Excel Worksheet
I am trying to embed a 2 page word document into an excel worksheet in a workbook
and am having a problem with the code.
Can you help
and am having a problem with the code.
Can you help
'This Code has the User open the CSR1 request form and
'then embeds it in a new excel sheet called CSR1 in the Fiscal Tool
Sub AddCSR1()
Dim FullFile As Variant
Dim work_book As Workbook
Dim last_sheet As Worksheet
Dim WS As Worksheet
Dim OLEWd As OLEObject
Dim WD As Document
Application.ScreenUpdating = False
Set work_book = Application.ActiveWorkbook
Set last_sheet = work_book.Sheets(work_book.Sheets.Count)
Set WS = work_book.Sheets.Add(After:=last_sheet)
WS.Name = "CSR1"
FullFile = Application.GetOpenFilename _
(" WORD files(*.doc),*doc", 1, "SELECT and OPEN the CSR1 File", , False)
If VarType(FullFile) = vbBoolean Then
MsgBox "No File Specified", vbExclamation
Exit Sub
End If
With OLEWd
Set OLEWd = ActiveSheet.WS.OLEObjects.Add(Filename:=FullFile, Link:=False, DisplayAsIcon:=False)
OLEWd.Verb xlVerbOpen
OLEWd.Name = "CSR1"
'OLEWd.Width = 400
'OLEWd.Height = 800
'OLEWd.Top = 30
Set WD = OLEWd.Object
End With
Set WS = Nothing
Set FullFile = Nothing
Set OLEWd = Nothing
Application.ScreenUpdating = True
End Sub
And Yes
To set a reference, In VBA Editor, Click on the Tools Menu~> References and then select the Microsoft Word Object xx.xx Library.
Sid
To set a reference, In VBA Editor, Click on the Tools Menu~> References and then select the Microsoft Word Object xx.xx Library.
Sid
ASKER
Sid:
My problem now is that the document I want to embed is 2 pages long and this code will only embed page one. Now if I click on the page to edit it it will open to 2 pages, however I need both pages to show wheen initially embedded
My problem now is that the document I want to embed is 2 pages long and this code will only embed page one. Now if I click on the page to edit it it will open to 2 pages, however I need both pages to show wheen initially embedded
I am not sure if you can do it.
There is an alternative though but might not be what you actually looking at. Insert two instances of the same Ole Object. In One show the 1st page and in the other show the other.
Sid
There is an alternative though but might not be what you actually looking at. Insert two instances of the same Ole Object. In One show the 1st page and in the other show the other.
Sid
ASKER
that approach seems more reasonable that the others I am looking into ... convert to PDF or convert to XML.
How would I code so that the second page would show up and the two would set next to each other or one below the other?
How would I code so that the second page would show up and the two would set next to each other or one below the other?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SID:
Thank you so much . I think I can figure out the rest from here
Thank you so much . I think I can figure out the rest from here
1) You need to set a reference to Microsoft Word Object Library for the line to work.
Dim WD As Document
2) There is an error in your code in this line
Set OLEWd = ActiveSheet.WS.OLEObjects.Add(Filename:=F
You are specifying the sheet twice.
Try this code
Open in new window
I have tested it and it works now :)
Sid