ThisWorkBook.Path Generates an Error

**Winon** · 11-22-2011, 07:26 AM

Hi everyone,

I am using Flash Drive which on my machine is Identified as Drive J. Plug it into another PC and it can be Listed as anything else but J. To extract data from a closed workbook within the same folder I need to make use of ThisWorkBook Path. What I have Is as follows:

Please Login or Register  to view this content.

And that works well, as long as "J" does not change. I have tried what I have below, but it does not work. VB tells me "GetRange" Argument is not optional?

Please Login or Register  to view this content.

Can someone please help me with this. What must the Code look like to work?

Thank you.

**royUK** · 11-22-2011, 09:30 AM

What's GetRange?

The drive letter looks to be hard coded, you aren't using thisworkbook

Please Login or Register  to view this content.

**Winon** · 11-22-2011, 09:59 AM

Hello Roy,

Thank you for your interest and help. I must admit, I am totally lost here. I got this code from Ron de Bruin's site and used it as follows and it works.

Please Login or Register  to view this content.

This is activated via a CommandButton.

The GetRange is a Sub which works if I refer to the actual directory only. And that is where my problem is. Should I want to use the Flash Drive on another PC, the Drive name changes, and no extract can be made from the Closed WorkBook.

The GetRange is:

Please Login or Register  to view this content.

Thank You Roy!

**royUK** · 11-22-2011, 10:19 AM

Try the the amended code that I posted

**romperstomper** · 11-22-2011, 10:24 AM

Should it not be:

Please Login or Register  to view this content.

**Winon** · 11-22-2011, 10:41 AM

Yes Roy,

I have tried what you gave me, but it did not work. Romperstompers' code did the trick however. What is it, that he knows which we don't?

Thank you very much Gentlemen.

Your help is greatly appreciated!!!