Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003

    Question Unanswered: Importing data from .xls or other files in an Access Table


    I need tot fill in daily hondreds of records in a database from a file i can easily export to excell or a text file or whatever. So i thaught i should automatise this and save a lot of time. But i really don't know where to start.
    WHat i done a few times is moving rows and colums , copy paste and make that the columns in excell are the same numnber and format as in my Access table and then copy and paste to access. But to arrange all data in excell to same format and number of colums also takes a lot of time. And there is another problem ; if in excell i have qty 10 of one item i need to have 10 diferent lines in my table. So what i did in excell before is adding rows and copy same thing to 10 different rows.

    Anybody any idea where i can start ?

    Thanks a lot.

  2. #2
    Join Date
    Jun 2003
    Put the field names in the top row of the Excel file and let it just act as a straight table.

    And in Access link to the table:

    File | Get External Data | Link Tables...
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Sep 2003

    Importing data in acess table

    Thank you for your advice. I tried it but all it does is link an excell table in acess. The problem is i need to import data from excell or other format file in an existing access table. I was more thinking of some visual basic code that will tell access to copy all data from column X from row Z to row Y and paste it in acees field F and so on....
    Another problem is that on the data i have to imort the description is like : 10x Item1 and this should be translated to 10 separate records with identical informatin and not just one record with qty 10.
    Don't forget the layout of my data in excell or rtf or whatever is not same as the layout in access. i wille have to skip some rows and colums and the nuber of rows can also be different every time.

    Any idea?


  4. #4
    Join Date
    Jan 2004
    Toronto, Canada
    Here's some Excel code i wrote before; the range takes the value of the cell;

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim rng As Excel.Range

    Set xlBook = GetObject("Q:\Combined.xls")
    Set xlApp = xlBook.Parent

    Set xlSheet = xlBook.Sheets("Sheet 1")

    For i = 6 To 347 'the number of rows
    Debug.Print xlSheet.Range("A" & i & "").Value
    '..Do stuff with that cell; if you want to copy to db, use
    'recordset and such

Posting Permissions

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