Results 1 to 12 of 12
  1. #1
    Join Date
    May 2007
    Posts
    92

    Unanswered: Importing Excel File to Access Database

    Hi everyone,

    I want to create a button in the form such that it will automatically transfer the excel data into the Access Table..

    For eg. Excel files name CP file, I only one the first spreadsheet to transfer into the access. Another criteria, I want 6 excel files to transfer automatically into the access database with the click of the button.

    The last criteria is that I want to delete the first row of the each spreadsheet before I want to transfer the 6 files into the access database..

    Thank...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are the 6 files in a single static location with non-changing file names?

    In fact, more importantly; WHY do you want to do this?
    George
    Home | Blog

  3. #3
    Join Date
    May 2007
    Posts
    92

    Import Excel File

    The 6 files are in the same location situated in 1 folder and non-changing file name. I only need to import the 1st spreadsheet from each of excel file.

    As for the first row i need to delete because it only show the header of the file and that is not important to put it in the database..

    Therefore, I need to import the data from the 1st spreadsheet from each of excel file and for each spreadsheet, i will need one table for 1 spreadsheet.

    Can you show me some examples???

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There are some greate examples in the code bank which cover issues such as FSO (opening a dialogue box to prompt for file selection) and Excel manipulation (posted by pootle flump). These should cover almost everything you need.

    Have a play and see how you get on.
    George
    Home | Blog

  5. #5
    Join Date
    May 2007
    Posts
    92

    Import Excel Spreadsheet into the Access Table

    I have check the code bank but i need to import Excel Spreadsheet into the Access Table by create a button in the Access form.. I cannot find the things I need...

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Translation: you want someone to do thsi for you.

    If you're not willing to put the effort in and at least try then nor am I.
    George
    Home | Blog

  7. #7
    Join Date
    May 2007
    Posts
    92

    Importing Excel File to Access Database

    But the code bank is export to excel spreadsheet ya.. Not import the data from Excel. The other thing the manually get external data i have try but however i need it to automate the process..

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please post the code you have tried.
    George
    Home | Blog

  9. #9
    Join Date
    May 2007
    Posts
    92

    Importing the Excel file into Access Database

    What have I try is that I import the table by getting the external data.. However, I need to do some adjust in the data which is import to the Access table. First is the field name and second is the column 1 and row 1 of the data in the spreadsheet which I do not want. As for the 2 rows, it should be the field name of the access database.. I am not sure... How to do the automate part cause i am not an expert..

    As for the code, I am also do not whether the DoCmd.transferSpreadsheet is suitable for my situation.

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would suggest trying to create a Macro to import the Excel spreadsheet. The macro editor will help you with the parameters for importing. Then it is much easier to figure out DoCmd.TransferSpreadsheet.

    Another thought that I had is, you don't need to import the data. I typically link to a file, then use a couple of queries to append the data to existing tables. Or you can create a Make Table query to create the data.

    If you are going to do it regularly (weekly) then I would suggest linking so you don't have to add the data twice to your database.

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I agree with DCKunkle, I have a link to a spreadsheet that is updated each day. I append this linked table to a another table while appending I set the criteria to not append the record that has the header it could be "Employee name"

  12. #12
    Join Date
    May 2007
    Posts
    92

    Import excel spreadsheet into Access

    Hi everyone,

    How to write the macro such that I can specify from which column and row of the excel spreadsheet data that I need to transfer to the 1 Access Table?

    I need to automate the process of transferring the data..

Posting Permissions

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