Controlling Page Setup in Word From Excel. 1

[xlStar]

I am still stuggling here.

I am pasting over many object from Excel into Word by using Excel's VBA.

xlHelp have kindly suggested I "activate" into Word to change the Orientation settings to Landscape (or vice versa). But, I am confuse on how to just only change the new page (created by page break) to a different orientation settings. Currently its changing the whole document!


ActiveDocument.Range(Start:=Selection.Start, End:=ActiveDocument. _
Content.End).PageSetup.Orientation = wdOrientLandscape

The above code is recorded macro, the 'Selection.Start' isn't quite right. Basically, I need a "SelectCurrentPage"

 

[rdodge]

First, when you are wanting to control an application other than the one that the code is in, you must refer to the external application directly. To do that more efficiently, you need to create an Application variable like:

Dim WDApp as Word.Application

Sub WDPageSetup()
Set WDApp = New Word.Application
WDApp.ActiveDocument.Range(Start:=Selection.Start, End:=WDApp.ActiveDocument. _
Content.End).PageSetup.Orientation = wdOrientLandscape
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[xlhelp]

try using

Selection.Information(wdActiveEndPageNumber)

in place of selection.start

http://www.geniusmedia.ca

 

[xlhelp]

xlStar

It woul;d be really good idea to continue the thread you started in the first place rather than create a new one.

Then myself and people such as rdodge can see what has been done before.

http://www.geniusmedia.ca

 

[xlStar]

xlHelp no it hasn't work. (I am new in this Forum).

I have very little experince on VBA Word but fairly good on VBA Excel. I am struggling with VBA options etc.

I have many excel file's showing "reports" from many departments, each file contained three reports in which one of them is in Landscape format (and of many pages long).

The aim is to transfer all the reports into one MS Word file; last year was done manually containing 122 pages.

I've started be creating a table in Excel showing filenames of where the reports are (.xls), step by step for each file, picks up a PrintArea and psate that into Word as paste special (MS Excel workbook), then resize.

Next is where I struggle, CHANGE ONLY THIS PAGE TO LANDSCAPE or PORTRAIT. I have used a pagebreak to go to next page. When I "stop" my macro halfway, the selections in Word is highlighting the whole document rather than selection active page and using "this point forward" option for page set-up (oreintation).

Can anyone help? (MS Office 2000)

 

[xlhelp]

I must have been sleeping, etc, when I replied. My line of code above only gives the current page number.

Anyway, xlstar, do you not have to insert a section break in your document? Word only orients sections, not pages.

So, this is (apporximately) what you need to do. (Not tested)

Dim crSc as String
.
.
.
.
Set crSc =Selection.Information(wdActiveEndSectionNumber)
ActiveDocument.Sections(crSc).PageSetup.Orientation = wdOrientLandscape
.
.
.

Don't forget to combine suggestions from your other thread and one from rdodge above.

http://www.geniusmedia.ca

 

[xlStar]

xlhelp, as I am in-expereince on MS Word, you have actually got me on the right track by stating that Word uses Section instead of Page for Orientating.

After struggling with pasting the picture in right page instead of last page, I have managed to achieve my target now, I don't even need to switch over to Word now. See part of code that use Landscape settigns...


'Add new new page and set previous page to Landscape
WDApp.Selection.InsertBreak Type:=wdSectionBreakNextPage
WDApp.Selection.GotoWhat:=wdGoToSection, _
Which:=wdGoToPrevious, Count:=1, Name:=""
WDApp.Selection.PageSetup.Orientation = wdOrientLandscape

'Copy over the Excel Print Area
Range(Cells(1, SrtC), Cells(42, EndC)).Copy
WDApp.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteOLEObject

