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
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
