How do you clear a named range in Excel from Access?

[Big Pat]

Hi,

I use the code below to loop through a table and export data to a named range in almost 100 different spreadsheets. These are identical in all but the filename, which changes by record.

It works fine if I manually open all the sheets first and clear the ranges in all of them, but otherwise I get a "Cannot expand named range" error.

Is it possible to clear each named range first? If so, can you tell me the code for that?

I'm no programmer and it took me long enough to figure out the DoCmd.TransferSpreadsheet part!

I'm using Access and Excel 2010.

Thanks

DoCmd.SetWarnings False

Dim MyFilename As String   ' Value will be set repeatedly in the loop
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT [CRN-WM Trusts].ODSCode, [CRN-WM Trusts].Trust FROM [CRN-WM Trusts] WHERE ((([CRN-WM Trusts].Type)='Trust') AND (([CRN-WM Trusts].Current)=True))ORDER BY [CRN-WM Trusts].Trust;"

Set rs = CurrentDb.OpenRecordset(strSql)

With rs

If Not .BOF And Not .EOF Then   
        .MoveLast
        .MoveFirst
        
        While (Not .EOF) 
        
        Me.lstTrusts.Value = rs.Fields("ODSCode")  

        MyFilename = DLookup("[Trust]", "CRN-WM Trusts", "[ODSCode]=[Forms]![frmMenu]![lstTrusts]") & " - Recruitment and ABF.xlsx"
        
        DoCmd.OpenQuery "Trust 002 Monthly recruits - part 2 - make table"  'this year     
        DoCmd.OpenQuery "Trust 005 Monthly recruits - part 2 - make table" 'last year

   [COLOR="Red"]    ' Code here, to clear the named range first?[/COLOR]
        
        DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="Trust 009a Recruitment only", FileName:="G:\Database\Trust Reports\Weekly Reports\" & MyFilename, Range:="Trust009a"
        
        .MoveNext     
        Wend
        
    End If
    
    .Close                     
End With 

Set rs = Nothing 

DoCmd.SetWarnings True

End Sub

 

[arnelgp]

add reference to Microsoft Excel X.XX Object.

dim xl as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

set xl = new excel.application
set wb = xl.workbooks.open("path and filename of excel")
set ws = wb.sheets("sheet name")
ws.names("NamedRange").Delete
set ws=nothing
wb.close True
set wb=nothing
xl.quit
set xl=nothing

 

[Big Pat]

Are you saying I need all of that code in place of the one red line in my code? If not then where does it go? Would this be a whole separate operation e.g. open all the workbooks, clear the ranges, close them and then move onto my existing code?

And wouldn't that DELETE my named ranges? It took me some time to get these right and then duplicate the file almost 100 times, so what I really want to do is clear the contents of each range, from within the loop.

 

[arnelgp]

sorry, lost there.
if you only want to delete the "content" of the named range and not the named range itself:


dim xl as excel.application
dim wb as excel.workbook

set xl = new excel.application
set wb = xl.workbooks.open("path and filename of excel")
xl.Goto Reference:="NamedRanged"
xl.Selection.ClearContents
wb.close True
set wb=nothing
xl.quit
set xl=nothing


you can wrapp the code in a sub or function.
create a sub in a Standard Module:

Public Sub delNamedRangeContent(wrkBook As String, RngName As string)
dim xl as excel.application
dim wb as excel.workbook

set xl = new excel.application
set wb = xl.workbooks.open(wrkBook)
xl.Goto Reference:=RngName
xl.Selection.ClearContents
wb.close True
set wb=nothing
xl.quit
set xl=nothing

End Sub


now substitute this for the Red Line:

Call delNamedRangeContent("WorkbookPathAndNameHere", "RangeNameHere")