Question Behavior of hyperlinks to excel

[Peartree]

Good evening all, and happy holidays.

I've spent the last month or so developing a simple MS Access database for my team at work to track our daily tasks. Up to this point, I've always been able to find an answer to my Access questions on this board or others like it, but this time no such luck.

We all open the db in the morning and basically keep it open all day. Most of our daily tasks are completed through excel spreadsheets with their own macros and queries. For my database, I simply have a hyperlink field to store locations on the network. These hyperlinks are displayed on a form, which users click on to open each task sheet. For some reason, the worksheets behave differently when opened from the hyperlinks in MS Access than they do if opened from excel itself.

In particular, the macros in the excel sheet fail at places they should not. For example the excel vba code will have a line referencing

Activeworkbook.name

but we will get a runtime error on that line, with activeworkbook.name not getting picked up. This is especially puzzling because these macros still have to be manually kicked off from the main page of the excel book - so I know the book has the focus. If I close the book and reopen it from excel (and not the access link) the very same macro works fine!

Has anyone seen anything like this before? I will be glad to provide the specific runtime error once I am back in the office tomorrow morning if that may help.

Cheers,
Ben

 

[Mike375]

Perhaps it is an attempt by the hyperlink to disable the macro.

The following code opens ComboOnForm.xls and that has an auto_open macro that opens a form. That does not work with the following code, but does when I run it from the macro/run list. Opening the same .xls with a hyperlink causes Vista to go into spinning circle mode Then Control Alt Delete and End Process for Excel.exe brings up a box about to I want open the spceific file and a Yes opens it and runs the auto-run.

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\ViperResides\ComboOnForm.xls")
Set oSheet = objXLBook.Worksheets(1)
objXLApp.Visible = True

The following on the OnClick for a button

Forms!ln!Command5.HyperlinkAddress = ("C:\ViperResides\" & Format([Forms]![ln]![LNameNoDoc]) + ".xls")

also stalls on the same file because of the auto_open. As side note using that method by passes the normal hyperlink warning. Usig the normal hyperlink label via insert in form design also causes the auto-open to stall everything

The fact that hyperlink either as an insert via form design or by the above stalls because of an auto_open suggest to me that hyperlink is not deal for opening such files.

The code above is bypassing the auto_open so I think it might be OK to open your xls files. If that is not a success then I would search around for VBA open Excel. I have very little experience with Excel and even less with opening from Access but I can tell you that opening Word from Access has all sorts of variatons with different results, depending on want you want to.

Perhaps another way to consider would be to open a very simple xls from Access that just has a form with a combo and use that combo/list to then open the desired xls.

 

[Peartree]

Hi Mike,

Thank you for the response - I believe using vba to open the file instead of a simple hyperlink solves a lot of my problems! I have now, however run into another problem which I am also having a difficulty finding a solution for.

When I open my spreadsheet with a command similar to that above, any function from the analysis toolpak will not work (#value). the strange thing is that if I look at Tools->Add Ins it appears both Analysis ToolPak and Analysis Toolpak - VBA are selected!

I have checked my Access references, and I have the Microsoft Excel 11 Object Library selected - we use excel 2003 so I think this should be correct?

 

[Mike375]

When I open my spreadsheet with a command similar to that above, any function from the analysis toolpak will not work (#value). the strange thing is that if I look at Tools->Add Ins it appears both Analysis ToolPak and Analysis Toolpak - VBA are selected!

I have checked my Access references, and I have the Microsoft Excel 11 Object Library selected - we use excel 2003 so I think this should be correct?

My knowledge of Excel is extremely limited. I make some things in Excel that replicate some things I do in Access but that involves backup system, date/stamping files and opening Word and inserting cell data in to Word bookmarks. I only used that code to open Excel from Access from a bit of playing I was doing to insert Access data into Excel cells. I chopped that part of the code off. Based on what I do with Access to Word I feel if you search and play around you will get some VBA that will open Excel without the problems.

Another very simple approach would be to have your Excel files in a specific folder and use

Call Shell("explorer.exe c:\Letters", vbNormalFocus)

That will open the folder in the normal manner and then you open the Excel file direct.

Another way would be to use a batch file to open Excel file. I don't know if you have used batch files but they are very simple when it comes to opening a file, for example

cd\Letters
exp.xls

You can run the batch file with RunApp in a macro or use Shell.

You could use Shell to open the Excel file. Here is one for doing Word

Call Shell("C:\Program Files\Microsoft Office\Office11\Winword.exe C:\Letters\0Letter2Mike.doc")

One advantage of using Shell instead of a batch file is you could have the file name drawn from an entry in a textbox. Thus if you had 12 Excel files you could make a table and enter the file name for each and have a tabular form display with a button to run the Shell and it would open the appropriate file. I do this Word.

I am extremely confident that a batch file will open Excel and it will be the same as if you opened it directly and I think Shell will be the same but I am not as confident as I am with a batch file.

Come to think of, for what you are doing the type of VBA I put up earlier is not required since you are not wanting to controll Excel from Access, you just want to open the .xls file.

I don't know if seen the site for the link below, but if not, it is the reverse of this site in the action is all Excel and a quite Access forum.


http://www.mrexcel.com/forum/index.php

 

[Peartree]

Thanks Mike, I will take a look at using a batch file (haven't worked with them before but does not sound like the one I need would be that complex).

Best,
Ben

 

[Mike375]

Real simple.

Open NotePad and type in the path.

You might have

cd\FolderName
FileName.xls

Then from the NotePad menu do File>SaveAs and it will probably default to a file name like *.txt. Change that to Something.bat

You can then either run the bat file from a RunApp action on a macro or use Shell or make a shortcut to te toolbar.

When a bat files runs you will briefly see the black DOS screen flash open.

If you have trouble with it then add Pause at the end of the code and that will hold the DOS screen open so you can see any error messages.

 

[Peartree]

You da man Mike, the call shell command from vba works beautifully. Much smoother than using hyperlinks from Access.

Thanks Again,

 

[Mike375]

Glad the the suggestions worked. And thanks for the feedback.