Question Shelling Excel crashes Access

[MikeDuffield]

Hello all,

I'm new to the forum and would like some assistance if you wouldn't mind?

I'm currently building a "Main Menu" tool for the team I work with, which has buttons and combo boxes that allow a user to choose a tool to use and then launch it.

When I use "Shell" to open an Excel sheet it works, however when other users go in using Access Viewer, they get an error message and the program terminates. I have Office 2007 and everyone else on the team has Office 2002 / 2003, could this be the cause of the problem?

The code that works on mine but crashes everyone elses is:

Private Sub checkingtoolbtn_Click()
Dim checkingtool As String
checkingtool = """G:\Sales and Marketing\General\Export\Export Lookup.xls"""
Call Shell("Excel " & checkingtool, 1)
End Sub

Any idea why it would work on mine but nobody elses?

I'm assuming it's to do with the difference in versions perhaps...


Any help would be much appreciated.


Regards,
Mike.

 

[Trevor G]

Welcom to the Forum,

It does sound as though it is the version issue,as you mention you are using Access 2007 and others are using 2003.

What about create object to see if this will resolve the issue of early and late binding?

Read this link as it may help.

http://support.microsoft.com/kb/245115

So you would end up with something like this

Sub OpenWorkbook()
Dim xlApp as Excel.Application
Set XlApp = Createobject("Excel.Application")
With xlApp
.Workbooks.Open "Your string"
.visible=True
End with

 

[MikeDuffield]

Thanks for your response Trevor, I'm still stuck though (Please excuse me if I ask silly questions, I'm very new to Access!)

I now have:

Private Sub checkingtoolbtn_Click()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Workbooks.Open """G:\Sales and Marketing\General\Export\Export Lookup.xls"""
    .Visible = True

End With

End Sub

That code is assigned to my command button, is that right?

For reference, it seems to fail on the Dim part.

I appreciate your help so far!


Thanks,
Mike.

 

[Trevor G]

.Workbooks.Open """G:\Sales and Marketing\General\Export\Export Lookup.xls"""

You would look to set the reference, in VBA (Use ALT + F11) then Go To the Tools menu and select References, Go dow the list until you find Microsoft Excel Object Library and then place a tick in the box on the left.

the workbook open line has to many quotes you only need one set

.Workbooks.Open "G:\Sales and Marketing\General\Export\Export Lookup.xls"

 

[MikeDuffield]

Thanks Trevor,

I followed your instructions and it worked on my version. Unfortunately testing it on a PC with an older version of Excel, I opened the Access database and it said it "Contains a broken reference to the file EXCEL.exe version1.6", it lets me enter the database after clicking OK but when pressing the command button Access crashed as it did before.

Very strange as it works perfectly on my system, but everyone else crashes out. It's clearly a problem with the different versions but I can't figure it out.

Thanks for your continued efforts!


Mike.

 

[Trevor G]

Mike try this version, from the link I sent it would have explained about the late binding

Function xlApp()
Dim xlWkbk As Object
Set xlWkbk = CreateObject("Excel.Application")
With xlWkbk
.Workbooks.Open "G:\Sales and Marketing\General\Export\Export Lookup.xls"
.Visible = True
End With
End Function

 

[MikeDuffield]

Hi Trevor,

I did read the page you linked me to initially and I just re-read it, unfortunately I'm not experienced enough to fully understand it. Thanks for bearing with me as I'm learning still...

Am I right in saying that the "Function" you posted is instead of a Sub?

If so, how exactly do I go about linking that Function to my command button?

Sorry if this is a really obvious question, I just can't seem to get it to work


Cheers,
Mike.

 

[Trevor G]

Sorry Mike if it seems as though I put another hurdle in your way.

Just copy the lines of code within the function and place them in your command button event for on click, rather than worrying about the function.

 

[MikeDuffield]

Bingo!

Works perfectly - thanks for your help Trevor, very much appreciated and will stop me from tearing the rest of my hair out!!

Mike.

 

[Trevor G]

Bingo!

Works perfectly - thanks for your help Trevor, very much appreciated and will stop me from tearing the rest of my hair out!!

Mike.

Pleased it has worked Mike

 

[ghudson]

To simply open a file, you can use the Application.Hyperlink function.

Application.FollowHyperlink "G:\Sales and Marketing\General\Export\Export Lookup.xls"

I use the below to open files...

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Function OpenFile(sFileName As String)
On Error GoTo Err_OpenFile

    OpenFile = ShellExecute(Application.hWndAccessApp, "Open", sFileName, "", "C:\", 1)

Exit_OpenFile:
    Exit Function

Err_OpenFile:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_OpenFile

End Function

To open the file, call it like this...

OpenFile "G:\Sales and Marketing\General\Export\Export Lookup.xls"

 

[boblarson]

And to TrevorG and anyone else:

If you use Trevor's code:

Function xlApp()
Dim xlWkbk As Object
Set xlWkbk = CreateObject("Excel.Application")
With xlWkbk
.Workbooks.Open "G:\Sales and Marketing\General\Export\Export Lookup.xls"
.Visible = True
End With
End Function

You should also destroy your app variable. Otherwise if you use it enough it can cause memory problems. What you want to do is relinquish to user control first so it doesn't close the Excel file:

Function xlApp()

Dim xlWkbk As Object

Set xlWkbk = CreateObject("Excel.Application")

With xlWkbk
.Workbooks.Open "G:\Sales and Marketing\General\Export\Export Lookup.xls"
.Visible = True
End With
 
[COLOR=red][B]xlWkbk.UserControl = True[/B][/COLOR]
[COLOR=red][B]Set xlWkbk = Nothing[/B][/COLOR]

End Function

 

[Trevor G]

Bob excellent tip thank you.