CODEWITHSUNDEEP
excelms-accessvba
Michael
Michael

Reputation: 2657

check if named range exists in Excel from MS Access

I use the below VBA in Access to import a range called 'Drop' in Excel, however, how do I check in Access first if the range Drop exists in Excel:

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, "tblCustomerImportDrop", ExcelFilePath, True, "Drop"

Or, is there a way to suppress Error 3011 (which is generated when it can't find the range) and continue with my code?

Upvotes: 1

Views: 663

Answers (2)

tranyo
tranyo

Reputation: 19

        Sub usage_example()
        fpath = "d:\names_test.xlsx"
        findedname = "xxxx"
        ret = existname(fpath, findedname)
        End Sub

        Function existname(fpath, findedname) As Boolean
            Set xlapp = CreateObject("Excel.application")
            Set wb = xlapp.workbooks.Open(fpath)
            existname = False
            For Each nn In wb.names
                If nn.Name = findedname Then
                    existname = True
                    Exit For
                End If
            Next nn
        wb.Close
        Set wb = Nothing
        Set xlapp = Nothing
        End Function

Upvotes: 1

Erik A
Erik A

Reputation: 32642

Suppressing errors in VBA is really simple:

Just add On Error Resume Next before the line that causes the error, and On Error GoTo 0 behind it if you want to see errors for all other lines.

Checking if ranges exist in Excel can be done, but requires you to have the Microsoft Excel Object Library, open up an Excel application, and close it afterwards, unless done by error trapping (I can show you an error trapping and a non-error trapping approach if interested).

Upvotes: 1

Related Questions