connect to excel and delete data

[teel73]

Hello, I am trying to connect to excel from Access database. Once I make the connection I want to delete data from a range on a specific sheet. There are 4 columns on the spreadsheet but i wouldn't know how many rows. So for example, A150... but I may not know what the last row is. How would I be able to delete data from a range of columns to the last row?

 

[Frothingslosh]

You can declare a range of columns by just using the headings, for example "A:F" contains columns A through F in their entirety.

 

[teel73]

So if I do this:
Sheets("Access_Data").Range("a33").ClearContents

That will get rid of the data in all rows down to the last row of data?

 

[jdraw]

There is a sample (Access 2003) at this link.

There is a youtube video here showing a linked Excel record being modified from Access. Again this is Access 200-2003.

I don't work with Excel, but I was under the impression you could not change linked Excel data from Access. Maybe it's just with later versions????

 

[Frothingslosh]

Linked, no. Accessed directly via automation, yes.

And teel73, to do what you're wanting, you're going to need to know the first row of data and the last row of data. For the first row, you can hardcode it if it'll never change, or you can search down the spreadsheet to determine the first row containing actual data.

The last row is actually easier: The quickest way to get the last row would be (worksheet).UsedRange.Rows.Count.

And fyi, the range A33 just selects A3, B3, C3, and D3, not the entire columns. If you want to affect all of row 3, then the range would actually be just "3". If you want, say, rows 3 through 5, your range would be "3:50".

So assuming you want to keep your headers, your range would be "<top left>:<bottom right>". Top left is easy - the first column and row of data to be deleted, such as A3. Bottom right requires the count from UsedRange. If your last row of data is line 50, and you're clearing columns A-D, then the bottom right is D50, making your range "A350".