Can I use an Outlook script to enter data into an open excel worksheet?

[Better_Days]

All the sample code I seem to find for VBA withing Outlook is for closed workbooks, open workbook-> post data->save and close book etc

I can't find anything to show any code to reference and write data to an open workbook, I'm simply looking to write say a "y" in cell A1 on workbook test.xlsm sheet1, just can't seem to find any working code.


Thanks

[SamT]

bump

[gmayor]

It is simple enough, provided you ensure that you error trap Excel not be ing open and the file not present e.g.


Sub Example()
'Graham Mayor - http://www.gmayor.com - Last updated - 24 Oct 2017
Dim xlApp As Object
Dim xlWB As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
MsgBox "Excel is not running"
End If
On Error GoTo 0
Set xlWB = xlApp.Workbooks("Test.xlsm")
If xlWB Is Nothing Then
MsgBox "Test.xlsm is not open"
GoTo lbl_Exit
End If
With xlWB.Sheets(1)
.Range("A1") = "y"
End With
lbl_Exit:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[Better_Days]

Thanks Graham, all working fine. Is there anyway it can check for more than one instance of excel being open also as it only works with the initial instance of excel.

[gmayor]

See https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel