+ Reply to Thread
Results 1 to 11 of 11

write to access from excel using a range instead of per field name

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    write to access from excel using a range instead of per field name

    Hi all,

    I have the below code to write to access database with excel.
    I would like to know if it is possible to change a little bit the code to use a range instead of per field as I would like to add more records to the database then just row by row.

    Any help is very appreciated.

    Please Login or Register  to view this content.
    Greetings.

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    Hi all,

    I get only problems here:
    Please Login or Register  to view this content.
    can't find project library

    Greetings

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: write to access from excel using a range instead of per field name

    Once you add your ADO reference, you should be able to insert records using code like this:

    Please Login or Register  to view this content.
    Note: there are memory leak issues with running queries against an open workbook so it would be better if you could run the code from a different workbook with the data one closed, but you will probably be OK if you only need to run this once or twice per session.
    Remember what the dormouse said
    Feed your head

  4. #4
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    Hi,

    The ADO is not working, what ever I do. Just no more clues what could go wrong.

    Greetings.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: write to access from excel using a range instead of per field name

    You need to add a reference to the Microsoft ActiveX Data Objects 2.8 library (note you may have 2.7 or 2.1 or other version if you don't have 2.8)

  6. #6
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    Hi,

    I did, but it could be that excel lost the dll file somehow.
    Where can I find it (folders)?

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: write to access from excel using a range instead of per field name

    I don't know what you mean - Excel doesn't lose .dlls

    If you set the reference to say the 2.1 version, that should be available on all machines.

  8. #8
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    I did reinstall excel 2003 and now I have 2003 and 2007. What was working before, is not working now. But also not on 2003.
    I guess something was overwritten.

  9. #9
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    Hi,

    maybe late binding will help out. but I don't know how I could change the above code to make it work.
    any ideas?

    Greetings.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: write to access from excel using a range instead of per field name

    Late bound version of the original code:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Austria
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: write to access from excel using a range instead of per field name

    Hi,

    It worked out :-)
    Thanks a lot :-)

    Greetings.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to write data in access from excel?
    By mrrizwan in forum Access Tables & Databases
    Replies: 3
    Last Post: 04-25-2014, 08:51 AM
  2. Write to Access from Excel ?
    By spookiepower in forum Excel General
    Replies: 2
    Last Post: 08-05-2013, 08:58 AM
  3. Replies: 9
    Last Post: 05-22-2012, 08:59 AM
  4. Read/Write Access DB from VBA in Excel
    By excelbobabc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2008, 05:45 PM
  5. Replies: 3
    Last Post: 09-23-2005, 03:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1