Word/Excel toggle

[StephanieH]

I have a macro that picks up information from Word and pastes it into Excel.
It then makes a couple additional changes and queues the user to review the
results with a mesg box. If everything is well, the user selects OK and the
macro continues with some additional changes. If not, the user selects
cancel and makes some changes manually.

This is my first attempt at toggling between Word and Excel so I have a
vague understanding of what's happening, but it seems that all of the actions
to be performed in Excel require "oXL." before the line of code. However,
when I get the message box, placing oXL. before the line of code gives me a
message of "Wrong number of arguments or invalid property assignment".
However without it, it goes back to Word to prompt. It runs fine after that,
but I can't review the information in Excel without switching back and forth.

How to do I execute the message box in Excel?

oXL.Workbooks("MyWorkbook.xls").Activate
oXL.Sheets("sheet1").Activate
oXL.ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
oXL.Range("c1").Select
oXL.Sheets("sheet2").Activate
oXL.Range("A1").Select
oXL.ActiveCell.Value = MyWordName
oXL.Sheets("sheet1").Activate

ans = MsgBox("OK?", vbOKCancel)
If ans = 2 Then End
If ans = 1 Then
oXL.Visible = True
oXL.Workbooks("MyWorkbook.xls").Activate
oXL.Sheets("sheet1").Activate
oXL.Range("c1").Select
oXL.ActiveCell.Value = MyWordName

 

[Ed]

Hi, Stephanie. I do some stuff between Word and Excel, though I've never
tried what I think you're describing. And I'm not a real expert, either,
but I think I might have a solution.

If I have your situation correct, if the answer is "No", you want to stop
the macro with the Excel file open in front of you so you can make changes.
I think the difficulty here is that the macro is running in Word, and when
it stops it "drops out" in the application in which it was running. Your
"End" command stops the macro in its tracks - which means it stops in Word.

If you want the Excel file open and available, then you need a different
approach. I think I would try calling a macro in Excel which opens a
modeless UserForm. This would activate the Excel app, but a modeless form
allows you to work on the file while the form is open. A button on the form
would unload it, effectively ending the Excel code and returning you to the
Word macro, which could then End. It would look like:
If ans = 2 Then
oXL.Run ("WorkbookName.xls!MacroName")
End
End If

HTH
Ed

 

[StephanieH]

Thanks for the suggestion. I had to tweak it a little but for those
interested, here's what I ended up with:

oXL.Run ("MyWorkbook.xls!KeepGoing")

ACTIONS IN EXCEL


Sub KeepGoing()
ans = MsgBox("OK?", vbOKCancel)
If ans = 2 Then End (At this point it goes back to Word to release
references, etc)
If ans = 1 Then
Workbooks("MyWorkbook.xls").Activate
Sheets("sheet2").Activate
Range("a1").Select
ActiveCell.Copy
Sheets("sheet1").Activate
Range("c2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End If
End Sub


It works great! Thanks Ed.

 

[Ed]

Glad to help!
Ed

Thanks for the suggestion. I had to tweak it a little but for those
interested, here's what I ended up with:

oXL.Run ("MyWorkbook.xls!KeepGoing")

ACTIONS IN EXCEL


Sub KeepGoing()
ans = MsgBox("OK?", vbOKCancel)
If ans = 2 Then End (At this point it goes back to Word to release
references, etc)
If ans = 1 Then
Workbooks("MyWorkbook.xls").Activate
Sheets("sheet2").Activate
Range("a1").Select
ActiveCell.Copy
Sheets("sheet1").Activate
Range("c2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

End If
End Sub


It works great! Thanks Ed.

 

[Ed]

My personal taste - I don't like the MsgBox. If you are doing this to allow
the user to check out the Excel file and make sure everything looks okay,
but the message box pops up in the middle of things and hides stuff,
something may slip by that the user wished they would have seen. That's why
I prefer the modeless UserForm - it allows the user to activate and scroll
through the file without covering over the "Okay" button. But if the MsgBox
works for you, then it's definitely the easier way to go. Glad you got it
working.

Ed