Autonumber field from VBA

Topic

New Reply

Hi

I have an Excel 97 spreadsheet which i have to import into Access 97. For various reasons to do with data spread across two rows instead of one (a bad, bad idea, i know), i want to create an autonumber field in the spreadsheet before importing.

From Access, i am able to locate the spreadsheet, add a new column, but am now looking for an easy way to add the autonumbers for each row where there is data.

I guess it will be a loop of some kind where intAutonumber = intAutonumber + 1????

Many thanks.

Reply | Quote

Replies

Something like this might help:

Dim r as range
For each r in YourWorksheet.Usedrange.rows
r.range(“A1”) = r.row
next

This sets the first cell in each row equal to the row number of the row, for each row in the range that has data (assuming that they are all together).

Jon

Reply | Quote

Thanks Jon for your prompt reply. I’ve been looking all morning for a module in an old database that had the code i’m looking for, but it continues to elude me.

Can you tell me how i create a field in the worksheet please?

Cheers

Reply | Quote

A field is nothing more than a column, and a row constitutes a record. If you want to give the field a name, just enter a title in the the first row of the column.

You don’t really need code to give a number to each record. If say you want row 2 (assuming Row 1 contains field names) to be given the number one you could set up a blank column, say A, and in A2 enter =ROW()-1. Copy that formula down the extent of your data, and each row should have it’s own unique number.

Andrew C

Reply | Quote

Thanks for your comments Andrew.

Not sure if i made myself clear, but what i want to do from within my import code in Access is add a new column to the worksheet and update each row with a unique number, i.e. the equivelant of autonumbering a table in Access.

Reply | Quote

To insert a column and fill it full of unique numbers the code could be something like:

Dim r As Range
Columns(1).Insert shift:=xlToRight
For Each r In ActiveSheet.UsedRange.Rows
Cells(r.Row, 1) = r.Row
Next

See how you go with that.

Jon

Reply | Quote

Hi Jon.

Thanks once again for your help. I’m getting there slowly, and although i am experienced in Access VBA, I am quite new to Excel programming. In fact, i’ve even been out this morning and bought MS’s Excel VBA book to give me a start. 🙂

OK, to my problem. I’m now able to create the new blank column, but i’m getting a “Method or Data Member not found” error message on the “row” bit of this line:

Cells(r.row, 1) = r.row

I know from Access programming that you’re usually going OK if you use one of the choices offered in the box that comes up after typing the dot, but in this case “row” is not offered.

Have i got something wrong???

Reply | Quote

I’m somewhat bemused, as I tested this code and it worked fine under Excel 97.

The only suggestions I can make are:
1) have you included the Dim r as range statement at the top?
2) If so, can you show us the code for the subroutine with the offending section in? It’s always easier if we can see what you’re actually doing.

Jon

Reply | Quote

Well, i’ve solved it!!

After loads of reading about automation and looking at dozens of irrelevant examples i tried changing:

Dim r as range

to:

Dim r as Excel.Range

and hey presto!

What i’d like to know is why i have to put this and you don’t????

Reply | Quote

Hi Peter,
Are you running your code within Access? If so, that’s the difference.
Hope that helps.

Reply | Quote

Hi Rory

Yes, i am running this code from within Access. Am i correct in saying i will always need to use the bit before the dot (sorry about the technical reference 🙂 )whilst referencing Excel (or any other Office app. whilst in Access?

Anything else i should look out for???

Thank you for your contribution.

Reply | Quote

Hi Peter,
The helpful answer is “it depends”! If you don’t have any references set to object libraries that also include that particular object, then you shouldn’t need to include the ‘Excel.’ qualifier. For example, Range objects also exist in the Word and Graph libraries, so if you have references set to one or more of those, you will usually (but not always in my experience) need to explicitly declare it to be an Excel.Range object. Generally speaking, I’d recommend an explicit declaration to be safe because otherwise you’ll find that your code will usually compile perfectly well but will throw out runtime errors, which are often less than clear as to exactly what the problem is! It’s similar to the ADO versus DAO issue with Access 2000 – they contain some of the ‘same’ objects, but those objects have different properties and methods depending on whether you use ADO or DAO.
I hope that made sense but if not please repost and I’ll try and clarify!

Reply | Quote