Split data help

[brss]

Hi, Usually i used to transfer data from Excell multible column to access db. Now i have data in one column seperated by , as mention below. Please what should i change in code below to transfer data from excel to access db? or please advice me how to do it? i appreciate your help in advance.
Excel sheet data:
In column A,

B,Brss,4645656,basmoh,6565,failed

Code

'
'
If rs1.Fields(1) <> vbNullString Then
        strSQLSELECT = "SELECT COUNT(*) AS recCount FROM " & strTable1 & " WHERE "
        strSQLSELECT = strSQLSELECT & "colname = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colnumber = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colICCID = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colstatus ='" & Trim$(rs1.Fields(0)) & "',") & "'"
        rs2.Open strSQLSELECT, cnn, adOpenStatic, adLockOptimistic
        If rs2![recCount] = 0 Then
            strSQL = " (colname,colnumber,colICCID,colstatus) VALUES("
            strSQL = strSQL & "'" & rs1.Fields(1) & "',"
            strSQL = strSQL & "'" & rs1.Fields(2) & "',"
            strSQL = strSQL & "'" & rs1.Fields(3) & "',"
            strSQL = strSQL & "'" & Trim$(rs1.Fields(4)) & "')"
            cnn.Execute strSQl1 & strTable1 & strSQL
            cnn.Execute strSQl1 & strTable2 & strSQL
        End If
'
'

[SamOscarBrown]

Here is AN approach...I put your excel field into a textbox for clarification. Instead of putting the individual values as I did into an array of textboxes, insert them into your database instead.

Dim mySplitArray() As String
Dim myText As String
myText = Text1.Text & ","   'use your excel cell value instead of my text1.text (add a comma at the end)
mySplitArray = Split(myText, ",") 'splits your value 
Dim i As Integer
For i = 0 To UBound(mySplitArray()) - 1
    Text2(i).Text = mySplitArray(i)  'instead of putting the array into textboxes, do your insert cmd here
Next i

[gep13]

Hello,

This feels more like an Office question, rather than specifically a VB6 one.

As such, I am going to move it to the Office Development Forum.

Let me know if you have any questions.

Gary

[brss]

Dear Sam, Thank you for your reply, i really enjoy apply your code i did make array and work such fine. I did not figure out how to add it to my project. I attach the source code with excel data and Access DB, I would be appreciate if you can look at it and help me. ofcource if you have time for me bro. Also, Thank you gep. Thank you again

[brss]

Looking forward for your advice Sam? Thank you very much.

[brss]

Any advice please? Thank you very much.

[Doogle]

Try this:

Private Sub TransferExcelFile2Database_Click()
'On Error GoTo errortrap
Dim strTable1 As String
Dim strTable2 As String
Dim strSQL As String
Dim strSQl1 As String
Dim strSQLSELECT As String
strTable1 = "Table3"
strTable2 = "Table4"
strSQl1 = "INSERT INTO "
Dim strDealer As String
Dim strDate As String
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim mySplitArray() As String
Dim myText As String
Dim i As Integer
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.path & "\sample.mdb;"
strSQL = "SELECT * FROM [Report_DealerInfo$]"
rs1.Open strSQL, con, adOpenStatic, adLockOptimistic
Do
    If rs1.Fields(0) <> vbNullString Then
        mySplitArray = Split(rs1.Fields(0), ",")
        strSQLSELECT = "SELECT COUNT(*) AS recCount FROM " & strTable1 & " WHERE "
        strSQLSELECT = strSQLSELECT & "AREA = '" & mySplitArray(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEPT_CODE = '" & mySplitArray(1) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEALERNAME = '" & mySplitArray(2) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEALER_MSISDN = '" & mySplitArray(3) & "' AND "
        strSQLSELECT = strSQLSELECT & "SUB_MSISDN = '" & mySplitArray(4) & "' AND "
        strSQLSELECT = strSQLSELECT & "STATUS = '" & mySplitArray(5) & "' AND "
        strSQLSELECT = strSQLSELECT & "ACTIVATION_ON ='" & Trim$(mySplitArray(6)) & "'"
        rs2.Open strSQLSELECT, cnn, adOpenStatic, adLockOptimistic
        If rs2![recCount] = 0 Then
            strSQL = " (AREA,DEPT_CODE,DEALERNAME,DEALER_MSISDN,SUB_MSISDN,STATUS,ACTIVATION_ON) VALUES("
            strSQL = strSQL & "'" & mySplitArray(0) & "',"
            strSQL = strSQL & "'" & mySplitArray(1) & "',"
            strSQL = strSQL & "'" & mySplitArray(2) & "',"
            strSQL = strSQL & "'" & mySplitArray(3) & "',"
            strSQL = strSQL & "'" & mySplitArray(4) & "',"
            strSQL = strSQL & "'" & mySplitArray(5) & "',"
            strSQL = strSQL & "'" & Trim$(mySplitArray(6)) & "')"
            cnn.Execute strSQl1 & strTable1 & strSQL
            cnn.Execute strSQl1 & strTable2 & strSQL
        End If
        rs2.Close
    End If
    rs1.MoveNext
Loop Until rs1.EOF
rs1.Close
Set rs1 = Nothing
con.Close
Set con = Nothing
cnn.Close
Set cnn = Nothing
MsgBox "Data transfered"
   'TransferExcelFile2Database.Enabled = False
'errortrap:    MsgBox Err.Description, vbExclamation, "error"
End Sub