Opening Excel from Access

[Phil Stanton]

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_Click

Dim ExcelApp As Object
Dim ExcelWasNotRunning 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(ExcelPath, "\"))
' File Name

If Dir(ExcelPath) <XLName Then ' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\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("Excel.Application")

If Err.Number <0 Then ExcelWasNotRunning = 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("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True ' Read Only
ExcelApp.Visible = True

Exit_Storage_Click:
Exit Sub

Err_Storage_Click:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
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

[Phil Stanton]

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" <ph**@myfamilyname.co.ukwrote in message
news:W8******************************@posted.plusn et...

>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_Click

Dim ExcelApp As Object
Dim ExcelWasNotRunning 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(ExcelPath, "\")) '
File Name

If Dir(ExcelPath) <XLName Then ' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\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("Excel.Application")

If Err.Number <0 Then ExcelWasNotRunning = 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("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True ' Read Only
ExcelApp.Visible = True

Exit_Storage_Click:
Exit Sub

Err_Storage_Click:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
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]

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" <ph**@myfamilyname.co.ukwrote in message
news:R8******************************@posted.plusn et...

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" <ph**@myfamilyname.co.ukwrote in message
news:W8******************************@posted.plusn et...

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_Click

Dim ExcelApp As Object
Dim ExcelWasNotRunning 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(ExcelPath, "\"))

'

File Name

If Dir(ExcelPath) <XLName Then ' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\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("Excel.Application")

If Err.Number <0 Then ExcelWasNotRunning = 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("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True ' Read

Only

ExcelApp.Visible = True

Exit_Storage_Click:
Exit Sub

Err_Storage_Click:
If Err = 2447 Then ' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
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]

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]

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.XLS" 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************@googlemail.comwrote in message
news:4e**********************************@26g2000h sk.googlegroups.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]

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("SheetName").cells(row,column).value =
ADORecordsetObjectName.Fields("Fieldname").value , 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]

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************@googlemail.comwrote in message
news:71**********************************@r66g2000 hsg.googlegroups.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("SheetName").cells(row,column).value =
ADORecordsetObjectName.Fields("Fieldname").value , 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]

On May 28, 9:46*pm, Roger <[e-mail]:

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.GetOpenFileName("Access 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]

"lyle fairfield" <ly************@gmail.comwrote in message
news:bd**********************************@a70g2000 hsh.googlegroups.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]

On May 28, 8:45*pm, lyle fairfield <[e-mail]:

On May 28, 9:46*pm, Roger <[e-mail]:

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.GetOpenFileName("Access 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.GetOpenFileName("Access 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]

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 (QSpaceAllocation) 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.DefaultValue = 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" <ly************@gmail.comwrote in message
news:bd**********************************@a70g2000 hsh.googlegroups.com...
On May 28, 9:46 pm, Roger <[e-mail]:

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.GetOpenFileName("Access 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]

On May 29, 3:23*am, "Phil Stanton" <[e-mail]:

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 (QSpaceAllocation) 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.DefaultValue = 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" <[e-mail] in message

news:bd**********************************@a70g2000 hsh.googlegroups.com...
On May 28, 9:46 pm, Roger <[e-mail]:

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.GetOpenFileName("Access 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]

Sure, if they will run without the jpegs.

"Phil Stanton" <ph**@myfamilyname.co.ukwrote in
news:-o*********************@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]

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

lyle DOT fairfield at gmail dot com

lyle fairfield <ly******@yah00.cawrote in news:Xns9AAD3CCC5DCD56666646261
@216.221.81.119:

Sure, if they will run without the jpegs.

"Phil Stanton" <ph**@myfamilyname.co.ukwrote in
news:-o*********************@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]

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.FollowHyperlink ExcelPath.Value
End Sub
This worked swimmingly for me.

A "good" thing about Application.FollowHyperlink 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.GetOpenFilename; 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.Name).Properties("Defau ltExcelPath").Value
End Sub

Private Sub Storage_Click()

With CurrentProject.AllForms(Me.Name).Properties
.Add "DefaultExcelPath", ExcelPath.Value
.Item("DefaultExcelPath").Value = ExcelPath.Value
End With

Application.FollowHyperlink 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" <[e-mail]:

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

[lyle fairfield]

As I see the structure and code there are two files, an mdb and an
xls.

The cycle of activity follows:
mdb -xls -mdb

On May 29, 4:54*am, Roger <[e-mail]:

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

[Phil Stanton]

Thanks a hell of a lot, Lyle

Problem solved with the Hyperlink method

Thanks also to Roger & Keith for their help

Phil
"lyle fairfield" <ly************@gmail.comwrote in message
news:0d**********************************@j22g2000 hsf.googlegroups.com...
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.FollowHyperlink ExcelPath.Value
End Sub
This worked swimmingly for me.

A "good" thing about Application.FollowHyperlink 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.GetOpenFilename; 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.Name).Properties("Defau ltExcelPath").Value
End Sub

Private Sub Storage_Click()

With CurrentProject.AllForms(Me.Name).Properties
.Add "DefaultExcelPath", ExcelPath.Value
.Item("DefaultExcelPath").Value = ExcelPath.Value
End With

Application.FollowHyperlink 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" <[e-mail]:

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