Call an Excel Module

[matt]

Hello,
Is there a way I can call a Module that is in "personal.xls" and have it run
through Word? I want it to execute the Module that I wrote in Excel on a
Linked table from Excel, that is in my word doc.
I think the Module is called "Module 1" and inside that it is called "Sub
Continue_Open()" which is located in personal.xls
Thanks,

 

[alborg]

Hi Matt:

Microsoft Excel offers some functions that are not available in Microsoft
Word, for example, statistical functions. However, you can access these
functions from within Microsoft Access by using Automation if you first set a
reference to the Microsoft Excel object library.

To create a reference to the Microsoft Excel object library, follow these
steps:
1. Open the Word VBA platform (i.e. View-> Toolbars-> Select "Visual Basic".
2. Click on the Visual Basic icon to get into the Visual Basic platform.
3. On the Tools menu, click References.
3. In the References box, select the Microsoft Excel Object Library
appropriate for your version of Microsoft Excel, and then click OK.

After the reference is created, you can reference all the Microsoft Excel
objects, properties, and methods, including the functions available through
the Application object.

Cheers,
Al

 

[alborg]

Oh- forgot to add the VBA code, which should look something like this (run
from either a UserForm or a toolbox clickbutton):

Sub Run_Excel_Macro()

Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "personal.xls"
strMacro = "Module1"

Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

'Run Macro
xls.Run strMacro

Set xls = Nothing
Set xlWB = Nothing

End Sub

That should do it...

Regards,
Al

 

[matt]

Hey alborg,
Thanks for your response. When I run the code you gave me it opens the excel
file, but it does not show me personal.xls in the project explorer window. If
I open the file normally from its folder and then go to the VBA editor I can
see and edit personal.xls in the project explorer, I just can't see it when
it opens it from the code.

I played around a little with the code and it does open a Macro that I call
directly from the excel file that needs editing. But I cannot see
personal.xls.. Any idea why?

You may be wondering why I just don't write the macro directly in the main
excel file that I have. It is because every morning I get this file in an
email, and I save it and overwrite the one from the day before. So anything
that was written in the VBA editor for that file gets erased.

Thanks,
Matt

 

[alborg]

Hi Matt:

Is it that you just don't see the workbook maximized? Try this code fix-

--------------------------------------------------------
Private Sub Command0_Click()
Dim xls, xlWB As Object
Dim strFile, strMacro As String
Dim Excelwasnotrunning As Boolean ' Flag for final release.
Dim wdWindowStateMaximize As Long
wdWindowStateMaximize = 0
On Error Resume Next

strFile = "personal.xls"
strMacro = "module1"

Set xls = CreateObject("Excel.Application")
xls.Application.Visible = True
If err.Number <> 0 Then 'test to see if an error occurred
Set xls = CreateObject("Excel.Application")
End If
If Excelwasnotrunning = True Then
xls.Application.Quit
End If
With xls
.WindowState = wdWindowStateMaximize
End With
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" & strFile)

xls.Visible = True

'Run Macro
xls.Run strMacro

Set xls = Nothing
Set xlWB = Nothing

End Sub