Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
I have a routine that opens an existing Word Doc, pastes (special) a metafile
picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
' NOTE here you are using WordApp:
WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ' But below, you stop referring to WordApp ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage ... Once you stop referring to WordApp, VBA assumes you are referring to the active Application, i.e. Excel. The ActiveDocument actually errors (I am guessing) because it is specific to Word; but your OnError statement will supress the errors here and elsewhere. In other places, i.e. in referring to Selection, VBA thinks you mean the Excel selection. But if you run the code in Word, where the active Application is Word, it would work OK. If you use WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I think the code will run OK. "Robin" wrote: I have a routine that opens an existing Word Doc, pastes (special) a metafile picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
I was having the problem earlier with the code activating the shapes on the
spreadsheet, but I resolved that. I step through the code to make sure that the WordApp is selected, and the the shape in the doc is selected. To test it I changed the code to reference WordApp.ActiveDocument, etc., and am still having the same problem. I wonder if the code for the Word methods is incompatible with Excel. I am using all 2000 versions. -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC "K Dales" wrote: ' NOTE here you are using WordApp: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ' But below, you stop referring to WordApp ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage ... Once you stop referring to WordApp, VBA assumes you are referring to the active Application, i.e. Excel. The ActiveDocument actually errors (I am guessing) because it is specific to Word; but your OnError statement will supress the errors here and elsewhere. In other places, i.e. in referring to Selection, VBA thinks you mean the Excel selection. But if you run the code in Word, where the active Application is Word, it would work OK. If you use WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I think the code will run OK. "Robin" wrote: I have a routine that opens an existing Word Doc, pastes (special) a metafile picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
I don't think compatibility should be an issue. When you use the Word
methods you are using the Word object library (.olb file) and the library does not care what app is calling it. I still think it is necessary to use WordApp before any Word properties or methods - it is not the Windows "active" app that matters, it is the application that is actually running the code that determines the "default" properties and methods of the Application object. So without specifying, VBA will use the Excel methods, not Word. The problem, then, is elsewhere in the code. Stepping through it will not really resolve it since you still bypass error checking with the OnError Goto Next statement. Without the OnError statement, does the code error out, and if so where and what is the error message/code? There is another thing I often do to test code: put a break point in right after inserting the shape, then try the other lines one by one by typing them in the immediate pane. You can try many variations of this or you can test the object properties as you go to see what is really happening. "Robin" wrote: I was having the problem earlier with the code activating the shapes on the spreadsheet, but I resolved that. I step through the code to make sure that the WordApp is selected, and the the shape in the doc is selected. To test it I changed the code to reference WordApp.ActiveDocument, etc., and am still having the same problem. I wonder if the code for the Word methods is incompatible with Excel. I am using all 2000 versions. -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC "K Dales" wrote: ' NOTE here you are using WordApp: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ' But below, you stop referring to WordApp ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage ... Once you stop referring to WordApp, VBA assumes you are referring to the active Application, i.e. Excel. The ActiveDocument actually errors (I am guessing) because it is specific to Word; but your OnError statement will supress the errors here and elsewhere. In other places, i.e. in referring to Selection, VBA thinks you mean the Excel selection. But if you run the code in Word, where the active Application is Word, it would work OK. If you use WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I think the code will run OK. "Robin" wrote: I have a routine that opens an existing Word Doc, pastes (special) a metafile picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
Robin
I don't understand Word and its fascination with the Selection object, but this works. You only need to get a proper reference to the shape. If you know it's the only shape, the Shapes(1) will work. If not, you'll need to count the shapes before you paste, then add 1. The constant you used in the DataType argument didn't exist in my Word 9.0 Object Library. Sub PastePicture() Dim wdApp As Word.Application Dim wdDoc As Word.Document Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Add wdApp.Visible = True Sheet1.Pictures(1).Copy wdApp.Selection.PasteSpecial link:=False, _ DataType:=wdPasteMetafilePicture, _ Placement:=1, _ DisplayAsIcon:=False With wdDoc.Shapes(1) .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage .Height = 606.25 .Width = 483.85 .Left = wdShapeCenter End With End Sub -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Robin wrote: I was having the problem earlier with the code activating the shapes on the spreadsheet, but I resolved that. I step through the code to make sure that the WordApp is selected, and the the shape in the doc is selected. To test it I changed the code to reference WordApp.ActiveDocument, etc., and am still having the same problem. I wonder if the code for the Word methods is incompatible with Excel. I am using all 2000 versions. ' NOTE here you are using WordApp: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ' But below, you stop referring to WordApp ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage ... Once you stop referring to WordApp, VBA assumes you are referring to the active Application, i.e. Excel. The ActiveDocument actually errors (I am guessing) because it is specific to Word; but your OnError statement will supress the errors here and elsewhere. In other places, i.e. in referring to Selection, VBA thinks you mean the Excel selection. But if you run the code in Word, where the active Application is Word, it would work OK. If you use WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I think the code will run OK. "Robin" wrote: I have a routine that opens an existing Word Doc, pastes (special) a metafile picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange problem running Word from Excel
Thanks to both Dick K and K Dales. It works!!
-- Mr. Robin Sayler JIT Warehousing & Logistics, LLC "Dick Kusleika" wrote: Robin I don't understand Word and its fascination with the Selection object, but this works. You only need to get a proper reference to the shape. If you know it's the only shape, the Shapes(1) will work. If not, you'll need to count the shapes before you paste, then add 1. The constant you used in the DataType argument didn't exist in my Word 9.0 Object Library. Sub PastePicture() Dim wdApp As Word.Application Dim wdDoc As Word.Document Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Add wdApp.Visible = True Sheet1.Pictures(1).Copy wdApp.Selection.PasteSpecial link:=False, _ DataType:=wdPasteMetafilePicture, _ Placement:=1, _ DisplayAsIcon:=False With wdDoc.Shapes(1) .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage .Height = 606.25 .Width = 483.85 .Left = wdShapeCenter End With End Sub -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Robin wrote: I was having the problem earlier with the code activating the shapes on the spreadsheet, but I resolved that. I step through the code to make sure that the WordApp is selected, and the the shape in the doc is selected. To test it I changed the code to reference WordApp.ActiveDocument, etc., and am still having the same problem. I wonder if the code for the Word methods is incompatible with Excel. I am using all 2000 versions. ' NOTE here you are using WordApp: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ' But below, you stop referring to WordApp ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage ... Once you stop referring to WordApp, VBA assumes you are referring to the active Application, i.e. Excel. The ActiveDocument actually errors (I am guessing) because it is specific to Word; but your OnError statement will supress the errors here and elsewhere. In other places, i.e. in referring to Selection, VBA thinks you mean the Excel selection. But if you run the code in Word, where the active Application is Word, it would work OK. If you use WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I think the code will run OK. "Robin" wrote: I have a routine that opens an existing Word Doc, pastes (special) a metafile picture from Excel, and attempts to modify the properties of the picture. After the paste the picture is sized at 89%. The code is actually selecting the pasted picture, but none of the methods have any effect on the size and orientation of the picture. My goal is to size the picture to 100% and center it in the Word doc. (Interestingly, when I paste an Enhanced metafile, the picture is sized at 100%, but the result is a huge file.) The code being executed from Excel, which follows, does work when executed from Word! Here is the guts of the code: WordApp.Visible = True On Error Resume Next With WordApp.Selection .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _ Placement:=1, DisplayAsIcon:=False End With ActiveDocument.Shapes.SelectAll With Selection.ShapeRange .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage Selection.ShapeRange.Height = 606.25 Selection.ShapeRange.Width = 483.85 Selection.ShapeRange.Left = wdShapeCenter End With ActiveDocument.SaveAs Filename:=SaveDocName, FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _ SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _ False -- Mr. Robin Sayler JIT Warehousing & Logistics, LLC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Problem in EXCEL | Excel Worksheet Functions | |||
Strange Excel Problem..... | Excel Discussion (Misc queries) | |||
Strange Excel problem | Setting up and Configuration of Excel | |||
Strange Excel Problem | Setting up and Configuration of Excel | |||
Strange Excel problem | Setting up and Configuration of Excel |