Open XLS and delete Named Range Contents

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I have an Access db that links to multiple named ranges within a XLS
Workbook with multiple Worksheets. Each Nmaed Range is a seperate Linked
Table that I update via queries. I will like to be able to do a "mass
deletion" of all
values that all named ranges contain prior to my update. Can this be done,
if so how?

Thanks,
Anthony
 
Anthony said:
I have an Access db that links to multiple named ranges within a XLS
Workbook with multiple Worksheets. Each Nmaed Range is a seperate Linked
Table that I update via queries. I will like to be able to do a "mass
deletion" of all
values that all named ranges contain prior to my update. Can this be done,
if so how?
Click to expand...


Here's some DAO air code that outlines a way to delete all
records in all linked Excel tables:

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
For Each tdf In db.TableDefs
If Left(tdf.Connect, 6) = "EXCEL " Then
db.Execute "DELETE * FROM " & tdf.Name
End If
Next tdf

Set tdf = Nothing
Set db = Nothing

Be sure you backup the workbook before trying this kind of
thing.
 
That's sort of the code I was using, but I keep receiving the following
message upon execution:
"Deleting data in linked table is not supported by this ISAM"

Any thoughts?
 
Anthony said:
That's sort of the code I was using, but I keep receiving the following
message upon execution:
"Deleting data in linked table is not supported by this ISAM"
Click to expand...

That's the problem with air code, no pudding to put the
proof in ;-)

I was sure that it would work, but I guess I've never tried
to delete records in an Excel range before. Every other DAO
or SQL operation I've ever done on a range worked without
issue, but now I know Delete is not a valid operation.

I tried every which way (short of Automation) and just kept
getting the same ISAM message. I'm not the right guy for
details, but I suggest that you look into using Automation
to do this operation.

Sorry I wan't able to help out here.
--
Marsh
MVP [MS Access]


 
Thanks! You didn't have to go through all that work.
Marshall Barton said:
Anthony said:
That's sort of the code I was using, but I keep receiving the following
message upon execution:
"Deleting data in linked table is not supported by this ISAM"
Click to expand...

That's the problem with air code, no pudding to put the
proof in ;-)

I was sure that it would work, but I guess I've never tried
to delete records in an Excel range before. Every other DAO
or SQL operation I've ever done on a range worked without
issue, but now I know Delete is not a valid operation.

I tried every which way (short of Automation) and just kept
getting the same ISAM message. I'm not the right guy for
details, but I suggest that you look into using Automation
to do this operation.

Sorry I wan't able to help out here.
--
Marsh
MVP [MS Access]


Click to expand...
 
Anthony said:
Thanks! You didn't have to go through all that work.
Click to expand...

Oh, but I did!

I really hate passing on bad advice, so I had to at least
try to understand what was going on here. Besides, this was
my "Learn Something New Every Day" item ;-)
 
Back
Top