Results 1 to 5 of 5
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Adding Data From Excel File

    Hi Guy's WGM and Micron kindly helped me with this, it's frustrating because i can't where i am going wrong here as i have moved the code to a combo instead of a button



    I have put 3 Records from row 6 on the excel file xlSht1, it is adding the 3 correctly plus another 265 blank records, so tried rsDel to delete records where the name is null (265 Blank Records)

    Just tried adding 6 records from row6 and still adding 265 blank records!!!

    Code:
     Set xl = CreateObject("Excel.Application")    Set xlWrkBk = GetObject(srcPath & srcFile)
        Set xlsht = xlWrkBk.Worksheets(1)
        Set xlsht2 = xlWrkBk.Worksheets(2)
        Set xlsht3 = xlWrkBk.Worksheets(3)
            myCust = Forms!frmMainMenu!frmIndex1!cboCustomer
            cAdd1 = DLookup("Add1", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
            cAdd2 = DLookup("Add2", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
            cTown = DLookup("Town", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
            cPC = DLookup("Postcode", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
        
        LastRow = xlsht.UsedRange.Rows.Count
        myRow = "6"
        For i = myRow To LastRow
            c1 = xlsht.Cells(i, 2).Value ' Del TO
            c2 = xlsht.Cells(i, 3).Value 'Town
            c3 = xlsht.Cells(i, 4).Value 'PostCode
            c4 = xlsht.Cells(i, 5).Value 'ItemNo
            c5 = xlsht.Cells(i, 6).Value 'SL
            myDealer = c1
            myPC = c3
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblEdit WHERE 1 = 0") 'you don't use these values so load an empty recordset on the table
            With rs
                .AddNew
                !Customer = myCust
                !CustAdd1 = cAdd1
                !CustAdd2 = cAdd2
                !CustTown = cTown
                !CustPostCode = cPC
                !ShipmentDate = Null
                !PONumber = "PO-"
                !SONumber = c5
                !itemType = "Item"
                !ItemNo = c4
                !TotalBox = "0"
                !TotalRail = "0"
                !DelTo = c1
                !Town = DLookup("Town", "tblDealers", "[PostCode] = '" & c3 & "'")
                !Add1 = DLookup("Add1", "tblDealers", "[PostCode] = '" & c3 & "'")
                !Add2 = DLookup("Add2", "tblDealers", "[PostCode] = '" & c3 & "'")
                !PostCode = myPC
                !Added = "New"
                !Status = "Planning"
                !DueDay = ""
                !Source = "Acc"
                !Pasted = True
                .Update
            End With
        Next i
        rs.Close
        Set rs = Nothing
            Set rsDel = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo Is Null")
                With rdels
                    Do Until rsDel.EOF
                    rsDel.MoveFirst
                    rsDel.Delete
                    rsDel.MoveNext
                    Loop
                End With
                rsDel.Close
            Set rsDel = Nothing
            
            Me.frmIndex1DS.Visible = False
            Me.frmIndex1DSPaste.Visible = True

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well you need to check what excel thinks is the last row?
    I am going to have this tattooed on my forehead 'WALK THROUGH THE CODE WITH F8'
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    WGM, i have got this sorted, I am pulled away too many times and concentration from i left off is causing it I think!!!

    I think I should start leaving code work until interruptions don't interfere !!!!

    Thanks anyway

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I once went on a time management course, the end result was to manage YOUR time efficiently. So if someone came up to me and asked for help, I was meant to say I cannot help you now, but I can in 30 minutes' or whatever. So that was me sorted, but the poor sod who had to wait until I was free was not so productive.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Wgm

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

Similar Threads

  1. Replies: 3
    Last Post: 06-26-2020, 12:37 PM
  2. Replies: 3
    Last Post: 08-07-2019, 02:57 PM
  3. Adding sheet & file names to Excel import
    By sportyaccordy in forum Import/Export Data
    Replies: 2
    Last Post: 03-05-2019, 10:37 AM
  4. Replies: 3
    Last Post: 11-29-2016, 09:29 AM
  5. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 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
  •  
Other Forums: Microsoft Office Forums