Running an excel macro from within VB

[SmagO]

Hi all,
I need to run a silly little macro from a command button in VB.

This is the macro from excel:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/01/2002 by MAGrosoft
'
' Keyboard Shortcut: Ctrl+q
'
    Range("A1:A6").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\My Documents\Book2.xls"
    Range("A1").Select
    ActiveSheet.Paste
    Range("C5").Select
End Sub

its just a cut and paste thingy.
what I want is to be able to call that macro from VB
Any suggestions as to how I do that?
could I dim variabvles in this macro (VBA) so that I can change the selections and filenames, and then pass the parameters from VB across to the VBA macro?

Thanks in advance

[trisuglow]

Hi Smag,

Do you already have an Excel object in your code (like XLApp in the fragment below)? If you do then can't you do what you want as I've shown? I have to admit I haven't tried this code, but its very similar to something that I did in Word VBA.

By the way, what part of SA are you from?


Tris.

Public XLApp As Object

Set XLApp = CreateObject("Excel.Application", "")
XLApp.ScreenUpdating = True


XLApp.Documents.Open FileName:="c:\filename.xls"
XLApp.Range("A1:A6").Select

Set XLApp = Nothing

[SmagO]

Thanks, Im from Observatory in Johannesburg, why u ask?

I tried the code but it is not registering what XLApp is. should I be using ADO?

[kbort]

it's been awhile since i used this hope it helps!

Shell "C:\program files\microsoft office\office\excel.exe C:\filename.xls", vbMinimizedFocus
ddechan = DDEInitiate("Excel", "System")
DDEExecute ddechan, "[Run(""filename.xls!macroname"")]"

[trisuglow]

Ok, I've been a little less lazy than last time and tried the code out before I posted it!

I'm running VB6 on NT and I'm using Excel97.

I created a spreadsheet called "Filename.xls" in C:\ with just a couple of random bits of text in it. The following code is the entire code from Form1 of a new VB Project. I haven't added any references or anything.

It works for me. Let me know how it goes for you.

btw, the location question was because I've just got married and my wife is from Fish Hoek, Cape Towm. I was hoping for an unlikely coincidence to brighten up my day...

Option Explicit

Public XLApp As Object

Private Sub Form_Load()

    Set XLApp = CreateObject("Excel.Application", "")
    XLApp.ScreenUpdating = True
    XLApp.Visible = True
    XLApp.Workbooks.Open FileName:="c:\filename.xls"
    XLApp.Range("A1:A6").Select

    MsgBox "Excel should be open right now with A1:A6 selected. " & _
            "(You may need to click on Excel to actually see the " & _
            "selection.)", vbMsgBoxSetForeground

    XLApp.quit
    Set XLApp = Nothing

End Sub