Excel interactions don't work after Excel file opened

Question

I open a Excel workbook using this code from within OUTLOOK VBA:

'~~> BEGINNING OF EXCEL CODE <~~
Dim xlApp As Excel.Application
Dim sourceWB As Excel.Workbook
Dim sourceWS As Excel.Worksheet

Set xlApp = New Excel.Application

With xlApp
    .Visible = True
    .EnableEvents = True
    .UserControl = False
    .DisplayAlerts = False
    .AskToUpdateLinks = False
End With


strFile = scPATH & "Seattle ScoreCard " & Format(prevSCdate, "mm") & "." & Format(prevSCdate, "dd") & "." & Format(prevSCdate, "yy") & ".xlsm"

Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWS = xlApp.sourceWB.Worksheets(Chr(34) & "Week " & Format(prevSCdate, "ww") & " " & Format(prevSCdate, "mm") & "." & Format(prevSCdate, "dd") & "." & Format(prevSCdate, "yy") & Chr(34))

'~~> CANT GET THIS TO RUN ONCE EXCEL OPENS <~~

sourceWS.Copy Before:=Sheets(2)

'~~>

...

It opens the correct file and everything, but once it opens it does not execute the sourceWS.Copy line. I have verified that that command works by using executing it directly from VBA in the Excel file. I also have the Excel library activated.

Any help? The results are as if that last line were non existent. It opens the file and then nothing happens.

score 1 · Accepted Answer · edited May 23 '17 at 12:28

First thing:

Set sourceWS = xlApp.sourceWB.Worksheets(...)

In this line you tries to call property of xlApp object (instance of class Excel.Application) with name sourceWB, but, of course, xlApp doesn't support property with name sourceWB.

It would give you an error, unless you are using On Error Resume Next - if so, be carefully of using it, see image to my another answer for explanation why it's bad:)

So, you have already assign sourceWB to xlApp when opened workbook and Outlook knows that sourceWB belongs to xlApp. That means that you shouldn't use it in this context xlApp.sourceWB. Change the line above to

Set sourceWS = sourceWB.Worksheets(...)

Another thing is to use fully qualified name for Sheets(2) (using just Sheets(2) may trigger an error if you have more than one opened workbook while code is executing). Change:

sourceWS.Copy Before:=Sheets(2)

to

sourceWS.Copy Before:=sourceWB.Sheets(2)

Excel interactions don't work after Excel file opened

1 Answers1

Linked