问题描述
是否可以在打开Excel文件时自动执行我的宏(update())。以下代码不正常。感谢
Is it possible that my macro (update () ) auto-executes everytime the excel file is opened. The code below doesn't work well. Thanks
Private Sub Workbook_Open()
Run "update"
End Sub
Option Explicit
Sub update()
Dim rng As Range
Dim Sh As String, Cl As String
Dim ws As Worksheet
Dim i As Integer, ncol As Integer
Dim Row1 As String
ncol = Range("B1:O1").Columns.Count
For i = 1 To ncol
Set ws = ThisWorkbook.Sheets("sheet1")
With ws
Row1 = .Cells(1, i).Value
If Len(Row1) > 0 Then
Sh = Split(Row1, "'!")(0)
Cl = Split(Row1, "'!")(1)
Set rng = ThisWorkbook.Sheets(Sh).Range(Cl)
'Here you were always refering to cell A2 not moving through the values which was the main problem.
rng.Value = .Cells(2, i).Value
End If
End With
Next i
End Sub
推荐答案
如评论中所述。移动以下内容:
As mentioned in the comments. Move the following:
Private Sub Workbook_Open()
Run "update"
End Sub
到这里:
如上所述Siddharth还有另一种方法可以让宏运行在文件打开的事件上,也就是简单地给出以下签名:
As mentioned by Siddharth there is another way to get a macro to run on the file open event and that is to simply to give it the following signature:
Sub Auto_Open
另外,个人我可能不会调用一个子例程只是更新因为它非常接近许多保留字 - 我会去像updateSomething这样的东西。这只是个人选择。
Also, personally I'd probably not call a sub-routine just "update" as it is quite close to lots of reserved words - I'd go for something like "updateSomething". This is just personal choice.
这篇关于宏打开时自动执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!