Using two do loops to insert data into database properly

Question

I am attempting to insert data from Excel to a SQL datbase by means of VBA. I am using the following structure in Excel:

I am using the following code:

 Private Sub CommandButton1_Click()

 Dim i                 As Integer
 Dim p                 As Integer
 Dim product           As String
 Dim version           As String
 Dim opt               As String
 Dim visible           As String
 Excel.Worksheets("Blad2").Select

i = 3
Do Until IsEmpty(Cells(i, 1))

opt = ActiveSheet.Cells(i, 1)

    p = 3
    Do Until IsEmpty(Cells(1, p))

        product = ActiveSheet.Cells(1, p)
        version = ActiveSheet.Cells(2, p)
        visible = ActiveSheet.Cells(i, p)

        Debug.Print product & version & opt & visible

    p = p + 1
    Loop

i = i + 1
Loop

End Sub

The result of running the script is as follows:

  product#1  version#1   option#1   0
  product#1  version#2   option#1   1
  option#1

While I want it to result in:

  product#1  version#1   option#1   0
  product#1  version#2   option#1   1
  product#1  version#1   option#2   0
  product#1  version#2   option#2   0

Could someone help me out?

The script uses the strings, namely "option#1" and "option#2", to find the corresponding optionvalue id, which is 2 for option#1 and 5 for #option2 — user2237168, Sep 04 '17 at 11:14
I might be missing something but I can't see how your source data gets to that — Tom, Sep 04 '17 at 11:17
The source data gets to that result by selecting the relevant data from the database. So it uses the select query in conn.Execute to retrieve the correct id and feature. Hopefully, I am clear. — user2237168, Sep 04 '17 at 11:20

Vityata · Accepted Answer · 2017-09-04T13:01:59.317

1

This is something that should work for the input of this:

bringing this:

product1 version1 option1 0
product1 version2 option1 1
product1 version1 option2 0
product1 version2 option2 0

Option Explicit

Public Sub TestMe()

    Dim k           As Long
    Dim i           As Long
    Dim p           As Long
    Dim product     As String
    Dim version     As String
    Dim opt         As String
    Dim visible     As String

    With ActiveSheet
        i = 3
        Do Until IsEmpty(.Cells(i, 1))
        p = 3
        k = 0
            Do Until IsEmpty(.Cells(1, p))
                opt = .Cells(i, 1)
                product = .Cells(1, p)
                visible = .Cells(i, p)
                version = .Cells(2, 3 + k)
                k = k + 1

                Debug.Print product & " " & version & " " & opt & " " & visible
                p = p + 1
            Loop
            i = i + 1
        Loop
    End With
End Sub

In general, consider using better names for the variables and using Long instead of Integer.

edited Sep 04 '17 at 13:01

answered Sep 04 '17 at 11:20

Vityata

42,633
8
55
100

Thank you Vityata for your answer. However, it is not exactly what I want. I edited my main question a bit. Also based on the comment of Tom. Hopefully the question makes more sense now and hopefully you are still willing to help me out – user2237168 Sep 04 '17 at 11:36
@user2237168 - if you can make it to a minimal working example (minimal working example stackoverflow), I will try. E.g., try to remove all the useless parts of your question and try to leave only the input or output, as far as it can be replicated easily. E.g., in my answer there is no connection string or complicated SQL queries. – Vityata Sep 04 '17 at 12:12
Dear Vityata. I now edited my main question and only included the necessary code and outputs. Sorry for me not being clear in the first place. I hope my main question now makes more sense. – user2237168 Sep 04 '17 at 12:50
@user2237168 - that is much better, see the edited answer :) – Vityata Sep 04 '17 at 13:04
1

Thanks for your help Vityata, the solution works like a charm! – user2237168 Sep 04 '17 at 13:53

kulapo · Answer 2 · 2017-09-04T13:49:18.453

1

I might be missing something but seems the variable visible is stucked with row 3 .Cells(3, p), that's why it only inserting Option 1 and disregarding the first loop.

try to change it with visible = ActiveSheet.Cells(i, p)

Edit: You said that is it not working, but seems when i tested it, I am getting the right result.

There's a possibility that when SQL string to be executed is the issue here.

edited Sep 04 '17 at 13:49

answered Sep 04 '17 at 12:11

kulapo

397
3
15

Once again, thanks for your help Kulapo. I really appreciate it! I eventually used the solution from Vityata. – user2237168 Sep 04 '17 at 13:53
But you are indeed right. It is quite strange that the sql does not work. I observed that when adding the K (as in the solution form Vityata) the SQL does work and the data is inserted properly ... – user2237168 Sep 04 '17 at 14:32

Using two do loops to insert data into database properly

2 Answers2