Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber field from VBA

    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.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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???

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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????

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Autonumber field from VBA

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

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber field from VBA

    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.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Autonumber field from VBA

    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! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •