Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Changing Excel Cell Data

    Hi Guy's, is ther ean easy method to change details of excel data from sheet2 to sheet1 from Row 6 ?

    ie: if i have data on sheet2 as below

    Do a count on sheet 2 from A1 And paste on Sheet 1 from Column/row B6

    SHEET2
    Col A ColB ColC ColE
    123456 SL-123456 Company1 Ltd Smith
    789101 SL-789876 Company2 Ltd Jones



    I need this on sheet1 as:

    SHEET1
    ColB ColE ColF
    Company1 Ltd 123456-Smith SL-123456 (Note Column B is Column A And E taken from Sheet2 with a hyphen in between the 2 fields)
    Company2 Ltd 789101-Jones SL-789876
    etc....

    If 29 rows used on sheet2 then

    next records

    ???

    Thanks guys

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I'd start with recording a macro to get the moves.
    Then surround with a loop for the range to work on.?
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    or look here
    http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Might not fit the definition of easy, but I've always had success.

    So double posted??
    https://www.accessforums.net/showthread.php?t=84588

    I hate wasting my time providing the same suggestions that someone else already has provided.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Micron View Post
    or look here
    http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Might not fit the definition of easy, but I've always had success.

    So double posted??
    https://www.accessforums.net/showthread.php?t=84588

    I hate wasting my time providing the same suggestions that someone else already has provided.
    That is a different thread Micron, but it was sort of duplicated one way and then the other.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My apologies to this OP. Got my browser tabs mixed up, but the sentiment is the same - just that it applies there, not here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    why not just have sheet 1 with calculated cells to get the data from sheet 2?

    And why use access for a purely excel action?

  7. #7
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you guy's, i think i can adapt existing code ranman sent on my last post

    Ajax, thank you, the reason is the db adds the amount of records and data dependant on rows used in excel

    ranman sent me a code idea which worked very well, now I have had to change the goal post so the data now required is in the wrong sequence but that is sent to me rather than us generating the data so i need to copy from a shared file and paste into a worksheet but the way it is sent to me is the wrong way around so i am going to test cells a,b,c,d from sheet2 is cells a in sheet1 is a & d from sheet 2, cells c in sheet2 is cells e in sheet1 etc.....

    I am going to do some testing so i can learn also, i think testing is a good way to understand providing you can reach the goal required

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I will try somethings and if i get stuck, you great people may be able to help so i will get back if im stuck

    greatly appreciated

  9. #9
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guy's here is where i m testing to get data from xl sht2 and combine / change columns as xlsht1 columns are different and there is one combined data (mfg & "-" & enduser)

    I find it quite bizarre that i am getting debug on DLookup("Town" line, when i hover over c3 in the Dlookup Line, it does display the correct delto name in the 1st cell

    So my target is to copy all used rows on sht2, do my combination and the look up and update sht1 with the info

    Hoping i have made some kind of sense with this but i would typically get an error due to wrong method whereas the debug is a line that is normally simple!!!!

    Code:
    Dim stAppName As String, myURL As String, OpenChrome As String, myApp As String, stPathName As StringDim myCust As String, cAdd1 As String, cAdd2 As String, cTown As String, cPC As String, myDealer As String
    Dim c1 As String, c2 As String, c3 As String, c4 As String, c5 As String, c8 As String, myPC As String
    Dim xlRow As Long, LastRow As Long, ColCount As Long, LastRows2 As Long
    Dim myRow As Integer, i As Integer, c6 As Integer, c7 As Integer, DelQty As Integer, myRows2 As Integer, i2 As Integer
    Dim FindRow As Range
    Dim xl As Excel.Application
    Dim xlsht As Excel.Worksheet, xlsht2 As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook
    Dim rsDel As DAO.Recordset
    Dim a1 As String, a2 As String, a3 As String, a4 As String, a5 As String
    Dim DelTown As String, DelPC As String
    
    
    srcPath = "T:\DMT Ltd\XL Files\"
    srcFile = "New Items.xlsx"
    
    
        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 & "*""")
    
    
        Set xl = CreateObject("Excel.Application")
        Set xlWrkBk = GetObject(srcPath & srcFile)
        Set xlsht = xlWrkBk.Worksheets(1)
        Set xlsht2 = xlWrkBk.Worksheets(2)
        xl.Visible = False
    
    
        LastRow = xlsht2.UsedRange.Rows.Count
        myRow = "1"
    
    
        For i = myRow To LastRow
            'xlsht2
            c1 = xlsht2.Cells(i, 1).Value 'MFG
            c2 = xlsht2.Cells(i, 2).Value ' SL
            c3 = xlsht2.Cells(i, 3).Value 'DELTO
            c4 = xlsht2.Cells(i, 4).Value 'END USER
            c5 = xlsht2.Cells(i, 6).Value 'ITEM TYPE
        
            
            'combined date to go to different cells on xksht1,also search town and postcode
            a1 = c3
            a2 = c1 & "-" & c4
            a3 = c2
            a4 = c5
            
            DelTown = DLookup("Town", "tblDealers", "[Name] Like ""*" & c3 & "*""")
            DelPC = DLookup("Postcode", "tblDealers", "[Name] Like ""*" & c3 & "*""")
        
            xlsht.Cells(i, 2).Value = a1
            xlsht.Cells(i, 3).Value = DelTown
            xlsht.Cells(i, 4).Value = DelPC
            xlsht.Cells(i, 5).Value = a2
            xlsht.Cells(i, 6).Value = a3
            xlsht.Cells(i, 9).Value = a4
             
        Next i
        
        xl.Quit
        Set xl = Nothing
        Set xlWrkBk = Nothing
        Set xlsht = Nothing

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No idea at present? What is the error?, that always helps.
    However I would ask why are you using Like?
    I also tend to use single quotes as my string delimiters. I presume you names will contain a single quote some times?

    Again issue the command in the Immediate window.

    I can't help feeling a CopyFromRecordset would be better?
    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

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi WGM, the reason i am using Like quotes is because the information provided an be slightly different on our system for the same contact, maybe only brackets or Ltd or something along those lines.

    I will run the issue and have a look

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by DMT Dave View Post
    Hi WGM, the reason i am using Like quotes is because the information provided an be slightly different on our system for the same contact, maybe only brackets or Ltd or something along those lines.

    I will run the issue and have a look
    So how can you be sure you get the correct record? DLookup returns the first record it finds that matches your criteria?
    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

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try this syntax
    "[Name] Like '*" & c3 & "*'"

    Name is a reserved word in Access and should not be used for object names if you can help it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Ahh ok, thank you micron, i have nearly achieved the main goal, just having an issue combining 2 cells into on from xlsht2 to xlsht1 (different column), i will add your suggestion

    There is only 1 contact in the dealer table with that name, although some maybe relatively close but i can change minimal amount of records from db combo box but majority will go in correct (fingers corossed)

  15. #15
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi, issue1: No i'm having not a lot of joy!!! The DelTown and DelPC is coming up as "" when you hover over the yellow debug line but hover over c3 then the name does display so its finding the info but not looking up, there will only be 1 contact in dealer table

    issue 2: I m trying to get xlsht1 column (e) to be c1 & "-" c4 taken from 2 columns on xlsht2

    I am more keen on achieving the combined fields (issue2)

    Ironically the Dlookup (issue1) works on myCust and cAdd1, cAdd2 etc...

    Code:
    Dim stAppName As String, myURL As String, OpenChrome As String, myApp As String, stPathName As StringDim myCust As String, cAdd1 As String, cAdd2 As String, cTown As String, cPC As String, myDealer As String
    Dim c1 As String, c2 As String, c3 As String, c4 As String, c5 As String, c8 As String, myPC As String
    Dim xlRow As Long, LastRow As Long, ColCount As Long, LastRows2 As Long
    Dim myRow As Integer, i As Integer, c6 As Integer, c7 As Integer, DelQty As Integer, myRows2 As Integer, i2 As Integer
    Dim FindRow As Range
    Dim xl As Excel.Application
    Dim xlsht As Excel.Worksheet, xlsht2 As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook
    Dim rsDel As DAO.Recordset
    Dim a1 As String, a2 As String, a3 As String, a4 As String, a5 As String
    Dim DelTown As String, DelPC As String
    
    
    srcPath = "T:\DMT Ltd\XL Files\"
    srcFile = "New Items.xlsx"
    
    
        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 & "*""")
    
    
        Set xl = CreateObject("Excel.Application")
        Set xlWrkBk = GetObject(srcPath & srcFile)
        Set xlsht = xlWrkBk.Worksheets(1)
        Set xlsht2 = xlWrkBk.Worksheets(2)
        xl.Visible = False
    
    
        LastRow = xlsht2.UsedRange.Rows.Count
        myRow = "1"
    
    
        For i = myRow To LastRow
            'xlsht2
            c1 = xlsht2.Cells(i, 1).Value 'MFG
            c2 = xlsht2.Cells(i, 2).Value ' SL
            c3 = xlsht2.Cells(i, 3).Value 'DELTO
            c4 = xlsht2.Cells(i, 5).Value 'END USER
            c5 = xlsht2.Cells(i, 6).Value 'ITEM TYPE
        
            
            'combined date to go to different cells on xksht1,also search town and postcode
            a1 = c3
            a2 = c1 & "-" & c4
            a3 = c2
            a4 = c5
            
            DelTown = DLookup("Town", "tblDealers", "[Name] Like '*" & c3 & "*'")
            DelPC = DLookup("Postcode", "tblDealers", "[Name] Like '*" & c3 & "*'")
            myRows2 = "6"
            For i2 = myRows2 To LastRow
            xlsht.Cells(i2, 2).Value = a1
            'xlsht.Cells(i, 3).Value = DelTown
            'xlsht.Cells(i, 4).Value = DelPC
            xlsht.Cells(i2, 5).Value = a2
            xlsht.Cells(i2, 6).Value = a3
            xlsht.Cells(i2, 9).Value = a4
            Next i2
            
        Next i
        
        xl.Quit
        Set xl = Nothing
        Set xlWrkBk = Nothing
        Set xlsht = Nothing

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Excel data cell by cell into an Access table.
    By russmann2000 in forum Import/Export Data
    Replies: 3
    Last Post: 03-21-2018, 07:18 PM
  2. Data from Excel has Alt enter in a cell
    By newbee in forum Reports
    Replies: 1
    Last Post: 03-14-2013, 12:09 PM
  3. Changing the colour of a cell?
    By WayneSteenkamp in forum Access
    Replies: 3
    Last Post: 03-08-2012, 10:12 AM
  4. Importing ever-changing Excel data
    By jtf1972 in forum Database Design
    Replies: 7
    Last Post: 08-05-2011, 11:24 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

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