Question Locked

Excel 2003 VBA - Need to open a workbook in a folder

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have two workbooks in a folder. The first open automatically and does what it should do then it calls the second workbook to open. The code appears to require a complete path in order to find the second workbook. If it were only to run on my computer, it would be fine as it is. However once I get it working, it will be placed on a SharePoint server to be downloaded and used by anyone who needs it. When that happens, the first workbook can no longer locate the second, as it is looking for my C:/ drive. I tried truncating the addy to C:/(name of folder both files are in)/(name of second workbook). That still results in an error. Any way to make this a relative address instead of absolute? (NOTE: I am a self-taught VBA newbie. Be gentle.)

7 replies

by maevinn

Don't reference the drive at all--truncate to /FileName.xls This tells File1 to look for File2 in the same folder as File1 is located.

by bablhous

Actually, that's how I started out - it just felt reasonable but it didn't help. I tried your suggestion again (maybe I keyed it in wrong or something the first time) but I still get the following error message: 'Run-time error '9': Subscript out of range.'

I've tried it with and without the quote marks, with and without the forward slash. I'm simply not getting it and I feel it's something obvious that I'm just not seeing.

Thanks for your help so far - any other ideas?

by maevinn

Wrong slash--try it with \ instead of the forward slash.

by bablhous

Tried forward slashes, tried backward slashes, tried with C: (both forward and backward), tried it with c:// and c:\\. The closest I came to resolving this was some info I unearthed saying that if both were opened simultaneously, my problem would be moot but the new resource gave no clue as to how I would manage this.

I really have no idea why this won't work. It should. ** teeth gnashing sound **

Thanks for sticking with me on this -- I do appreciate it. After today, I wil not be looking at this until the day after Christmas so if we don't speak before then, have a happy holiday. (And if you see Santa before me, ask him for the answer, will you?)

by maevinn

You just want the immediate file name after the slash, nothing else.

by alexander.hardman

If you use the full name property of a workbook you can return it's complete path as well as the file's name. My solution to the same problem was to retrieve the full name of a file, programmatically count until I got to a '\' and then extract just the folder path to the files.

I then concatenated that path onto the file name I wanted to open.

strFileName = ThisWorkbook.Name
strFullName = ThisWorkbook.FullName
strFileName = "(COPY)" & ThisWorkbook.Name
strFullName = Left(strFullName, InStrRev(strFullName, "\", , vbTextCompare))

The last line is where I pull just the directory path and leave out the file name.

Workbooks.Open (strPathToSave & "template.xlt")

Hope that helps. I can clarify if you need me to.

This discussion is closed to new replies.