CODEWITHSUNDEEP
excelvba
user4599
user4599

Reputation: 127

how to resolve ambigous name detected error in excel

I have written macro which saves current workbook after every ten second,

    Option Explicit
    
    Public ONTIMER_S As Date

Public Sub SaveBook()
    ThisWorkbook.Save
    
    ONTIMER_S = Now() + TimeValue("00:00:10")
    Application.OnTime ONTIMER_S, "SaveBook"
    
End Sub

this works fine for one workbook, I want to run this macro on multiple workbooks, when I try to add this macro to any other workbook I get error " ambigous name detected Savebook "

Is there any way to run this macro on multiple workbooks at same time, either by writing this macro to each workbook, or somehow running this macro on multiple workbooks

Upvotes: 0

Views: 134

Answers (1)

Tomasz
Tomasz

Reputation: 426

You can set n workbooks in one main code in one main workbook like below

 Option Explicit

Public ONTIMER_S As Date

Public Sub SaveBook()
    dim wb1 as workbook, wb2 as workbook
    dim name1 as string, name2 as string
    
    name1 ="name1"
    name2 = "name2"
    if IsWorkBookOpen(name1 )=true then 
        set wb1= wborkbooks(name1 )
        wb1.save
    end if
    if IsWorkBookOpen(name2 )=true then 
        set wb2= wborkbooks(name2 )
        wb2.save
    end if
    
    set wb1 =nothing
    set wb2 = nothing
    ONTIMER_S = Now() + TimeValue("00:00:10")
    Application.OnTime ONTIMER_S, "SaveBook"

End Sub

function to check is wb open

Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Upvotes: 1

Related Questions