Excel macro from Access 2

[SDS100UK]

Hi,

Is it possible to call a MS Excel macro from within MS Access?

And if so how so????

Many thanks in advance

Steven

 

[DougP]

you have to establish an connection beteen the two.

look at these 3 in Access Help

1. Dynamic Data Exchange (DDE)
2. Shell Command
3. ShellExecute

here is a sample (DDE) Example from Help
-----------------------------
Sub ExcelDDE()
Dim intI As Integer, intChan1 As Integer
Dim strTopics As String, strResp As String, strSheetName As String

On Error Resume Next ' Set up error handling.

intChan1 = DDEInitiate("Excel", "System&quot ' Establish link.
If Err Then ' If error occurs, Excel may
Err = 0 ' not be running. Reset error
Shell "C:\Excel\Excel.exe", 1 ' and start spreadsheet.
If Err Then Exit Sub ' If another error, exit.
' Establish Spreadsheet link.
intChan1 = DDEInitiate("Excel", "System&quot
End If

' Create new worksheet.
DDEExecute intChan1, "[New(1)]"
' Get topic list, worksheet name.
strTopics = DDERequest(intChan1, "Selection&quot
strSheetName = Left(strTopics, InStr(1, strTopics, "!&quot - 1)
' Terminate DDE link.
DDETerminate intChan1
' Establish link with new worksheet.
intChan1 = DDEInitiate("Excel", strSheetName)
For intI = 1 To 10 ' Put some values into
DDEPoke intChan1, "R1C" & intI, intI ' first row.
Next intI
' Make chart.
DDEExecute intChan1, "[Select(""R1C1:R1C10"&quot][New(2,2)]"
' Terminate all links.
DDETerminateAll
End Sub


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[p27br]

Hi

Here's an easier way from the microsoft site. It applies to any office application

Sub XLTest()
Dim XL as Object

Set XL = CreateObject("Excel.Application&quot

XL.Workbooks.Open "C:\My Documents\ExcelFile.xls"

' If there is more than one macro called TestMacro,
' the module name would be required as in
'
' XL.Run "Module1.TestMacro"
'
' to differentiate which routine is being called.
'
XL.Run "TestMacro"

End Sub

hope it helps

paul

 

[GaryRW]

Mind if i jump onto this thread?

I have tried the above (and all manor of variations) but keep getting the error that the macro cannot be found. I have tried placing the macro in the personal workbook, in workbook that I actually want it to work on (which is loaded) and always no response. Any ideas? I just can't get the macro to work (I have tried with a simple msgbox test macro and still no luck)

The other problem I have is that I am trying to start excel, load a file, run a macro (to change some cells and then save under a different name) and then leave excel open for the user to work on the workbook.

I am setting myExcelApp = nothing, but obviously haven't closed the application down. I am getting problems starting excel and loading the file second time around.