Consulting

Results 1 to 7 of 7

Thread: Importing excel data into Access using macro

  1. #1
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location

    Importing excel data into Access using macro

    Hi

    If I had a A1:C3 table in an excel worksheet called "Client details" with column headings "ClientID" = A1, "Firstname" = B1, "Lastname" = C3

    Then if I filled in the table with data i.e in cells (A2:C3) and then wanted to create a button where if I clicked then it would transfer the data in the table including the column headings into an Access database, what code would I assign to the button?

    Regards

  2. #2
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    Use the following code, replacing "R:\Data\Databases\Old\TASQ10 - 2.accdb" with the path\filename of your database:

    [vba]Dim daoDB As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    Dim i As Long
    Set daoDB = DBEngine.OpenDatabase("R:\Data\Databases\Old\TASQ10 - 2.accdb")
    Set tbl = daoDB.CreateTableDef("tblClientDetails")
    Set fld = tbl.CreateField("ClientID", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("FirstName", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("LastName", dbText, 255)
    tbl.Fields.Append fld
    daoDB.TableDefs.Append tbl
    daoDB.TableDefs.Refresh
    Set rs = daoDB.OpenRecordset("tblClientDetails")
    With ActiveSheet.Range("A1")
    For i = 1 To .CurrentRegion.Rows.Count - 1
    rs.AddNew
    rs("ClientID").Value = .Offset(i, 0)
    rs("FirstName").Value = .Offset(i, 1)
    rs("LastName").Value = .Offset(i, 2)
    rs.Update
    Next i
    End With[/vba]

    You will need to set a reference to the Microsoft Office ##.0 Access database engine Object Library.

  3. #3
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location
    Many thanks for your reply. I'll just try that now.

  4. #4
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location
    Sorry for the delay. Can't quite seem to get it working. Are you using an early binding method here?

    I can set the reference fine. But once I run the code it doesn't work. It highlights the first line of code.

  5. #5
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    What version of Office are you using?

    The reference that I have set here is to the Microsoft Office 14.0 Access database engine Object Library.

    In addition, there are references to :

    Visual Basic for Applications
    Microsoft Excel 14.0 Object Library
    OLE Automation
    Microsoft Office 14.0 Object Library

  6. #6
    VBAX Regular
    Joined
    Feb 2013
    Posts
    51
    Location
    Right I've referenced all 5 of those. I'm using Office 2010. I think the problem could be setting the correct path to my database. I've saved the Access file where I want the data to be extracted to. It is called: Extraction.accdb

    I put in this line as I've saved the Extraction file to my desktop:

    Set daoDB = DBEngine.OpenDatabase("C:\users\user\Desktop\Extraction")

    Thanks

  7. #7
    VBAX Contributor
    Joined
    Oct 2012
    Location
    Brisbane, Queensland, Australia
    Posts
    163
    Location
    I would think that you would also need the file extension and of course unless your user name is "user", then you would need to replace that in the path to the database with the actual user name.

Posting Permissions

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