Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel form into access (2000)

    I would like some advise.

    I have an Excel spreadsheet which is used as a form to request information. We will receive orders on this form. I want to be able to extract information from the form into an Access database.
    The form will not always have the same filename, and will not always reside at the same location. The data I want to import is a named Range in the excel form.

    I need to automate the process so that a user with only basic experience can get perform the import.

    What is the best way to to tackle this? Is it best to try to run and export from Excel or an Import from Access? Does it matter? What is the collective wisdom on such matters?

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

    Re: Excel form into access (2000)

    How do you want to import the range?
    - Into an existing table, or into a new table?
    - Is it a matter of dumping the range into the table (columns = fields, rows = records), or do you need to manipulate individual cells?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel form into access (2000)

    I need each excel data to produce a new record in an existing table

    I just need to dump the data from excel into access. Each Form contains effectively one row of data.(columns = fields, row = record).

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

    Re: Excel form into access (2000)

    One option is to create a macro in Excel that will insert a record into the Access table based on the range in the currently open workbook. You could use DAO or ADO to do this. The user only needs to open the workbook (s)he wants to export from and run the macro. I assume that the Access database will be in a fixed location and that the table will always be the same one, so their names can be hard-coded into the macro.

    It would also be possible to initiate the import from Access.

    It would be nice if you could attach a sample workbook (with dummy data), and provide info about the Access database and table.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel form into access (2000)

    Excel sheet is attached. There is a named Range "ImportCost" which contains the data I would like to import each time into Access.

    The table in Access is called tblCostings and has the field names that match the first row of "ImportCost" plus an autonumber field to give a primary key.

    Any other questions?

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

    Re: Excel form into access (2000)

    Assuming that the AutoNumber field is the first field in the table, you should be able to use the following macro from Excel:

    Sub AddRecord()
    Const strDatabase = "P:GBBSBTelecomsFixed Networks" & _
    "NRSWASystemsAccessCostingsNCCDB.MDB"
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rng As Range
    Dim c As Long
    On Error GoTo ErrHandler
    Set rng = Range("ImportCost")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
    rst.Open "tblCostings", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    For c = 1 To rng.Columns.Count
    rst.Fields© = rng.Cells(2, c)
    Next c
    rst.Update

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    Set rng = Nothing
    Exit Sub

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

    Note: you must set a reference to the Microsoft ActiveX Data Objects 2.n Library in Tools | References...

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel form into access (2000)

    Hans

    It worked perfectly - very slick

    THANK YOU <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>

Posting Permissions

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