Use Outlook VBA to manipulate data in an open Excel workbook

[AnthonyA755]

Hi All,

I seem to have hit a brick wall with my code. The main intention of what I am trying to achieve is run a macro in Outlook that saves attachments into a new folder, and the name for that folder and the files need to be based off information in some cells within an excel workbook. Currently, It works if the workbook is not open - here is the working code for that (note that I have declared the variables in two instances in the below examples just for ease of understanding this post, my actual code declares the variables once and sets them separately)

Dim xExcelFile As String: xExcelFile = "C:\Users\AAntoun\Desktop\QUOTES\QuoteTrackerNew.xlsm"
Dim xExcelApp As Excel.Application: Set xExcelApp = CreateObject("Excel.Application")
Dim xWb As Excel.Workbook: Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
Dim xWs As Excel.Worksheet: Set xWs = xWb.Worksheets("Window Quotes")
'from here, i can use xWs to manipulate data within that worksheet

The part that I cannot get to work is when the workbook is already open. Here is the code I am using for that

Dim xExcelApp As Excel.Application: Set xExcelApp = CreateObject("Excel.Application")
Dim xWb As Excel.Workbook: Set xWb = xExcelApp.Workbooks(xExcelFile)
Dim xWs As Excel.Worksheet: Set xWs = xWb.Worksheets("Window Quotes")

I get an error on the line where I am setting the variable for the Workbook. I get Run-time error "9": Subscript Out Of Range. I have tried to have the full filepath in the brackets, but that doesn't work either.

I guess, in summary, all I am asking is how can I declare a variable for an open Excel Workbook through Outlook Vba? I am sure I am making a simple mistake somewhere but my rummaging through the internet has returned nothing of value.

 

[Dan_W]

[Deleted because has already been dealt with at cross-post]