Microsoft Excel - Open Excel Workbook - Asked By Dan on 01-Feb-16 12:41 PM

So I have several Excel workbooks on a server and I am trying to figure out how to open them using a drop down list from my main Excel workbook.

On the sheet UPDATE in my main workbook in cells C10:E10 is the drop down list

I have the code to open a workbook, but not one that can take a name from the drop down list in cells C10:E10

Workbooks.Open Filename:="P:\People\Bob.xlsx", ReadOnly:=True

Example of workbooks

Bob.xlsx

Mike.xlsx

Steve.xlsx

linda.xlsx

Any help would be greatly appreciated.

Dan

Hello Dan

Assuming that your dropdown cell is A1 then using "P:\People\"&Range("A1").Value should go some way to getting your where you want to be.

Harry

Nope, I have tied that

Hi dan,

Try something like this and see if it makes a difference.


PathName = "P:\People\"
Filename = Range("A1").Value

Workbooks.Open Filename:=PathName & Filename, ReadOnly:=True

Harry 

So, I am get the error

        Run-time "1004':

        Method "Open" of object "Workbooks" failed

Hello Dan,

Have you tried it without the ReadOnly:=True to see if that makes a difference?

The other thing is, are you sure that your path is transcribed completely and accurately and ditto for your list of file names eg no hidden characters or leading spaces etc?

Another suggestion seems to be that the file is somehow corrupt.  Does it occur on all files?  Can you open the file manually?  Try doing a SaveAs under another name (say Boba.xls), change your list to the new name and try again.  If it then works, reverse the process to the original name(s).

I have not had any problems with that code in testing.

Harry

Hello Dan,

I did a bit more research on this after I realised you were working on a server whereas I am only working locally.

Have a read of this page.  There might be something of use there.

http://dedicatedexcel.com/universal-network-drive-mapping-with-excel-2010-vba/

Harry