Results 1 to 6 of 6
  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51

    Macro to import two columns from excel into existing table and update the data based on key.

    Hello

    I have an excel spreadsheet, with 12 columns, where I only need to import data based on the last two (The rest are needed to fill out the last column.)

    As shown on the picture I have a table in Access called "ProjektDelledning" where the two columns DelledningID and SaneringsmetKode are already present. If a DelledningID is already present it needs to update the SaneringsmetKode, if it isn't present in the table, it needs to add the value. Is this possible with a Macro? So far all I can get is importing af full Excel workbook, not just based on two columns.

    Any help would be much appreciated.





    Click image for larger version. 

Name:	Excel.jpg 
Views:	9 
Size:	261.7 KB 
ID:	24410Click image for larger version. 

Name:	Acces1s.jpg 
Views:	9 
Size:	196.2 KB 
ID:	24412

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using transferspreadsheet, you can specify the range to import (L:M) and if you table does not allow duplicates in DelledningID then these will be ignore if they already exist

  3. #3
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    First of thank you for the reply, been a bit much to do lately, so only got around to testing it today.

    My import gode is as follows:
    Code:
    Public Function Import2Columns()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ProjektDelledning", "C:\Users\JGJ\Desktop\Sanering.xlsx", True, "SaneringsVurdering!L:M"
    End Function
    However I get this error:
    Click image for larger version. 

Name:	ErrorImport.png 
Views:	6 
Size:	46.3 KB 
ID:	24464

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry - think I misunderstood what you wanted to do

    try a different approach.

    1. link to your spreadsheet (can use transferspreadsheet for this or do it through the external data tab) - call it the same name as your worksheet for simplicity in the examples below. Once linked, if you subsequently make changes in the spreadsheet they will be reflected in the linked table so this is a one time exercise


    2. create an update query to update existing records - something like

    Code:
    UPDATE ProjektDelledning INNER JOIN Concatenate ON ProjektDelledning.DelledningID= Concatenate.DelledningID
    SET ProjektDelledning.SaneringsmetKode = Concatenate.SaneringsmetKode

    3. create an append query to add new records - something like

    Code:
    INSERT INTO  ProjektDelledning (DelledningID, SaneringsmetKode)
    SELECT Concatenate.DelledningID, Concatenate.SaneringsmetKode
    FROM  ProjektDelledning LEFT JOIN Concatenate ON ProjektDelledning.DelledningID= Concatenate.DelledningID
    WHERE Concatenate.DelledningID Is Null

  5. #5
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    I can get number 2 to work, but the append query doesn't give me anything. Tried to change it up a bit, where it asks to update 5 rows, but says

    The name, Concatenate has been changed to SaneringTilImport in revised edition, sorry for the trouble.

    Code:
    INSERT INTO ProjektDelledning ( DelledningID, SaneringsmetKode )
    SELECT SaneringTilImport.DelledningID, SaneringTilImport.SaneringsmetKode
    FROM ProjektDelledning RIGHT JOIN SaneringTilImport ON ProjektDelledning.DelledningID = SaneringTilImport.DelledningID
    WHERE (((ProjektDelledning.DelledningID) Is Null));
    In datasheet view, I get the 5 entries shown, but when running it I get an error due to validation rule violations.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it should be a left join

    FROM ProjektDelledning RIGHT JOIN SaneringTilImport

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-25-2015, 01:44 PM
  2. Replies: 1
    Last Post: 04-25-2015, 01:41 PM
  3. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  4. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  5. Import Excel data into existing Access Table
    By octsim in forum Import/Export Data
    Replies: 4
    Last Post: 10-24-2013, 07:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums