Running an excel macro from access VBA

[lovemuscle2005]

Hi All

I have a bit of a problem.

I have an excel sheet which within in holds a MS Query. I have in the excel book a simple macro:
Autpen()
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Application.WindowState = xlNormal
Application.WindowState = xlNormal
Application.WindowState = xlNormal
Application.WindowState = xlNormal
Application.WindowState = xlMinimized
Application.WindowState = xlNormal
Application.WindowState = xlMaximized

I'm trying to run this from Access to try and make a process seemless. Can anyone tell me how to convert this to be able to call excel to run it

As always thanks in advance

 

[tinkythomas]

You could try this code which is what I use to open excel after exporting data from access. You would then simply put your excel macro in the workbook module and use the open method.

Then when excel is opened by access the macro will execute. There are of course other ways of doing this, I think you can hard code excel vba within access aswell, but I have not been sucessfull in getting this to work yet!

Hope this helps?

    Dim oXL As Object
    Dim sFullPath As String
    Dim srcfile As String
    srcfile = "C:\Yourfilepath\YourFilename.xls"

Set oXL = CreateObject("Excel.Application")

    On Error GoTo ErrHandle
    sFullPath = srcfile

    With oXL
        .Visible = True
        .Workbooks.Open (sFullPath)
    End With

ErrExit:
    Set oXL = Nothing
    Exit Sub

ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit

 

[lovemuscle2005]

Thanks tinkythomas worked a treat!!!

 

[tinkythomas]

No probs!!

 

[rconverse]

You can specify the macro for Excel to run as well in case you were going to run mulitiple macros. Also, this will force open a new instance of Excel each time it runs. If you put this in a loop, you would end up with "?" amount of Excels open. If this is only going to launch Excel once, then it may not be a big deal, otherwise, you might want to check and see if Excel is already open. If so, you probably don't want to keep creating Excel instances.

    Dim oXL As Object
    Dim sFullPath As String
    Dim srcfile As String
    srcfile = "C:\Yourfilepath\YourFilename.xls"

Set oXL = CreateObject("Excel.Application")

    On Error GoTo ErrHandle
    sFullPath = srcfile

    With oXL
        .Visible = True
        .Workbooks.Open (sFullPath)
        .Run "YourMacroHere"
    End With

ErrExit:
    Set oXL = Nothing
    Exit Sub

ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit

Thanks,
Roger

 

[tinkythomas]

Hi Roger,

I have never been successful in running excel macro's in this manner. When an instance of excel is opened by access, my add-in does not load. I cannot figure out why this is?

If I uncheck and then re-check the add-in it's fine, it's just the initial opening of excel! I have tried to set a reference to the .xla but it then error's and say's there is already a reference with that name?

It would be better if I could use the method you suggest, because this would avoid the need to store the code in the workbook module. It wouldn't matter then if the file was overwritten.

Regards,
Chris

 

[rconverse]

Hmm. Curious. Which add in? I am assuming you need it to run your macro. Have you started a thread to see if anyone knows a workaround?