Controlling other applications with VBA, need help!

[PorscheGT2]

Hello All,

I've been tasked by my manager to create a macro that would copy a range of cells from MS Excel to MS Word. So basically when it gets transferred to Word, it becomes a table. Here's the code:

Application.ScreenUpdating = False
Dim appWD As Word.Application, docWD As Word.Document, appXL As Excel.Application
Range("A1:M68").Select
Selection.Copy
Set appWD = CreateObject("Word.Application.8")
With appWD
.ScreenUpdating = False
.Visible = True
.Documents.Add
.WindowState = wdWindowStateMaximize
End With
Set docWD = appWD.ActiveDocument
With docWD.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = InchesToPoints(0.4)
.BottomMargin = InchesToPoints(0.4)
.LeftMargin = InchesToPoints(1.25)
.RightMargin = InchesToPoints(1.25)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)
.FirstPageTray = wdPrinterDefaultBin
.OtherPagesTray = wdPrinterDefaultBin
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.SuppressEndnotes = False
.MirrorMargins = False
End With
With appWD
.Selection.Paste
.Selection.WholeStory
.Selection.Font.Size = 7
.Selection.HomeKey Unit:=wdStory
.ScreenUpdating = True
End With
Set appWD = Nothing
Set docWD = Nothing
Set appXL = Nothing
End Sub

This code works well when you don't have MS Word open and if you're running it the first time. But when I run it again, regardless of whether I close Word or not, it gives me a Run Time Error, something like, "RCP server not found!" or something like that. And this error comes up every other time I run the macro. I know that the reason for this is that there's something like a "residual" instance that gets left behind when I close Word. How do you check for that? And how do you create a condition that would open an existing instance of Word when it's there, and create one if there's none? Any help would be appreciated.

Thanks!

 

[SkipVought]

Porsche,

Take a look at GetObject Example in Excel Help. It may address the "already open/running" issue...
[tt]
GetObject Function Example
This example uses the GetObject function to get a reference to a specific Microsoft Excel worksheet (MyXL). It uses the worksheet's Application property to make Microsoft Excel visible, to close it, and so on. Using two API calls, the DetectExcel Sub procedure looks for Microsoft Excel, and if it is running, enters it in the Running Object Table. The first call to GetObject causes an error if Microsoft Excel isn't already running. In the example, the error causes the ExcelWasNotRunning flag to be set to True. The second call to GetObject specifies a file to open. If Microsoft Excel isn't already running, the second call starts it and returns a reference to the worksheet represented by the specified file, mytest.xls. The file must exist in the specified location; otherwise, the Visual Basic error Automation error is generated. Next the example code makes both Microsoft Excel and the window containing the specified worksheet visible. Finally, if there was no previous version of Microsoft Excel running, the code uses the Application object's Quit method to close Microsoft Excel. If the application was already running, no attempt is made to close it. The reference itself is released by setting it to Nothing.
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hi Skip,

Thanks for your help and reply. I've actually read that. I'd like to make this macro open Word and a new document if an instance doesn't exist, and use an existing instance when one exists. There's gotta be a way to create this condition without using error handlers. Any other takes?

Thanks,

Marvin

 

[SkipVought]

Why don't you want to use error handlers?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hello Skip,

It's not that I absolutely have to code without error handlers, it's just a preference I have and I think it's practical since if something screws up in the code, it will continue to run (assuming it's something that can be handled) without notifying the user that something went wrong. The people who will use this macro are non-technical people, therefore I'm trying to "dummy-proof" it as much as possible.

Also, if you use an error handler in the above macro, it does wonderfully take care of the problem, but since the error occurs when the upper and lower margins are set, the macro doesn't set the margins and therefore the pasted excel ranges get cut off to page 2. If you have suggestions as to how to make the upper and lower margins setup work when a RPC error occurs I'd be happy to take a look at it.

Thanks,

Porsche

 

[SkipVought]

The whole idea of error handlers is so that useres do NOT have to react to errors -- the PROGRAMER has already figgured out what ought to happen, including, for instance, writing an appropriate message to a log file when an error does occur that will provide information for further debugging and refining the application.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Alright, point well taken. I'm using an error handler in the same code, replacing "createobject" with "getobject" and using err.document and another variable to set a flag. It still has a hole though, since when you close MS Word manually and do not specify to close Word in the macro, it still thinks Word is open. So it works funny, the margins don't set after you reopen Word.

BTW, Skip, would you happen to know how to make the MsgBox function work in another application? Tried to make it work from Excel to Word but it comes up in Excel. TIA!