Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export a Named Range from Excel to Access... (2000 SR-1)

    Hi all...

    I'm looking for code to export a named range directly from Excel to an Access 97 database...
    The reason I want to do it this way, rather than import from inside Access, is that I want it automated... and there is a new excel file created every day... with difference names... The database that the data is going to is always going to be in the same location and exported into the same table...

    Specifically... in VBA, I want to say:

    Export the named range, "Prices", from this workbook... to "ThePathandDBName.mdb", table named Prices... Yes, the first row contains headings... and Yes, overwrite the existing table...

    Can this be done?
    Thanks in advance! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Trudi

    Please take a look at <!post=this message from Rory,280250>this message from Rory<!/post> , and the whole thread if you like.

    If you have any additional questions, please post a reply.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    An alternative would be to use Automation to start Access from Excel. You need to set a reference to the Microsoft Access 9.0 Object Library in Tools | References...

    Sub ExportPrices()
    Dim appAccess As New Access.Application
    Dim strRange As String
    Dim strDatabase As String
    Dim strTable As String

    On Error GoTo ErrHandler

    ' Modify the following strings as needed
    strRange = "Prices"
    strDatabase = "serversharefoldermyDatabase.mdb"
    strTable = "Prices"

    appAccess.OpenCurrentDatabase strDatabase
    On Error Resume Next
    appAccess.DoCmd.DeleteObject acTable, strTable
    On Error GoTo ErrHandler
    appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, ActiveWorkbook.FullName, True, strRange

    ExitHandler:
    appAccess.Quit acQuitSaveNone
    Set appAccess = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  4. #4
    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: Export a Named Range from Excel to Access... (2000 SR-1)

    Wassim (and Trudi),
    The code in my post may need revision if the named range contains more than one row (which I imagine it does). The For Each rngCell loop may not go through the range in the order required! (I confess I haven't tested it.) It would be safer to use row and column variables and loop through that way.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    Thanks Wassim... I chose Hans' automation suggestion, simply because the named range has quite a few fields and I'm feeling lazy today... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> ...but thanks for the alternative... I am sure I will use it at another time!

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    Thanks Hans...

    I only have Access 97... so I had to reference the 8.0 Object Library... and in turn, change the "acSpreadsheetTypeExcel9" to "acSpreadsheetTypeExcel97"... but it worked perfectly! ... I had planned to do it this way, but the filename part of the import statement had me stumped... I didn't know about the FullName property of the ActiveWorkbook... I really need to get an Excel VBA book...

    Thanks, once again, for teaching me something new... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    Thanks Rory... I saw that it would need some revision... I've done the For Next loop in Excel VBA for other situations...
    I always appreciate the guidance!

    Have a great weekend! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export a Named Range from Excel to Access... (2000 SR-1)

    I'm sorry, you did state that it was for Access 97, but I just looked at the 2000 in the subject. Glad you figured it out.

Posting Permissions

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