Help with Opening an Excel Workbook from macro

[Aboone]

Hello,

I have very little experience with macros and NO experience with writing code so I appreciate your patience in explaining things to me.

I would like to have a command button on a form which will open an exisiting excel workbook. I do not need to import, edit or anything like that, simply have the file open.

How can I accomplish this?

Thanks!

 

[ted.martin]

Hello,

I have very little experience with macros and NO experience with writing code so I appreciate your patience in explaining things to me.

I would like to have a command button on a form which will open an exisiting excel workbook. I do not need to import, edit or anything like that, simply have the file open.

How can I accomplish this?

Thanks!

This should do it. Make the On-Click property of your command button

FollowHyperlink "c:\temp\test.xls"

 

[boblarson]

This should do it. Make the On-Click property of your command button

FollowHyperlink "c:\temp\test.xls"

As this Action doesn't appear to be available in a Macro, it will be necessary to use VBA code to accomplish.

So, if you aren't familiar with how to put code in the VBA window, take a look here:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx

The screenshots there show a different event than you would be dealing with (you want the CLICK event of the button).

 

[Mike375]

This opens an Excel file for me. It is attached to label0 for OnClick. The Private Sub and End Sub are alreayd there when you go to build event. Below the Excel opened is one for Word. I live and breathe macros but for opening an Excel file or Word file and then inserting data from Access, forget macros....no will work


Private Sub Label0_Click()


Dim xlApp, xlBook As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add("C:\Letters\PriceList.xls")

xlApp.Visible = True



Set xlApp = Nothing
Set xlBook = Nothing

End Sub

Private Sub Label120_Click()
Const MSTB_MSWORD = 300&

Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

Dim docname As Object
Set docname = CreateObject("Word.Basic")

docname.FileOpen "c:\Letters\0ACLMike.doc"
End Sub

 

[boblarson]

If just wanting to OPEN the file, it is much less code to use:

FollowHyperlink "c:\temp\test.xls"

instead of having to use the CreateObject method. However, if you want to programmatically do things within the file then you would need to open it with the COM object method.

And, by the way Mike, your line:
Dim xlApp, xlBook As Object

is declaring xlApp as a Variant, because in VBA it doesn't work to use the shortcut of declaring each object separated by a comma and the datatype at the end, unlike VB6 (where I believe you can do that). You should have

Dim xlApp As Object, xlBook As Object

 

[Mike375]

Thanks Bob.

I only use the Excel one to open the xls file.

The Word one I have for opening Word docs and also for inserting into bookmarks, copying the document back to a Memo Access field and print.

When I converted from Access 95 to 2003 (problems with XP) the Access/Word fouled up with EditGoTo. I phoned Microsoft who explained there was a problem. They gave me a couple of links, which were a waste of time but they aslo gave me the name of a company in Sydney. It cost me several thousand to get it fixed. Now there appear to be problems with Vista.

I don't have full details as my business partner purchased a new laptop and he is currently out of Sydney and phone and email contact is limited and he is very computer illiterate. One problem it seems to have is finding its way. Instead of taking a couple of seconds to complete things and print it is about a minute. I assume the coding the company did after conversion from 95 to 2003 was all OK as he replaced all of mine and also put lots of it in a module.

Mike