如何从其他应用程序或其他excel文件2003调用excel的宏 [英] How to call a macro of excel from other application or other excel file 2003

问题描述

大家好，



我有一个关于在excel中调用宏需要你帮助的错误。



我写了一个宏setColor（）来设置背景颜色并将值设置为excel文件A的sheet1，然后结束于excel文件B，我调用了文件A的这个宏，但是有一个bug。
$ b在excel 2003中$ b

我的代码只能设置值，不能为文件A设置背景颜色，虽然这个宏在文件A中调用时运行正常。



在这种情况下，我通过excel 2007打开文件A和文件B，它运行正常。

那么我的代码有什么问题吗？和如何我的宏可以在excel 2003和2007上运行甚至excel 2010？

这可能是代码：

文件A（calMacroOtherFile.xls）：

Hi All,

I have a bug about call a macro in excel that need your help.

I wrote a macro setColor() to set background color and set value to sheet1 of excel file A, end then at excel file B, I called this macro of file A, But there a bug.

in excel 2003 my code only can set value and can't not set background color for file A, althought this macro run OK when called at file A.

in the case, I open both file A and file B by excel 2007 , it run OK.
So do My code have any problem? and How to My macro can run at both excel 2003 and 2007 even excel 2010?
this is may code:
file A(calMacroOtherFile.xls):

Public Sub setColor()
    With Workbooks(Application.ThisWorkbook.Name).Worksheets("Sheet1")
        .Range(.Cells(3, 5), .Cells(6, 5)).Interior.ColorIndex = 3
        .Cells(3, 4).Interior.ColorIndex = 15
        .Cells(3, 4).Value = "ckfjfio"
        MsgBox "Workbook Name: " & workBookName
    End With
End Sub

文件B：

file B:

Private Sub CommandButton1_Click()
    On Error GoTo ErrorCode
    Application.Run ("'calMacroOtherFile.xls'!setColor()")
    Exit Sub
ErrorCode:
    MsgBox "Error discription: " & Err.Description

End Sub

提前感谢

Thank in advance

推荐答案

而不是ThisWorkbook [ ^ ]使用 ActiveWorkbook [ ^ ]，一切都应该没问题。按照链接了解差异。



示例：

Instead ThisWorkbook[^] use ActiveWorkbook[^] and everything should be OK. Follow the links to understand the difference.

Example:

Public Sub setColor()
    With ActiveWorkbook.Worksheets("Sheet1")
        .Range(.Cells(3, 5), .Cells(6, 5)).Interior.ColorIndex = 3
        .Cells(3, 4).Interior.ColorIndex = 15
        .Cells(3, 4).Value = "ckfjfio"
    End With
End Sub

在上面的示例中，单元格的颜色正在改变活动工作簿。如果打开了3个工作簿，则仅对活动工作簿进行更改。如果更改活动工作簿然后执行宏，则会对实际活动的工作簿进行更改。

试试这个

In above example the color of cells is changing for active workbook. If there are 3 workbooks opened, then changes are made only for active workbook. If you change the active workbook and then execute macro, changes are made for actually active workbook.
Try this

Private Sub ChangeColorOfCellsForEachWbk()
    For each wbk in Application.Workbooks
        setColor()
    Next
End Sub

要从vbscript调用宏，请参阅以下内容：

http://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or -batch-file [ ^ ]

http://stackoverflow.com/questions/10881951/how-to-launch-an-excel-macro-from-command-line-without-worksheet-open-event [ ^ ]

这篇关于如何从其他应用程序或其他excel文件2003调用excel的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！