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
 

Peter_W

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

Stumac, I got your code to work, and it works fine! Thank you! I believe a couple of times I was overwhelming the clipboard with too much data, and that may have prevented instances from running, but again, its good, and I'm all set now. Thanks again!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,816
Members
417,168
Latest member
StumpoC

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