Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Access VBA - How to split data in one field and insert into other fields in an existing table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access VBA - How to split data in one field and insert into other fields in an existing table

    Hello all,

    I have a piece of code regarding the "Split" function in Access VBA. It works great.

    That is, basically, my sub takes a block of data, as a string, received by the Access database as a copy from the clipboard.

    And then the current code here, converts the string to an array, and then places each line of data into a field called INPUT_FIELD in an Access table called VALUES. I get the right number of rows in the table after running the code

    However, that data consists of 3 different columns of data from the original source file.

    So, I would like to Split the data currently in INPUT_FIELD as follows, and separated by "pipes":

    9834 | 7729 | 1536

    Into inputs into INPUT_FIELD2, INPUT_FIELD3, and INPUT_FIELD4 respectively, which are existing fields within the same table VALUES, as follows:

    9834 to INPUT_FIELD2 (different column in same row of data)
    7729 to INPUT_FIELD3 " " "
    1526 to INPUT_FIELD4 " " "


    I sense I need the Split function, and/or maybe combined with Insert Into...Values code, but have failed on many attempts, while searching a lot too. Thank you in advance for any feedback. I am at the point of waving my white flag of surrender to VBA.


    Working code:

    Sub CopyFromClipToTable

    Dim strString As String
    Dim arrString() As String
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim Db As DAO.Database


    Set Db = CurrentDb
    Set rs = Db.OpenRecordset("VALUES")

    ' Retrieve clipboard contents into data object
    DataObj.GetFromClipboard

    ' Clipboard to string variable
    strString = DataObj.GetText

    ' Convert string variable to array
    arrString = Split(strString, vbNewLine)
    ' Post Array to Table using DAO recordset

    For i = 0 To UBound(arrString) - 1
    'Update Table with data from clipboard
    rs.AddNew
    rs!INPUT_FIELD = arrString(i)
    rs.Update
    Next I



    Set rs = Nothing

    End Sub

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    So, can you show us an example of your current data, and exactly what your current VBA code does to it, after you run it?
    I would like to take your sample file and run your code against it and see what it does.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Quote Originally Posted by Joe4 View Post
    So, can you show us an example of your current data, and exactly what your current VBA code does to it, after you run it?
    I would like to take your sample file and run your code against it and see what it does.
    Thanks Joe for the quick reply. Below is sample input, in tabular form, which is fed into the Access database by the copy from clipboard code (not exactly like my very simplified example, but again, these are text-formatted fields separated by pipes, by the original SAP file:

    Category1 Cat2 Cat3
    3300065815 FO 30110764
    3300066027 FO 30110764
    3300066165 FO 30110770
    3300072438 FO 31780962
    3300072858 FO 31780965
    3300073937 FO 31780972
    5000477829 NB 30110776
    5000477830 NB 30110776
    5000491340 NB 30110827
    5000491806 NB 30110830
    5000491806 NB 30110830
    5000491806 NB 30110830
    5000491806 NB 30110830
    5000491806 NB 30110830
    3300491806 NB 30110830
    3300491806 NB 30110830
    4200491806 NB 30110830
    4200492080 NB 30110822
    4200492080 NB 30110822
    4200498873 NB 30110828
    4200498873 NB 30110828
    4200498873 NB 30110828
    4700498873 NB 30110828
    4800498873 NB 30110828
    4900498873 NB 30110828

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    I would like the exact structure of the original SAP file that you are running this against, so I can try to emulate your exact scenario and test it all out.
    To me, your original description sounds like you have one long string with fields separated by Pipe symbols, and you are using the Split command to break it into multiple rows (records), but then are left with three fields in one, instead of being split into three.
    Do I have that right?

    What really would be must helpful is to see the following:
    - sample of original file, EXACTLY how it appears (without any modifications)
    - results you are getting with your code
    - what your desired results should look like
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Something like the following may work (untested)

    Code:
     Dim strString As String
     Dim arrString() As String
     Dim splitValues() As String
     Dim rs As DAO.Recordset
     Dim i As Integer
     Dim Db As DAO.Database
    
     Set Db = CurrentDb
     Set rs = Db.OpenRecordset("VALUES")
     ' Retrieve clipboard contents into data object
     DataObj.GetFromClipboard
     ' Clipboard to string variable
     strString = DataObj.GetText
     ' Convert string variable to array
     arrString = Split(strString, vbNewLine)
     ' Post Array to Table using DAO recordset
     For i = 0 To UBound(arrString) - 1
     splitValues = Split(arrString, "|")
     'Update Table with data from clipboard
     rs.AddNew
     rs!INPUT_FIELD = arrString(i)
     rs!INPUT_FIELD2 = splitValues(0)
     rs!INPUT_FIELD3 = splitValues(1)
     rs!INPUT_FIELD4 = splitValues(2)
     rs.Update
     Next i
     Set rs = Nothing

  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Thank you Joe4 and stumac for your replies.

    I tried the above code from stumac, and the code stops at the line below, and the error message is "Compile error: Type mismatch"

    splitValues = Split(arrString, "|")

    The code solution looks good to me. My table fields are all Text format, and I believe I am importing all text data. Any clues as to the type mismatch error? Thanks again in advance. Best, Peter_W

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Oops my bad, try changing to:

    Code:
     splitValues = Split(arrString(i), "|")

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Quote Originally Posted by stumac View Post
    Oops my bad, try changing to:

    Code:
     splitValues = Split(arrString(i), "|")

    Thanks, I added the (I), but now the code breaks here, at this line:

    DataObj.GetFromClipboard

    I was testing with the same text data yesterday, and this line was not a problem. Any thoughts, thanks, Pete.

  9. #9
    Board Regular
    Join Date
    Jul 2010
    Posts
    445
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Hmmm, I was surprised it was working when you posted it. When using this method in the past I've had to set a reference to the msforms library and then declare the data object, something like:
    Code:
    Dim DataObj As MSForms.DataObject
     Set DataObj = New MsForms.DataObject
    Last edited by stumac; Apr 23rd, 2018 at 05:32 PM.

  10. #10
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access VBA - How to split data in one field and insert into other fields in an existing table

    Quote Originally Posted by stumac View Post
    Hmmm, I was surprised it was working when you posted it. When using this method in the past I've had to set a reference to the msforms library and then declare the data object, something like:
    Code:
    Dim DataObj As MSForms.DataObject
     Set DataObj = New MsForms.DataObject
    Thank you stumac for the de-bug. I will try this fix soon and get back. I have this distracting thing called my day job that I have to attend to now. All the best, Peter_W

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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