Opening Excel from Access

**Phil Stanton** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Sorry

Should have added

There is some code in the Excel application which refers to the Database and
extracts the relevant information

No problem opening Excel whether the database is open or not.

Phil

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:W82dnW2MNI yg8aTVnZ2dnUVZ8 tfinZ2d@posted. plusnet...

>I have a form with a button which is supposed to open an Excel file (With
>lots of Macros /VBA) in it. The Excel file gets it's data from the Access
>program
>
Here is the code
>
Private Sub Storage_Click()
>
On Error GoTo Err_Storage_Cli ck
>
Dim ExcelApp As Object
Dim ExcelWasNotRunn ing As Boolean ' Flag for final release
Dim XLFilePath As String
Dim XLName As String ' Excel file name from
Paths
Dim MyDb As Database
Dim Msg As String
>
' Find the normal path
' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS "
End If
>
XLName = Right(ExcelPath , Len(ExcelPath) - InStrRev(ExcelP ath, "\")) '
File Name
>
If Dir(ExcelPath) <XLName Then ' Not found
XLFilePath = FindFile("C:\Do cuments and Settings\Phil\M y
Documents\Acces s\MDB\WFYC\", XLName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If
>
Set ExcelApp = CreateObject("E xcel.Applicatio n")
>
If Err.Number <0 Then ExcelWasNotRunn ing = True
Err.Clear ' Clear Err object in case error occurred.
>
' Set the object variable to reference the file you want to see.
>
Set ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.WorkBo oks.Open ExcelPath, , True ' Read Only
ExcelApp.Visibl e = True
>
Exit_Storage_Cl ick:
Exit Sub
>
Err_Storage_Cli ck:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Cl ick
End If
>
End Sub
>
When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or locked"
>
What am I doing wrong
>
Thank
>
Phil
>

**ByteMyzer** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Please post the code that is in the Excel file. If that code refers to the
database, then that is most likely where the error is occuring, in which
case, we might be better able to help you.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:R8idnaH_C8 VIEaTVnZ2dnUVZ8 tPinZ2d@posted. plusnet...

Sorry
>
Should have added
>
There is some code in the Excel application which refers to the Database

and

extracts the relevant information
>
No problem opening Excel whether the database is open or not.
>
Phil
>
"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in message
news:W82dnW2MNI yg8aTVnZ2dnUVZ8 tfinZ2d@posted. plusnet...

I have a form with a button which is supposed to open an Excel file (With
lots of Macros /VBA) in it. The Excel file gets it's data from the Access
program

Here is the code

Private Sub Storage_Click()

On Error GoTo Err_Storage_Cli ck

Dim ExcelApp As Object
Dim ExcelWasNotRunn ing As Boolean ' Flag for final release
Dim XLFilePath As String
Dim XLName As String ' Excel file name from
Paths
Dim MyDb As Database
Dim Msg As String

' Find the normal path
' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS "
End If

XLName = Right(ExcelPath , Len(ExcelPath) - InStrRev(ExcelP ath, "\"))

'

File Name

If Dir(ExcelPath) <XLName Then ' Not found
XLFilePath = FindFile("C:\Do cuments and Settings\Phil\M y
Documents\Acces s\MDB\WFYC\", XLName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If

Set ExcelApp = CreateObject("E xcel.Applicatio n")

If Err.Number <0 Then ExcelWasNotRunn ing = True
Err.Clear ' Clear Err object in case error occurred.

' Set the object variable to reference the file you want to see.

Set ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.WorkBo oks.Open ExcelPath, , True ' Read

Only

ExcelApp.Visibl e = True

Exit_Storage_Cl ick:
Exit Sub

Err_Storage_Cli ck:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Cl ick
End If

End Sub

When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
'Admin' on Machine 'PHIL-DESKTOP' that prevents it being opened or

locked"

What am I doing wrong

Thank

Phil

>
>

**The Frog** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Hi Phil,

I would just like to ask something so that I can understand this
correctly: Are you having the Access application and the Excel
application open at the same time? That is to say, have you got Excel
and Access running at the same time, then from Excel try to open the
same database with an Access object, and vice versa?

Cheers

The Frog

**Phil Stanton** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

The intention is to open the Excel application from a button on an Access
form (and leave Access running)

If I open access then open the Excel file by clicking "Storage.XL S" or
opening Excel and selecting the Storage file absolutely no problem. It
doesn't matter whether I open Excel or Access first
There is no problem having both open at the same time.
As you surmise the Excel application uses a query within that Access
application to load it's data.

The only problem is trying to open the Excel file from the command button on
an Access form (codes posted)

Thanks

Phil

"The Frog" <Mr.Frog.to.you @googlemail.com wrote in message
news:4ef45a26-3b9b-40f0-9d71-d650beb681ac@26 g2000hsk.google groups.com...

Hi Phil,
>
I would just like to ask something so that I can understand this
correctly: Are you having the Access application and the Excel
application open at the same time? That is to say, have you got Excel
and Access running at the same time, then from Excel try to open the
same database with an Access object, and vice versa?
>
Cheers
>
The Frog

**The Frog** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Hi Phil,

I know what the issue is here. Basically it works like this:

1/ You open an Access MDB in MS Access. This MDB has both the data and
the forms in it.

2/ On a form in MS Access you click a button and launch Excel.

3/ When Excel is launched the macro code you have posted tries to run
and returns with an error.

If this sequence of events is correct then i know why.

The reason is that the MDB file is in exclusive mode, and at the same
time you are trying to access it from another application. In short
you are trying to create an object / read from something that is
already in use / exists.

How to solve this.....?????

How I would approach this is as follows:

1/ Create an ADO recordset to hold the query results and disconnect
it.
2/ Create an Excel Application Object in Access (not try the other
way)
3/ Control the Excel object as you see fit to display the results from
the ADO recordset. (something like looping through the recordset and
doing a worksheets("She etName").cells( row,column).val ue =
ADORecordsetObj ectName.Fields( "Fieldname").va lue , or something like
that. Dont forget to increment your row numbers in Excel!).
4/ Destroy the recordset
5/ If you want to show excel, then control this from the Access
Application, or alternatively save the Excel workbook you are working
on (do this by controlling the object) and then use a second module /
macro in Access to launch Excel and load the workbook with the fresh
results.

I hope this helps

Cheers

The Frog

**Phil Stanton** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Hi

You are absolutely right in the sequence of events.

Where I am not so sure is that once both Access ans Excel are opened (the
latter by launching Excel directly) I can change data in Access, then press
a button on the Excel sheet to refresh the data with no trouble. This button
runs the same GetAccess() routine.

Where do you suggest I create the ADO recordset, within Access or Excel?

Thanks

Phil

"The Frog" <Mr.Frog.to.you @googlemail.com wrote in message
news:71d5dc28-2b38-42ed-9249-0f81d524f618@r6 6g2000hsg.googl egroups.com...

Hi Phil,
>
I know what the issue is here. Basically it works like this:
>
1/ You open an Access MDB in MS Access. This MDB has both the data and
the forms in it.
>
2/ On a form in MS Access you click a button and launch Excel.
>
3/ When Excel is launched the macro code you have posted tries to run
and returns with an error.
>
If this sequence of events is correct then i know why.
>
The reason is that the MDB file is in exclusive mode, and at the same
time you are trying to access it from another application. In short
you are trying to create an object / read from something that is
already in use / exists.
>
How to solve this.....?????
>
How I would approach this is as follows:
>
1/ Create an ADO recordset to hold the query results and disconnect
it.
2/ Create an Excel Application Object in Access (not try the other
way)
3/ Control the Excel object as you see fit to display the results from
the ADO recordset. (something like looping through the recordset and
doing a worksheets("She etName").cells( row,column).val ue =
ADORecordsetObj ectName.Fields( "Fieldname").va lue , or something like
that. Dont forget to increment your row numbers in Excel!).
4/ Destroy the recordset
5/ If you want to show excel, then control this from the Access
Application, or alternatively save the Excel workbook you are working
on (do this by controlling the object) and then use a second module /
macro in Access to launch Excel and load the workbook with the fresh
results.
>
I hope this helps
>
Cheers
>
The Frog

**lyle fairfield** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

On May 28, 9:46 pm, Roger <lesperan...@na tpro.comwrote:

it's not (access -excel -access).
it's access -excel
excel then has an external data source to retrieve data from an
access table

You mean it's Access->Excel->Other Access?

I guess you are better at reading and understanding this code and
description than I.

That's not how I interpreted

"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"

nor

"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"

Does this

"MDBName = Application.Get OpenFileName("A ccess Database ,*.mde",
, "Where is the Club Database?")"

not indicate he is looking for an instance of Access that is already
open?

And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.

And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?

When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.

I see no bug in this thread, just a display of bad code chasing an
illogical goal.

**Keith Wilby** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a7 0g2000hsh.googl egroups.com...

I see no bug in this thread, just a display of bad code chasing an
illogical goal.

And, if you really need this data to transfer from Access to Excel, why not
just *push* the data in from Access instead of trying to get Excel to drag
it out? That would seem logical to me.

Keith.

**Roger** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

On May 28, 8:45 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :

On May 28, 9:46 pm, Roger <lesperan...@na tpro.comwrote:
>

it's not (access -excel -access).
it's access -excel
excel then has an external data source to retrieve data from an
access table

>
You mean it's Access->Excel->Other Access?
>
I guess you are better at reading and understanding this code and
description than I.
>
That's not how I interpreted
>
"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"
>
nor
>
"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"
>
Does this
>
"MDBName = Application.Get OpenFileName("A ccess Database ,*.mde",
, "Where is the Club Database?")"
>
not indicate he is looking for an instance of Access that is already
open?
>
And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.
>
And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?
>
When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.
>
I see no bug in this thread, just a display of bad code chasing an
illogical goal.

this line
"MDBName = Application.Get OpenFileName("A ccess Database ,*.mde",
, "Where is the Club Database?")"

simply opens a dialog box to browse for a file name, with an MDE
extension
it does not open the MDE/MDB

the getAccess function is only run once to create an external data
source to retrieve data

technically it's a jet table not an access table, so to use you above
structure
it is access -excel -jet

there's nothing special being done in excel, just creating a data
source and importing data into a worksheet ... which is faster than
pushing it from access
and everytime you open the worksheet, the data is automatically
refreshed

**Phil Stanton** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Hi guys

Just to clarify. The DB is for running a Yacht/Sailing Club. We have various
storage areas eg boat compound and pontoons, and members are charged to keep
their boats there.

I have drawings of those areas which I hold as JPGs. I have spent ages
working out the position of all the boats in these storage areas and given
them an XY co-ordinate so that my Access Query (QSpaceAllocati on) gives the
name of the Storage Area, the SpaceNo, X & Y Co-ordinates, A combined field
of the name of the person using that space, & boat name & boat class &
whether they have paid, .

Excel,but not Access has an add-in written by Rob Bovey called "XY Chart
Labeler" which allows you to label every point on a scatter graph.
When the Excel application opens (from the Excel Icon rather than from
Access), it re-loads the data from the above query, checks the latest
version of the JPG plans, updates all the scatter charts and output them as
GIF files.

The database has various forms in it with an Image on it that retrieves the
GIF file.

OK its a long way round, but what I am trying to achieve is to eg change the
occupier of a space, within Access and be able to see the new plan. That is
why I want to be able to click a button (or something) to create the new GIF
file and load it back to the image on the form where I am changing the
occupier.

Everything works separately, it's just the bit where I click the command
button where I get the error. Nothing to do with record locking because it
won't work with the space allocation form closed, and clicking a button on
an unbound form.

Have nearly got to a solution!!!!!!

The form checks that the Excel path is OK and loads it into the default
value of the unbound textbox
ExcelPath.Defau ltValue = Chr$(34) & ExcelPath & Chr$(34)
DoCmd.Save acForm, Me.Name

If I comment out the DoCmd.Save acForm, Me.Name line I at least get Excel to
open. Still other problems there, but I think it could be caused by tryng to
generate the graph before the data is fully loaded.

I guess the DoCmd.Save acForm, Me.Name is a bit like opening the form in
design view and changing back to form view

Phil

"lyle fairfield" <lyle.fairfield @gmail.comwrote in message
news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a7 0g2000hsh.googl egroups.com...
On May 28, 9:46 pm, Roger <lesperan...@na tpro.comwrote:

it's not (access -excel -access).
it's access -excel
excel then has an external data source to retrieve data from an
access table

You mean it's Access->Excel->Other Access?

I guess you are better at reading and understanding this code and
description than I.

That's not how I interpreted

"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"

nor

"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"

Does this

"MDBName = Application.Get OpenFileName("A ccess Database ,*.mde",
, "Where is the Club Database?")"

not indicate he is looking for an instance of Access that is already
open?

And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.

And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?

When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.

I see no bug in this thread, just a display of bad code chasing an
illogical goal.

**Roger** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

On May 29, 3:23 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

Hi guys
>
Just to clarify. The DB is for running a Yacht/Sailing Club. We have various
storage areas eg boat compound and pontoons, and members are charged to keep
their boats there.
>
I have drawings of those areas which I hold as JPGs. I have spent ages
working out the position of all the boats in these storage areas and given
them an XY co-ordinate so that my Access Query (QSpaceAllocati on) gives the
name of the Storage Area, the SpaceNo, X & Y Co-ordinates, A combined field
of the name of the person using that space, & boat name & boat class &
whether they have paid, .
>
Excel,but not Access has an add-in written by Rob Bovey called "XY Chart
Labeler" which allows you to label every point on a scatter graph.
When the Excel application opens (from the Excel Icon rather than from
Access), it re-loads the data from the above query, checks the latest
version of the JPG plans, updates all the scatter charts and output them as
GIF files.
>
The database has various forms in it with an Image on it that retrieves the
GIF file.
>
OK its a long way round, but what I am trying to achieve is to eg change the
occupier of a space, within Access and be able to see the new plan. That is
why I want to be able to click a button (or something) to create the new GIF
file and load it back to the image on the form where I am changing the
occupier.
>
Everything works separately, it's just the bit where I click the command
button where I get the error. Nothing to do with record locking because it
won't work with the space allocation form closed, and clicking a button on
an unbound form.
>
Have nearly got to a solution!!!!!!
>
The form checks that the Excel path is OK and loads it into the default
value of the unbound textbox
ExcelPath.Defau ltValue = Chr$(34) & ExcelPath & Chr$(34)
DoCmd.Save acForm, Me.Name
>
If I comment out the DoCmd.Save acForm, Me.Name line I at least get Excel to
open. Still other problems there, but I think it could be caused by tryng to
generate the graph before the data is fully loaded.
>
I guess the DoCmd.Save acForm, Me.Name is a bit like opening the form in
design view and changing back to form view
>
Phil
>
"lyle fairfield" <lyle.fairfi... @gmail.comwrote in message
>
news:bd05b0cb-d994-4f77-8284-fa5b1e992087@a7 0g2000hsh.googl egroups.com...
On May 28, 9:46 pm, Roger <lesperan...@na tpro.comwrote:
>

it's not (access -excel -access).
it's access -excel
excel then has an external data source to retrieve data from an
access table

>
You mean it's Access->Excel->Other Access?
>
I guess you are better at reading and understanding this code and
description than I.
>
That's not how I interpreted
>
"I have a form with a button which is supposed to open an Excel file
(With
lots of Macros /VBA) in it. The Excel file gets it's data from the
Access
program"
>
nor
>
"There is some code in the Excel application which refers to the
Database and
extracts the relevant information"
>
Does this
>
"MDBName = Application.Get OpenFileName("A ccess Database ,*.mde",
, "Where is the Club Database?")"
>
not indicate he is looking for an instance of Access that is already
open?
>
And what exactly is an access table? Access has no tables.Access is a
gui that has some database manipulation commands and stores specific
procedures in JET/ACE databases.
>
And why when you have an instance of Access open would you open an
instance of Excel to deal with data in ANY database, either one to
which the first instance is linked, an entirely separate one, or the
first instance itself? Is there something you could do with Excel that
you couldn't do with Access/Jet etc?
>
When we talk of bug we can (as very recently was done in this group)
list all the conditions and steps required to emulate the bug.
>
I see no bug in this thread, just a display of bad code chasing an
illogical goal.

instead of using getAccess() to create the data source, have you tried
creating an odbc data source to your MDE, and creating the external
data query in excel

I wonder if one of the parameters in getAccess() is wrong or if one or
more are missing ?

**lyle fairfield** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Sure, if they will run without the jpegs.

"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in
news:-oCdnTiQsfp9_6PV RVnygwA@posted. plusnet:

Db is about 180 k and Excel file about 50k. Could send them to you if
you are interested. (Office 2000 SP3)

**lyle fairfield** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

00ps; clicking reply, of course will fail as the address is mangled; use

lyle DOT fairfield at gmail dot com

lyle fairfield <lylefa1r@yah00 .cawrote in news:Xns9AAD3CC C5DCD5666664626 1
@216.221.81.119 :

Sure, if they will run without the jpegs.
>
"Phil Stanton" <phil@myfamilyn ame.co.ukwrote in
news:-oCdnTiQsfp9_6PV RVnygwA@posted. plusnet:
>

>Db is about 180 k and Excel file about 50k. Could send them to you if
>you are interested. (Office 2000 SP3)

**lyle fairfield** · Jun 27 '08, 07:25 PM

Re: Opening Excel from Access

Code that opens another application and then instructs the application
to open a document is often difficult. What happens when the
application opens but the document file is not found? What happens
when the document generates an error?

I don't see that you need to control the Excel Sheet from your Access
application so I recommend that you replace the code behind the form
with:

Private Sub Storage_Click()
Application.Fol lowHyperlink ExcelPath.Value
End Sub
This worked swimmingly for me.

A "good" thing about Application.Fol lowHyperlink is that, if it can't
find the file, it doesn't open the application, but generates an
innocuous error message.

If I were doing this, I'd also include a GetOpenFileName procedure,
(plenty posted in CDMA, I think; I have one more or less ready to go
if you want it) to let your user find the excel file. You already use
this, in the reverse, in your Excel file, but excel provides this
capability implicitly with Application.Get OpenFilename; in Access you
must work for it but once importing the code, it's nothing.

Enough with gratuitous and unnecessary advice.

Bugs are replicable and can be reduced to very simple terms and
conditions. I think you have not identified a bug.
------
If you want to save the path to the excel file without messing with
saving the form you could just save the path as a property of the
Access Form Object (in CurrentProject. AllForms) as per these lines as
the code behind the form:

Private Sub Form_Load()
On Error Resume Next
ExcelPath.Value =
CurrentProject. AllForms(Me.Nam e).Properties(" DefaultExcelPat h").Value
End Sub

Private Sub Storage_Click()

With CurrentProject. AllForms(Me.Nam e).Properties
.Add "DefaultExcelPa th", ExcelPath.Value
.Item("DefaultE xcelPath").Valu e = ExcelPath.Value
End With

Application.Fol lowHyperlink ExcelPath.Value

End Sub

Now I suppose I'd better go make sure Access 2K has
CurrentProject. AllForms but TTBOMR it has.

On May 29, 5:23 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

Everything works separately, it's just the bit where I click the command
button where I get the error.

Opening Excel from Access

Opening Excel from Access

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment