Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel Spreadsheet

    Hi,

    I have an access table and form with address information fields of:

    Salutation
    FirstName
    LastName
    Address1
    Address2
    Address3
    Address4
    City
    Postcode
    AskingPrice
    Type
    Bedrooms
    WebLink

    I have an excel file with the same column headings in row 1 and then data anywhere from 2 rows to 50 rows at a time.

    I will be reguarly importing the data each week (usually new data) into the table to then interogate against our existing records in other tables and produce mailmerges.

    I would like to have a form whereby I can have a command button with vba to select the file I want to import and then it imports the data from the spreadsheet into the access table. When it does this, I would ideally like it to check first to see if the information exists in that table (to stop duplication) via the vba code.

    I have never got that far with importing a spreadsheet via vba or being able to work how to check first before importing.

    Assistance would be appreciated on this from anyone that could help......
    Best Regards,

    Luke

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Lstclair55 View Post
    Hi,

    I have an access table and form with address information fields of:

    Salutation
    FirstName
    LastName
    Address1
    Address2
    Address3
    Address4
    City
    Postcode
    AskingPrice
    Type
    Bedrooms
    WebLink

    I have an excel file with the same column headings in row 1 and then data anywhere from 2 rows to 50 rows at a time.

    I will be reguarly importing the data each week (usually new data) into the table to then interogate against our existing records in other tables and produce mailmerges.

    I would like to have a form whereby I can have a command button with vba to select the file I want to import and then it imports the data from the spreadsheet into the access table. When it does this, I would ideally like it to check first to see if the information exists in that table (to stop duplication) via the vba code.

    I have never got that far with importing a spreadsheet via vba or being able to work how to check first before importing.

    Assistance would be appreciated on this from anyone that could help......
    You haven't said which version of Access/Excel you have! Unless you have a really old version (pre 2002) you should be able to use Application.FileDialog to prompt for a filename using the standard Windows File Open dialog. You can then use DoCmd.TransferSpreadsheet to import a given range, from a named file, into a specific table, AND tell Access that there are field names. Given that there aren't many rows involved, I would be inclined to name a range covering the max expected rows of data, and deal with blank rows after import.

    How are you going to tell what is duplicated? Do you have a unique record identifier that you can look at or will you need to examine every field in a record? I would be inclined to import the Excel data into a temp table with the same structure as the "real" table, then check for duplicates and either report them or run an insert query to copy from temp to real accordingly.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your response.

    I am using Access 2000 and Excel 2000.

    The table will have a Primary Key.

    The way to identify whether there are duplicate records will if the first two lines of the address fields (address1 and address 2)are matched anywhere.
    Best Regards,

    Luke

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Lstclair55 View Post
    Thank you for your response.

    I am using Access 2000 and Excel 2000.

    The table will have a Primary Key.

    The way to identify whether there are duplicate records will if the first two lines of the address fields (address1 and address 2)are matched anywhere.
    OK, simplest part first(!): I would suggest doing what I proposed and setting up a temp table e.g. tblTemp with the same structure as the main table to hold the imported records. I would also set up a SELECT query joining the main table to the temp table on 2 fields - address1 and address2. If you have duplicates, this query will contain some records and if there are no duplicates, there will be no records in this query. You can check this in VBA by opening the query as a recordset and checking the recordcount property.

    Selecting the file is going to be more difficult since you only have Access 2000. To start with, I would write a DoCmd.TransferSpreadsheet command to import a named range into the temp table, but hard-code the file name.

    I think to use file selection in Access 2000 you need Windows API functions and a wrapper around these, but I haven't done this for years since I upgraded my Office!

    Are you OK to work on the TransferSpreadsheet and query checking code? I can have a look to see if I have any legacy code for file selection.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have never used the TrasnferSpreadsheet elements, so any sample code would be greatly appreciated.
    Best Regards,

    Luke

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Lstclair55 View Post
    I have never used the TrasnferSpreadsheet elements, so any sample code would be greatly appreciated.
    Here's something I've lifted from an old database to get you started:
    Code:
     ' import all the relevant ranges from the file into corresponding tables...
     ' parameters indicate: import or export, Excel version, table name, file name, HasFieldNames,range
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TABBASE", strWB, True, "TABBASE"
    Those are my table and range names in the command, and strWB is a string variable I was using to hold the file name.

    If you enter a command like this in the VBA behind a command button, you can get help on the TransferSpreadsheet command to see more details. I think you are going to have to change the second parameter as you have an old version of Access and Excel, so the file type I've used won't be recognized!

Posting Permissions

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