Problem with Follow Hyperlink

[Guest]

I am exporting some data to Excel - and all is going well (thanks to this
group!), and I thought it would be good to give the user the option to check
what they had exported, so here is the code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportExl", _
CurrentProject.Path & "\" & sFile, True

If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
spath = CurrentProject.Path & "\" & sFile & ".xls"
FollowHyperlink spath
End If

What happens is that Excel appears to open, but then the windows "freeze" -
I am left with what I will call the Excel "shell" ie menu bars, round my
Access window. I tried closing down the Access window and then tried to open
the spreadsheet, but it said it was already open.

I feel I am so close!!

Many thanks

Helen

 

[John Nurick]

Hi Helen,

I wonder if this might just be a timing issue. Do you get the same
problem when you step through the code, waiting a few seconds after
TransferSpreadsheet finishes before executing FollowHyperlink? If it
works when you go slowly, try adding a DoEvents between the
TransferSpreadsheet and the MsgBox call; if that doesn't fix it, use the
form's Timer to provide a short delay.

 

[Guest]

Well that certainly seems to help (although I'm not entirely sure what I have
done!

I've entered the code:

openforms = DoEvents()

Before I send the message - and after a short delay the spreadsheet appeared.

Thanks John, was that right??

 

[Guest]

OK I spole too soon - it worked once and then not again - could be something
to do with timing though - could you please give me some more advice on how
to "wait a few seconds"?

Many thanks

 

[John Nurick]

If you haven't already done so, fire up the Windows Task Manager and
check in the Applications tab whether there are any instances of Excel
running that don't show up in the task bar. If there are, use End Task
to terminate them. Alternatively, reboot the computer.

Then use something like this (air code, so you may need to fix some
typos) to wait a few seconds:

Dim UntilTime As Date
Const SecondsToWait = 5#

...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"ExportExl", CurrentProject.Path & "\" & sFile, True

If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
'Wait 5 seconds to give Transferspreadsheet time to complete
UntilTime = DateAdd("s", SecondsToWait, Now())
Do
DoEvents
Loop Until Now() > UntilTime
spath = CurrentProject.Path & "\" & sFile & ".xls"
FollowHyperlink spath
End If

If that doesn't fix the problem, increase SecondsToWait to something
excessive (30 seconds, perhaps). If there are still problems on the
second and subsequent attempts, it's probably not a timing problem after
all.

 

[Guest]

Thanks John, however I'm begining to think that it isn't a time thing.

I booted my machine this morning and guess what - the routine (without your
ammendments) worked fine.

Tried it again and it was ok - ran it once with excel open - and it didn't
work, and now, having first checked that Excel was not running, it won't work.

Put the hyperlink on a separate button - so you do the export and then
choose to view it - and same problem - it appears to try to load and just
hangs!

I've just upgraded to 2003, so don't know if that is the problem - but then
the person that I'm designing this for also has 2003!

Guess I need to look at another way of linking to excel - but this seemed so
neat!

If you have any other thoughts please let me know. (thanks for your work to
date )

Helen

 

[John Nurick]

This sounds like a problem with your Office installation or somewhere in
Windows.

If you've got a file (e.g.) "D:\Folder\MyFile.xls" then this

Application.FollowHyperlink "D:\Folder\MyFile.xls"

should just open it. Check the following:

1) When you double-click on an XLS file in Windows Explorer, does it open
OK?

2) Open Internet Explorer. Type the location of an Excel file (e.g.
D:\Folder\MyFile.xls
into the address bar and hit Enter. Does the file open OK?

3) Open your database and go to File|Database Properties. On the summary
tab, is there a value in the Hyperlink Base property? If so, it's
conceivable that this is upsetting FollowHyperlink, though it didn't when I
tried it just now.

4) Launch Word. Hit Alt-F11 to get to the VBE, then Ctrl-G for the Immediate
pane. Type
Application.FollowHyperlink "D:\Folder\MyFile.xls"
(using the actual location and name of a database file) and hit Enter. Does
the workbook open OK? If not, are the symptoms the same as when you try from
Excel.

 

[Guest]

Thanks John,

In fact I've just (in the last 2 days) upgraded to 2003 - I'll maybe try it
on another machine and then reinvestigate what M$ office is playing at!

Thanks for the help to date