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

By bablhous ·
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.)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by Maevinn In reply to Excel 2003 VBA - Need to ...

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.

Collapse -

Thanks but I still don't have it right

by bablhous In reply to path

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?

Collapse -


by Maevinn In reply to Thanks but I still don't ...

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

Collapse -

Nice try but no cigar...

by bablhous In reply to Doh!

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?)

Collapse -

No C

by Maevinn In reply to Nice try but no cigar...

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

Collapse -

Don't know if you ever solved this, but...

by alexander.hardman In reply to Excel 2003 VBA - Need to ...

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.

Related Discussions

Related Forums