Solved: Opening Files

[c19h28O2]

Hi,

I have 2 questions both for VBA

1. I w

[XLGibbs]

I am pretty intuitive, but care to elaborate?

[c19h28O2]

Hi Sorry,

My explorer crashed i didn't think it posted.....

I woud like to open an excel sheet inside excel and an access database from access,
I have 2 buttons on my form one which should open an excel sheet and the other should open an access database as if i used windows explorer to open it.. hope that makes sense.....

Thanks

C19

[XLGibbs]

No worries, welcome to the board!

Okay...well, more information is needed.

These buttons are on an excel form?

To open an excel worksheet from excel...
[vba]
Workbooks.Add
[/vba]

Would add a new workbook to the instance of excel, and it would be default become the active workbook.

[vba]

Dim DPath As String
DPath = "C:\Documents and Settings\Owner\My Documents\TEST.mdb"
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DPath
[/vba]


Would be a sample of opening Access from Excel, just specify the path to your database.

[c19h28O2]

Hi,

Thanks,

Both bits of code work great, thanks a lot for your hope really appreciated.

C19

[c19h28O2]

Hi,

I have added the code to an if statement but as soon as the if statement is has ended or the procedure ends the access closes?

the code i am using is

Dim ans As Boolean
ans = MsgBox("Database Updated, would you like to open the database?", vbYesNo)
If ans = True Then
	Dim DPath As String
	DPath = "S:\Hockney Folder Structure\CUSTOMER SERVICE\Michael\ACBS\ACBS.mdb"
	Set oApp = CreateObject("Access.Application")
	oApp.Visible = True
	oApp.OpenCurrentDatabase DPath
End If

[XLGibbs]

YOu probably want to open a particular recordset or something right?

DoCmd.OpenForm FormName

or

DoCmd.OpenTable "TableName"

You can use that line to open a form in the access database...

Since the excel code is opening the database, Excel is actually controlling the action. You can use the DoCmd line to open a query, table or other recordset... just change "FormName" to the form you want them to get to when..

[c19h28O2]

Hi,

I'd just like to display the tables once the database has opened, not any particular record or query etc...

Thanks

C19

[XLGibbs]

[VBA]
Dim DPath As String
LPath = "C:\Documents and Settings\Owner\My Documents\db1.mdb"
Set oApp = Access.Application
oApp.Visible = True
oApp.OpenCurrentDatabase LPath
[/vba]

This will keep it open. My code was more for gaining access to a recordset in access by creating an instance of it in the background.

This keeps it open...

[c19h28O2]

Hi,

Thanks for that, will give it a try tomorrow and let you know how i get on.

Thanks for the help BTW

C19

[c19h28O2]

Hi,

Do I need to add a reference for that bit of code? I am getting an object required error...

Thanks

C19

[c19h28O2]

Hi,

All ok now, I needed a reference to the MS access object library.

Access is now staying open ;-)

Cheers