Switching between Access - Excel - Acess

[puressence73]

Hello,

I have an Excel spreadsheet which is linked to 2 tables in an Access database. The reason for this is to generate and calculate tax details, which is easier in Excel than Access.

I have a macro in my Access database which closes the only instance of Access and opens Excel.

As soon as excel opens it updates from the database. I then have a form triggered by the spreadsheet.change event so that the user can then load criteria to the form to activiate a series of date related filters. Once this is done a print out of the figures is generated.

This is where I get stuck!!!!

I need to now somehow get back to Access which is causing me some trouble.

Firstly the link from the Access tables to the spreadsheet prevents Excel from re-opening the DB. So as a work around I have put a line in to delete the connected spreadsheets before quiting Excel. I would like a smarter solution if you have any ideas but this does work as a stop gap.

The second problem is that when I re-open Access from the Excel macro, closing Excel in the process, it only remains open for a few seconds before disappearing so basically I end up with everything shut down!!!!!!!

This is the code I am using to

'Path to Access database

LPath = "C:\database project.accdb"

'Open Access and make visible

Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable

oApp.OpenCurrentDatabase LPath
oApp.DoCmd.OpenForm "User Input"

With oApp
.DoCmd.RunMacro "Beep"

End With

DateInput.Hide

Application.Quit


End Sub

I would be very gratefull if someone could give me some ideas as I am pulling my hair out trying to get around this.

Regards

M

 

[gemma-the-husky]

i was trying to do that, and was struggling

i was able to set the excelapp to not visible, which did it, but i dont really like the effect that way.

the users are happy clicking back to access manually. hope you find a solution

 

[puressence73]

Hi Gemma,

Yes it's frustrating.

I am starting to question whether it is possible or not.

Thanks

M:-}

 

[gemma-the-husky]

must be - its just knowing the command

perhaps you have to tell windows to do it with a api call

 

[boblarson]

I guess I'm confused as to why you would need to do this anyway. Can you elaborate a little further as to why you have to have Access closed while doing Excel?

 

[puressence73]

Hi Bob,

Maybe this will explain what I am trying to overcome.

If I try to open the Excel spreadsheet whilst the DB is open I recieve the following error from Excel.

"You attempted to open a database that is already opened by user " x " on machine " y ". Try again when the database is available.

If I try to open Access I can not open a read / write copy of the database, only a read only which needs to be saved elsewhere.

Because of this I thought it would be easy to quit Access via a macro when switching to Excel, and then to quit Excel on the way back into Access.

However that would appear easier said than done. I have managed to quit Access so that Excel can update from the DB, then the user can add addtional criteria to manipulate the data.

The problem I am having is trying to return to Access. I have succesfully closed Excel and re-openened Access, although it closes immediately. I just need to work out how to keep it open.

Hope this helps (also hope you can help!!!)

M

 

[boblarson]

I have succesfully closed Excel and re-openened Access, although it closes immediately. I just need to work out how to keep it open.

How are you currently trying to reopen Access? What code?

 

[puressence73]

I am trying to re-open using the following code driven from a command button on a form in Excel.


Private Sub BckAcc_Click()

Dim oApp As Object
Dim LPath As String
Dim LCategoryID As Long

'Path to Access database

LPath = "C:\database project.accdb"

'Open Access and make visible

Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable

App.OpenCurrentDatabase LPath
App.DoCmd.OpenForm "User Input"

' With oApp
' .DoCmd.RunMacro "Beep"

' End With


End Sub


M

 

[gemma-the-husky]

bob

thats not quite what i am trying to do

i am using access

i open an excel sheet, update it from access, and then make excel the active window

all i then want is a way to make access the active window again

 

[boblarson]

I am trying to re-open using the following code driven from a command button on a form in Excel.


Private Sub BckAcc_Click()

Dim oApp As Object
Dim LPath As String
Dim LCategoryID As Long

'Path to Access database

LPath = "C:\database project.accdb"

'Open Access and make visible

Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable

App.OpenCurrentDatabase LPath
App.DoCmd.OpenForm "User Input"

' With oApp
' .DoCmd.RunMacro "Beep"

' End With


End Sub


M

Not good to use the object model for opening it because the minute you close Excel it will destroy your variable and hence close Access.

Just use a FollowHyperlink command:

FollowHyperlink "C:\database project.accdb"

to reopen.

 

[boblarson]

bob

thats not quite what i am trying to do

i am using access

i open an excel sheet, update it from access, and then make excel the active window

all i then want is a way to make access the active window again

Not sure you're going to be able to do that without using API programming to capture the hwnd of the window and then using that to activate. That is beyond my abilities.

 

[puressence73]

Hi Bob,

Thanks very much, that done the trick.

Now I have something that works...

 

[DCrake]

Has anyone considered using the AppActive command, such as:

    AppActivate "Microsoft Excel", False
        Application.Goto Reference:="DataTable1"
        Selection.Copy
    
    AppActivate "Microsoft Word", False
        wdApp.Selection.Goto Name:="bmTable1"
        wdApp.Selection.Paste

This is a snipped I use between Excel and Word, whereby Excel populates a word document based on the contents of the Excel file. I am sure this could be changed to work with Access as well.

David