Results 1 to 3 of 3

updating/append excel files to a access table with VBA

  1. #1
    Ricardo Caicedo is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    11

    updating/append excel files to a access table with VBA

    Hello Gurus
    I am not new in VBA, but just rookie in Access VBA. I got a problem with my code. I need to open several excel files created by other areas and update a database (a table call Customers that needs update and/or append the record if does not exist or update other records if exist).
    The excel files have the following characteristics (see down here picture):

    • The data that I need to update starts at a variable row, so in order to know where I need to look for the word “Rate”
    • Once the word Rate is found, this row contains the name of the fields to be updated/appended
    • There is an empty row after this row
    • The quantity of rows that can be updated/appended is also variable

    Column A Column B Column C
    Row 1
    Row 2
    …..
    Row N
    i.eA23
    Rate Customer Account # Premise #
    Empty row
    Row N+2 Lml22 Customer 1 1178954 54544
    Row N+3 Lml5 Customer 2 1175489 1254

    My Access Table has the same name fields that the excel files, however the field “Premise #” is the principal key and is indexed.

    My code works until the use of DoCmd.TransferSpreadsheet, as it give an error message that the records were lost due to key violations so I do not know if I cannot use this Method.According to the metod, UpRange is a string that contains the data from the excel file in the example case is “A23:D408” Here is my code (includes a macro to enter the file from a directory that works - call selectFile not included):

    Sub PopulateCorrectAdd()

    Dim i As Long, j As Long, RowStart As Long, LastRow As Long
    Dim Rw As Long
    Dim UpRange As String, Rowloc As String

    Dim xlApp As Object
    Dim WorkBk As Object
    'Dim sh As Excel.worksheet
    Call SelectFile
    'Open excel file and find first record to update--------------------------------------------
    Set xlApp = CreateObject("Excel.Application")


    xlApp.Visible = True
    'Set WorkBk = appExcel.Workbooks.Open("FolderPathData")
    xlApp.Workbooks.Open FolderPathData, True, False
    Debug.Print xlApp.Version
    Worksheets(1).Cells.Find(What:="Rate", after:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Rowloc = ActiveCell.Address
    Rowloc = Mid(Rowloc, 4, 2)
    RowStart = Val(Rowloc)
    ActiveCell.Offset(2, 0).Select
    LastRow = ActiveCell.CurrentRegion.Rows.Count + RowStart + 1
    UpRange = "A" & RowStart & ":" & "D" & LastRow
    'Debug.Print sh.Name
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customers", _
    FolderPathData, True, UpRange
    'WorkBk.Close


    Set xlApp = Nothing
    xlApp.Quit
    End Sub


    thanks I appreciate any help

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,224
    Advise not to use spaces and special characters/punctuation in naming convention.

    Any method that attempts to insert records that cause duplication of primary key will have issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    Are you sure that each Premise # is unique within Excel?

    To test your routine, empty the table that you're putting the data into, or put the data into a empty temporary table with the same format.
    If the routine works, change to put data into the temp table, then you just need to do an append/update query to move the data into the permanent table from the temporary table.

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

Similar Threads

  1. Import multiple Excel Files into 1 access table
    By jurbin in forum Import/Export Data
    Replies: 1
    Last Post: 05-15-2015, 01:45 PM
  2. Replies: 1
    Last Post: 03-25-2014, 08:54 PM
  3. Browse for excel files and append to Access table.
    By newbieX in forum Programming
    Replies: 5
    Last Post: 01-27-2014, 07:10 PM
  4. Replies: 1
    Last Post: 06-27-2012, 07:39 AM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 PM

Posting Permissions

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