Accessing other Office files through a Command Button in Excel 1

[Tigerman68]

Hi. I am creating an excel spreadsheet to serve as a communication "bulletin board" for my staff at work. I need the VBA coding that can allow me to program a command button in Excel to open other Office documents. Merging the documents in a spreadsheet is not what I'm interested in doing. I just need a command button to open an Office document when the user needs it.

Any help will be much appreciated! Thank you!

Tigerman68

PS. I eventually want to write a VB program to do the same thing so it can be a stand alone application, however, I'm not far enough in VB yet to know how to do it.

 

[Bong]

You may find the DDEInitiate command useful:

Application.DDEInitiate Method
Opens a DDE channel to an application.expression.DDEInitiate(App, Topic)
Return Value
Long

expression A variable that represents an Application object.

Parameters

Name Required/Optional Data Type Description
App Required String The application name.
Topic Required String Describes something in the application to which you're opening a channel — usually a document of that application.

Remarks


If successful, the DDEInitiate method returns the number of the open channel. All subsequent DDE functions use this number to specify the channel.


Example


This example opens a channel to Word, opens the Word document Formletr.doc, and then sends the FilePrint command to WordBasic.

Visual Basic for Applications
channelNumber = Application.DDEInitiate( _
app:="WinWord", _
topic:="C:\WINWORD\FORMLETR.DOC")
Application.DDEExecute channelNumber, "[FILEPRINT]"
Application.DDETerminate channelNumber

Likewise, the application object:

Application Object
Represents the entire Microsoft Excel application.
Remarks


The Application object contains:

Application-wide settings and options.
Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.


Example


Use the Application property to return the Application object. The following example applies the Windows property to the Application object.

Visual Basic for Applications
Application.Windows("book1.xls").Activate

The following example creates a Microsoft Excel workbook object in another application and then opens a workbook in Microsoft Excel.

Visual Basic for Applications
Set xl = CreateObject("Excel.Sheet")
xl.Application.Workbooks.Open "newbook.xls"

_________________
Bob Rashkin

 

[Tigerman68]

Thanks for the response, Bong. I'm not well hearsed in VBA, so I will need to try and sort out the info you posted and give it a shot in my spreadsheet.

I assume I would include all of the following by substituting the "WinWord" with "excel" and then type in the path where the document is located?

Thanks again.

 

[Bong]

Yes and no. If you're opening Excel from Excel, then you don't need quite so many magical incantations. Is that what you're doing?

_________________
Bob Rashkin

 

[Tigerman68]

Yes. That's basically it. One Excel workbook will serve as a control panel to open other workbooks located on the same server.

I would rather have a VB program serve as the control panel, but I wouldn't know how to code it and figure it's more complicated than using Excel with VBA.

Thanks.

 

[Bong]

That makes things much simpler. From within Excel, you can open another workbook with

Workbooks.Open (fnam)

where "fnam" is the name of the .xls file. This could be returned from a file browser for instance:

fnam = Application.GetOpenFilename()

_________________
Bob Rashkin

 

[SkipVought]

Tigerman68,

If you frame and focus you question properly, you get better answers sooner.

Communication Bulletin Board:

Why not a sheet with hyperlinks to other workbooks? No need for buttons and VBA code, is there?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Tigerman68]

Thanks, Bong. I will give that a shot. I really appreciate the help.

SkipVought, what exactly do you mean "frame and focus"? I'm new to this forum, so if there's a better way I can post questions, I would welcome the advice. To answer your question, yes, I could use hyperlinks, but using command buttons is a cleaner way to operate the "control panel" I'm creating. Thanks for your response.

Tigerman68