Subjectfilter as Reference not String

[Dave_Holland89]

Hello All

Hopefully this will be a nice easy one for someone, but I just can't seem to work around this one, I'm sure I must be missing something obvious.

I've got a piece of script to download a file from Outlook based on the subject line which currently works fine, providing the subject line is a string as follows (this is an excerpt):

Dim subjectFilter As String

subjectFilter = "This is The Subject"

This works fine. However in a bid to future proof the application, I want the end user to be able to select from a number of email subjects (there may be up to 15) in a drop down box in Excel.

If I write "This is the Subject" in A1 of my sheet in the same workbook as the macro, and then replace the above with:

subjectFilter = Application.Workbook("C:\Documents\Daily Reports.xlsm").Worksheet("Master Sheet").Range("A1")

I just get an error - any ideas on how I can have the Macro run from a reference in a cell? I'm guessing it's because the subjectFilter is no longer a String?

Thanks!

Dave

 

[PatOBrien198]

subjectFilter = Application.Workbook("C:\Documents\Daily Reports.xlsm").Worksheet("Master Sheet").Range("A1")

try:

subjectFilter = Application.Workbook("C:\Documents\Daily Reports.xlsm").Worksheet("Master Sheet").Range("A1").[COLOR=#FF0000]Value[/COLOR]

 

[Dave_Holland89]

try:

subjectFilter = Application.Workbook("C:\Documents\Daily Reports.xlsm").Worksheet("Master Sheet").Range("A1").[COLOR=#ff0000]Value[/COLOR]

Thanks - have given that one a whirl and it's a good suggestion - but met with the same error!

Dave

 

[PatOBrien198]

So, the macro you're running is in the same workbook that you're trying to pull the subject text from?

try:
subjectFilter = Activeworkbook.Worksheets("Master Sheet").Range("A1")

or better yet...
create a named range (say "subjectFilter") where your subject filter text will be and you can reference it:
subjectFilter = [subjectFilter]
or
subjectFilter = range("SubjectFilter")

 

[Dave_Holland89]

You sir, are correct, many thanks!

Not sure why I was trying to include the file path of a workbook that was already open . Sorted now

Thanks very much again.

Dave