Fake a Drive and Path (Dev versus Prod)?

[drewg]

Hi
This must be an ancient problem but Im stumped
A workbook copies a range into an Access table, but the problem is
Where it was written, the Access database is on C:\Data\MyApp
Where it is running, the Access database is on R:\MRXX\Prod\Colby
Is it possible to fake it somehow so that we pick up the right path regardless of where its running?
Thanks very much

 

[Scott Huish]

You would want to replace the C:\ and R:\ in this example with the full path to where the Excel sheet would be stored at.

Perhaps something like this:

Dim accessPath As String
Select Case ThisWorkbook.Path
    Case "C:\": accessPath = "C:\Data\MyApp\"
    Case "R:\": accessPath = "R:\MRXX\Prod\Colby\"
End Select

 

[drewg]

Thanks very much
The Excel workbooks are always in C:\ProjectDir in either situation, its the Access location that changes, so the C:\ branch will always win.
In the interim i found this

If Len(Dir("c:\Instructions.doc")) = 0 Then
   Msgbox "This file does NOT exist."
Else
   Msgbox "This file does exist."
End If

So thanks very much for your help, I appreciate it