Path changes affecting external data query

T

Tony

I have an access database and an excel file (for the publication of various
outputs of the database) which I update via external data query. Both of
these files live on a datastick for portability. The issue I have is that the
drive letter is different from one PC to another and manual editing of the
SQL in excel is required to simply change the drive letter from E to F or
whatever.
Is there a way to get around this?
I have tried to remove the drive letter altogether thinking that perhaps
excel will look in the files home directory first for the DB but this doesnt
work.

TIA
Tony
 
C

CLR

This formula, put in a helper cell in your Excel workbook, will return the
drive letter with colon and backslash..........perhaps you could use it to
feed your Query........

=LEFT(CELL("filename",A1),3)

hth
Vaya con Dios,
Chuck, CABGx3
 
Top