Open a file

[Jaz]

Sorry, I posted this in a different group because it forgot to cross post:

I'm trying to create a button that will open a file in access 2000.

The location of the file is D:\Test\label.xff.

If I run this in the command prompt, it opens the file with no problems.

If I create an event on a button to open the file I get an error. The VB
code is this:

Private Sub RunForms_Click()
Dim RetVal
RetVal = Shell("D:\Test\label.xff", 1)
End Sub

Why won't this work if it works in the command prompt?

Thanks,
Jasper

 

[Douglas J. Steele]

It doesn't work because Shell requires the name of a program, not the name
of a file.

Try

Private Sub RunForms_Click()
Application.FollowHyperlink "D:\Test\label.xff"
End Sub

 

[davethewelder]

Douglas, I have a similar problem, although at the moment I can find the file
on the network it won't let me open it due to permissions restrictions, I
have raised this with the company helpdesk. I can find the database with a
macro and looking at other posts I see the next step to run a query is to
input the SQL to the macro to run the query. My problem is that I have a
large suite of report which compile the report I am constructing in Excel
which are "fed" from the access database. What I am trying to do now is
activate the macros in the access database from the excel macro. Can this be
done? The macro code is below.

ub TestAccessRunQuery()
Dim dbs As Database
Dim wrkjet As Workspace
' fname = "c:\Documents and Settngs\dburns1\Report 15.MDB"

fname = "\Authors\Fsa records\RISK MANAGEMENT\Retail MSU\Panel
Analysis\Probability League Table\Report 15.mdb"
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkjet.OpenDatabase(fname, True)
End Sub

Hope this can be achieved.

Thanks in anticipation.


Davie

 

[Douglas J. Steele]

I don't believe you'll be able to run the Access macros from Excel in that
way. You have to use Automation, and if you can't open the file normally,
you probably won't be able to open it using Automation.

 

[davethewelder]

Douglas, can access run excel macros? The source files are created as csv
files but have text fields which hold strings with commas which, as you know,
creates a new field after each comma. The way I was getting around this was
to save them as an excel file.

Davie

 

[Douglas J. Steele]

Yes, but again you need to use Automation.

There's an example at http://www.mvps.org/access/modules/mdl0007.htm

Can you use some other separator than a comma in your file? Say a semi-colon
instead?

 

[davethewelder]

Douglas, these reports are created centrally and I don't know if they would
agree to change the separator as various departments use the same reports.
I tried to run the automation script you highlighted to me, changing the
path and macro name but I got an error saying "variable not defined" and the
"xlAutoOpen" command is highlighted. If I delete this Excel opens with the
correct file. Is something missing from the code?

Davie

 

[Jaz]

Douglas,

Thank you! That worked.

However, I get a warning message from microsoft office that states this:

"Some files can contain viruses or otherwise be harmful to your computer.
It is important to be certain that this file is from a trustworthy source.
Would you like to open the file?"

Is there a way to prevent this from poping up?

Thanks,
Jasper

 

[Douglas J. Steele]

Bad code on Dev's part! (I'll try and get it changed.)

Choice one of the following three options:

1. Go into the VB Editor, choose Tools | References from the menu bar,
scroll through the list of available references until you find Microsoft
Excel n.0 Object Library (where n depends on what version of Excel you're
using), select it and close the dialog. (I don't recommend this approach: it
can lead to problems if you've got multiple users with different versions of
Excel.)

2. Add the line of code

Const xlAutoOpen As Long = 1

before the existing line of code Dim objXL As Object, x

3. Change the line of code

..ActiveWorkbook.RunAutoMacros xlAutoOpen

to

..ActiveWorkbook.RunAutoMacros 1

 

[Douglas J. Steele]

For an easy fix, you can use the ShellExecute function (see
http://www.mvps.org/access/api/api0018.htm for an example).

This means you'd use

Private Sub RunForms_Click()
Call fHandelFile("D:\Test\label.xff", 1)
End Sub

For a more general solution, see http://support.microsoft.com/kb/829072

 

[Jaz]

Forgive me but I'm not a programmer by no means!

I get an compile error that states that the Sub or Function is not Defined.

I'm on version 2000, does that make a difference?

Any suggestions?

Thanks,
Jasper

 

[Jaz]

Douglas,

Forget my last post. I did not setup the module correctly.

I copied and pasted the code and created a module called 'FHandle'.

However, when I press on the button, nothing happens.

Any suggestions?

Thanks,
Jasper

 

[Douglas J. Steele]

Did you name the module FHandle, or did you name it FHandleFile, the same
name as the function within the module? Modules must have unique names, so
if you named it FHandleFile, rename it.

If the module does have a unique name and it's still not working, I'm afraid
that I have no idea. Since you're apparently not getting any error message,
it would seem as though you copied the code correctly, and I've never had
any problems using that code.

 

[Jaz]

Douglas,

Thanks for all your help. Not sure why the fhandle command did not work but
I went full circle and was able to get the shell command to work.

Thanks again!
Jasper