Change absolute in relative path-problem 1

[Nogi]

Hi everybody. I'm hoping somebody could help me out with this issue, cause i've searched all over the internet but cannot seem te find any solution.

Idea:

1. I have an excelsheet, containing some information that needs to be transfered into an access database.
2. In order to do that, i've created a macro in an access database
3. The macro in the access-database, is launched by a press on a button in the excelsheet that contains a certain vba-code.

Problem:

The path of the database in the vba-code may not be an absolute path. If i use an absolute path, the code works. When i try to make it relative, it fails with a "Run-time Error 242: Object Required".
Does anyone have an idea how to solve this? Or what i'm doing wrong?

This is the working code with absolute path:

Sub GetAccess()
Application.StatusBar = "Working in MS Access"
Dim MyAccess As Object
Set MyAccess = CreateObject("Access.Application")
MyAccess.OpenCurrentDatabase "C:\Blue Books\start project\database.mdb"
MyAccess.DoCmd.RunMacro "Run"
MyAccess.Quit
End Sub

Now this is what i've changed (with the error as result):

MyAccess.OpenCurrentDatabase "C:\Blue Books\start project\database.mdb"
i changed into:
MyAccess.OpenCurrentDatabase (App.Path & "\database.mdb")

Is there anyone that has an idea of what went wrong? Or how i can make this work?

Thanks in advance for your helps

 

[alvechurchdata]

Have you checked the value of the App.Path string at runtime?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Nogi]

Uhm...how do i do that? I don't think i've ever checked this.

 

[Remou]

Hi
In Excel
Application.Path = C:\Program Files\Microsoft Office\Office
So you would need to say what app is, I think.

 

[alvechurchdata]

how do i do that?

You can use the debugger but I'd just put a message box in the code:
MsgBox("App.Path is " & App.Path)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

how do i do that?
MsgBox App.Path

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nogi]

I've tried several things with the msgbox app.path, but i'm not sure if i'm doing what you think i should.

Can you be more specific? Where in the vb-code do i type this?

 

[Nogi]

I've past it somewhere in the code as you said but i keep on getting the same Run-time error. No message box or something shows.

 

[Nogi]

Uhm..i've kept on searching the internet, and maybe found something to work with:

Wouldn't it be possible to get the job done with the "Application.CurrentProject.Path"-code? Since the excelfile and database-file are always in the same directory and folder?

Though i don't know how to add it to my code in order to make it work..

Any idea's on this?

 

[PHV]

Something like this ?
MyAccess.OpenCurrentDatabase ActiveWorkbook.Path & "\database.mdb"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nogi]

Yeay!! That did the trick PHV. Thanks for having helpt me out. You to alvechurchdata and Remou.

You've all been a great help to me.

 

[sa5cha]

This is a function where you get the path in return. I found it usefull:

Just copy&paste it.


Public Function AppPath() As String
Dim sPath As String

sPath = CurrentDb.Name
While Right$(sPath, 1) <> "\"
sPath = Left$(sPath, Len(sPath) - 1)
Wend
AppPath = sPath
End Function

 

[JerryKlmns]

Even faster!!

Public Function AppPath() As String

AppPath=left(currentproject.FullName, 
     len(currentproject.FullName)-len(currentproject.Name))

End Function