| |
こんにちは
申請書のあるブックでAlt+F11キーでVBE画面を開いて標準モジュールを挿入して
Public Declare Function SetTimer Lib "user32.dll" _
(ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32.dll" _
(ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Sub DeleteDefinedNames()
Dim beforeReferenceStyle As Variant
beforeReferenceStyle = Application.ReferenceStyle
Dim timerID As Long
timerID = SetTimer(0, 0, 100, AddressOf TimerProc)
If beforeReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
Dim n As Name
For Each n In ActiveWorkbook.Names
If Not n.Name Like "*!Print_Area" And _
Not n.Name Like "*!Print_Titles" Then
n.Delete
End If
Next
Application.ReferenceStyle = beforeReferenceStyle
KillTimer 0, timerID
End Sub
Private Sub TimerProc()
Dim hwnd As Long
hwnd = FindWindow("bosa_sdm_XL9", "名前の重複")
If hwnd > 0 Then
SendKeys getRandomString(3, 20), 10
SendKeys "{ENTER}"
End If
End Sub
Private Function getRandomString(min As Long, max As Long) As String
Dim s As String
Dim i As Long
max = Int(max * Rnd)
For i = 0 To min + max
Randomize
s = s & Chr(65 + Int(26 * Rnd))
Next
getRandomString = s
End Function
のコードを貼り付けて、マクロ実行で「DeleteDefinedNames」を実行するだけですけど、
うまく起動しないとは、どのような状態でしょうか？
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
