transfer from Excel to Access
transfer from Excel to Access
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: transfer from Excel to Access

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    Could you help me to solve a problem of transferring Excel cell data to Access. I need to create a Macro in an Excel file so that when user click a button or press certain hot key, values of some cells in some sheets will be transferred to some Access tables.
    I don't mean to export list-formatted Excel to Access table.

    Regards
    XJ

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi XJ,

    Perhaps this will help. This is just a little demonstration code that takes a few values from worksheet cells and puts them directly into fields named FirstName and LastName in an Access Table named "Employees".

    Sub ToAccess()

    Dim axsApp As Object
    'Assume Access already open with desired database
    Set axsApp = GetObject(, "Access.Application")

    Set cdb = axsApp.currentdb
    Set rst = cdb.openrecordset("Employees")

    Dim iRow As Long 'Excel worksheet row number

    'Loop thru first 8 rows on active worksheet and put data from
    'first two columns into Access employees table

    For iRow = 1 To 8

    With rst
    .addnew
    ' read the names from Excel into Access table
    !LastName = Cells(iRow, 2)
    !FirstName = Cells(iRow, 1)
    .Update
    End With

    Next iRow

    End Sub

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a lot, Damon!!
    I believe this is extremely useful for my project!!! I will try it in my project.
    best regards
    Xinjun
    Regards
    XJ

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    North Carolina
    Posts
    122
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: transfer from Excel to Access

      
    Any way to modify the code listed above by Damon (it works great, by the way) such that the Access db doesn't have to be open at the time the Excel macro is executed??? Just wondering...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com