Solved: Does this make sense?

[stanl]

I've been out Excel development but have been placed back into Office 2007/2010 where I am updating a hidden data tab with SQL queries from Access. Other sheets in the workbook contain Pivots and charts related to the data in that tab.

There can be no macro code in the workbook, so I accomplish this with a script from outside. The code is recognizable if you know VBA, so I would like to set out the process I envision to update the hidden tab with new data.

First: the tab is Named Disco_Out, a range name Disco is set to the usedrange and it is not a dynamic range.

So,

[vba]oXL.ActiveWorkbook.WorkSheets("Disco.Out").Cells.Clear
For Each N In oXL.ActiveWorkbook.Names
If N.Name="Disco" Then N.Delete
Break
Next[/vba]

after which I perfom the Access Query to obtain a Recordset [oRS], then

[vba]oXL.ActiveWorkbook.WorkSheets("Disco.Out").Range("A1").CopyFromRecordset(oR S)
oXL.ActiveWorkbook.Names.Add Name:="Disco", RefersTo:=oXL.ActiveWorkbook.WorkSheets("Disco.Out").UsedRange[/vba]

after which I save the workbook, and hopefully all other tabs are updated to reflect the new data in the hidden tab.

Appreciate any comments, criticisms....

[stanl]

Well all worked as plans with one caveat - you cannot update a hidden sheet, so I added code to unhide, then re-hide after the copyfromrecordset. I'm very pleased as this presents a template for 20 or more such operations. Now, just have to figure out how to auto-update Sharepoint.