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

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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.
 

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471

ADVERTISEMENT

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
 

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471

ADVERTISEMENT

Oops my bad, try changing to:

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

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,537
Messages
5,636,892
Members
416,947
Latest member
asher_nk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top