Object Variable or with block variable not set.

[AnthonyWB]

I have an error that I just can't figure.

Please Login or Register  to view this content.

[davegugg]

This error is occuring because you are trying to set a workbook type variable to a string value. Try this instead:

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

[AnthonyWB]

I am now getting a subscript out of range:

Please Login or Register  to view this content.

Any ideas?

[davegugg]

Sounds like the file is not there. Make sure you have all your slashes.

[AnthonyWB]

well the file name should read as an example:

Option EPMS Valuation 3-31-11.xlsx

Perhaps I need to account for the spaces?

[davegugg]

Look at what you have put together in your code. Right now it is:

XXX:\Users\YYYYY1\Desktop\Option EPMS Valuation 03-31-11.xls.

You need to change the month to a single character to get rid of the zero in front of the 3.

[AnthonyWB]

Thanks, but still not working. Take a look at what I have done:

Please Login or Register  to view this content.

and then,

Please Login or Register  to view this content.

I am still geting a subscript out of range.

[davegugg]

Use the immediate window to check the file path and name and compare it to the filepath and name in the windows explorer.

[AnthonyWB]

Dave thanks for following me on this one.

Acording to the Immediate window:

Please Login or Register  to view this content.

yields the error: Object Variable or with Block Variable not set. All the other variables pan out exactly the way they should.

[davegugg]

Do this:

Please Login or Register  to view this content.

[AnthonyWB]

Yep its what I thought, there is no space after Valuation and date.

Please Login or Register  to view this content.

we just need to add a space. I have tried the following and it does not correct the issue:

Please Login or Register  to view this content.

Note the space in the date format.

[davegugg]

Like this instead:

Please Login or Register  to view this content.

[AnthonyWB]

Almost there.

Please Login or Register  to view this content.

is correct. But WrkbookPrior = Nothing. The file does exist and its on my desktop. According to the properties of the file the name of the file is: Option EPMS Valuation 12-31-10.xlsx and it is in the folder C:\Users\AHernandez1\Desktop

[davegugg]

Please Login or Register  to view this content.

Not sure why I wrote this wrong earlier.