'Resize the Report on Word
WDApp.Selection.ShapeRange.ScaleHeight 0.94,msoTrue, _
msoScaleFromMiddle
WDApp.Selection.ShapeRange.ScaleWidth 0.94, msoTrue, _
msoScaleFromMiddle
WDApp.Selection.ShapeRange.RelativeHorizontalPosition = _
wdRelativeHorizontalPositionMargin
WDApp.Selection.ShapeRange.RelativeVerticalPosition = _
wdRelativeVerticalPositionMargin
WDApp.Selection.ShapeRange.Left = wdShapeCenter
WDApp.Selection.ShapeRange.Top = wdShapeCenter

'Goto Next Page
WDApp.Selection.Goto What:=wdGoToSection,
Which:=wdGoToNext, Count:=1, Name:=""




Sometimes, just sometimes the macro crashes stated that there are nothing to paste, to over come this I move to debug (yellow) line back one step to start the copy option again and it works fine. How can I stop this?

 

[xlhelp]

Where is the yellow debug line when the macro stops?

http://www.geniusmedia.ca

 

[rdodge]

Your code for the most part looks as though it's using Active objects (I.e. Not prequalifying your Range object, thus refers to the active worksheet of the active workbook, using the Selection Object in Word application), maybe even the Activate/Select Method, but none the less, using these things in general are highly discouraged cause they do create issues. Don't get me wrong, their are uses for these objects/methods, but should not be used unless it's really needed. This could be part of the reason why you are getting the issue that you have with regards to why the PasteSpecial method in Word is failing out on you.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[xlStar]

WDApp.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteOLEObject

 

[rdodge]

Oh, one other issue you may be running into as I didn't think about it before, you may need to put in a bit of delay time. The reason being, the code may be trying to execute the paste before it has fully copied the information as I have ran into this sort of issue before (copying date from MS Project and pasting into Excel)

How I resolved this sort of issue, I was able to just put in a "DoEvents" function.

In your code above, insert a line of code between your Copy line and PasteSpecial line with:

DoEvents

Post back and let us know if this worked for you or not as I have another idea, but that's going to take some timing guess work.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[xlStar]

rdodge, I have done as what you said, it happened once this time! The Run Time Error is 4605; "This meoth or property is not available because the Clipboard is empty or not valid".

To overcome this (currently) I push the yellow line back to the 'copy' line.

I suppose the macro need to check is Clipboard not empty before continuign with the PasteSpecial method.

 

[xlhelp]

Something like this will ignore the error

.
.
.
On Error GoTo ErrHandler
.
.
.
ErrHandler:
If Err <> 0 Then
Err.Clear
Resume Next
End If

http://www.geniusmedia.ca

 

[rdodge]

If the code is truely copying something into the clipboard, and you are still getting the empty clipboard error, then you could give the following a try. First, setup a declaration statement in the global section of your module with the following line:

Private Declare Sub Sleep Lib &quot;kernel32&quot; (ByVal dwMilliseconds As Long)

Once you have set that up, you can use the Sleep procedure to cause the system to delay for how ever milliseconds you set the argument to such as the following:

Sleep 5500 'This causes the system to sleep for 5.5 seconds to allow any and all previous actions to be completed during this time before attempting to start executing the next command.

The above should work as long as:

You are not concerned about the monitor being refreshed during the time period

And you provide ample amount of time for the copy operation to complete.

Reason for doing this, I have found in my experience, there are times when a command is attempting to be executed before a previous command has been completed, as this has been confirmed by a microsoft tech person. I also found that DoEvents works most of the time, but there has been situations where it doesn't do the job as the main times when I found it doesn't do the job so well is when working across multiple programs.

In my program that I have code controlled in Excel and getting data from Project, the DoEvents worked well with the copying bit, but when it came to closing out Project and then moving onto the next project, it didn't do the job, and the only resolution I found to the issue was to use the above sleep command that I provided in this example.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[xlStar]

Thanks for all your help.

I have manage to complete the whole macro now. On the Clipboard problem, I manage to understand more about VBA (I'm self trained) and I overcome the problem by using a situation where the macro wait until...

Application.CutCopyMode = True

cheers.