title: How to load data from Excel sheet into the two-dimensional array using record set from ADODB connection with EXCEL file?
body: <p>My goal is to load data from a record set (ADODB connection with EXCEL file) into the two-dimensional array I need for my Word VBA macro.</p>
  <p>I want to stress that the code is written in Word VBA Editor and it's lunched from Word .docm file. The data I'm talking about is imported into this macro via ADODB connection with an EXCEL file, that's used as a clients database.</p>
  <p>The confusion I feel is because I don't understand if the record set, that was created with ADODB connection with EXCEL file, is one or two-dimensional. I ask this because after I have this record set <strong>I need to load it into two-dimensional array, and I don't really know how to do this.</strong></p>
  <p>The data I grab from Excel file via ADODB connection with source EXCEL file looks like this:</p>
  <p><a href="https://i.stack.imgur.com/rERQx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rERQx.png" alt="how far to the right the columns are imported into the record set??" /></a></p>
  <p>So if the data in the record set are one dimensional, how would I fit it into the two dimensional array, to preserve the way they are arranged (graphically) in the spreadsheet???</p>
  <p>Currently, I have all the time the same source row of data imported into every other row in the array. Therefore, I have first row of my source data in every row of the array and It's not what I need.</p>
  <p><a href="https://i.stack.imgur.com/suT4F.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/suT4F.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/egpY9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/egpY9.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/RxZ7u.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RxZ7u.png" alt="enter image description here" /></a></p>
  <p>The code looks like this:</p>
  <pre><code>Sub UseADOSelect_02()
  
  Dim connection As New ADODB.connection
  Dim recSet As New ADODB.Recordset    'All the results of the query are placed in a record set;
  
  Dim exclApp As Excel.Application     'This code is written and lunched from Word VBA Editor
  Dim exclWorkbk As Excel.Workbook
  Dim mySheet As Excel.Worksheet
  Dim wordDoc As Word.Document
  Dim cntCntrl As ContentControl
  Dim strPESELfromWord As String
  Dim strQuery1 As String
  Dim strQuery2 As String
  Dim intWiersz As Integer
  Dim intRemainder As Integer
  Dim intRow As Integer
  Dim arraySize As Long       'previously was integer =  -32,768 to 32,767
  
  Dim strSexDigit, y As String
  Dim strArray() As String    'Dim strArray() As Long    'Dim strArray() As String    'We don't specify the size of dynamic array when we declare it;
  Dim i, j, k As Long         '&quot;k&quot; is the recStet number
  
  
  'Set exclApp = GetObject(, &quot;Excel.Application&quot;)   'When no Excel Workbook is open an error 429 will be returned.
  'Debug.Print exclApp
  
  Set wordDoc = Word.ActiveDocument
  Debug.Print wordDoc
  
  'Set mySheet = exclApp.ActiveWorkbook.ActiveSheet
  'Debug.Print mySheet.Name
  
  'Set mySheet = exclApp.ActiveWorkbook.Sheets(&quot;sample&quot;)
  'Debug.Print mySheet.Name
  
  strPESELfromWord = Trim(Selection.Text)
  Debug.Print strPESELfromWord
  Word.Application.Visible = True
  
  strSexDigit = Mid(strPESELfromWord, 10, 1)      'Extract 10th digit from PESEL number
  Debug.Print strSexDigit
  intRemainder = strSexDigit Mod 2
  Debug.Print intRemainder
  
  connection.Open &quot;Provider = Microsoft.ACE.OLEDB.12.0;Data Source = X:\Roesler\Excel\FW 1\custdb.xlsm;&quot; &amp; _
                  &quot;Extended Properties=&quot;&quot;Excel 12.0 Macro;HDR=YES;IMEX=1;&quot;&quot;;&quot;                 'now it works
                                  
  
  
  'strQuery1 = &quot;SELECT * From [Simple$]&quot;   '[Simple$] is the table name; in this case it's the sheet name;
  strQuery1 = &quot;SELECT * From [data$]&quot;     '[data$]   is the table name; in this case it's the sheet name;
  strQuery2 = &quot;SELECT * From [data$] WHERE pesel &lt;&gt; ''&quot;      'col B = pesel; col C = data_urzodzenia; col D = imie_nazwisko
  'recSet.Open strQuery1, connection
  recSet.Open strQuery2, connection, adOpenStatic        ' Or specify adOpenStatic in the Open method directly.
  Debug.Print &quot; RecordCount = &quot; &amp; recSet.RecordCount             'https://www.geeksengine.com/article/recordcount-ado-recordset-vba.html
  Debug.Print &quot;EOF value = &quot; &amp; recSet.EOF
                              'Returns a value that indicates whether the current record position is after the last record in a Recordset object.
                              'At the moment the value is &quot;False&quot;.
  Debug.Print &quot;BOF value = &quot; &amp; recSet.BOF
                              'The BOF property returns a Boolean value that indicates if the current position in a Recordset object is just before the first record.
                              'If True, you are at BOF. If False, you are at or beyond the first record, but still inside the Recordset.
                              
  If recSet.BOF = True Then
     recSet.MoveFirst
  End If
                              
  'recSet.Open strQuery, connection
  'recSet.CursorType = adOpenStatic
  Debug.Print &quot; RecordCount = &quot; &amp; recSet.RecordCount             'https://www.geeksengine.com/article/recordcount-ado-recordset-vba.html
  
  arraySize = recSet.RecordCount   'I wanted to assign total number of records to arraySize variable to use it later in ReDim statement.
  Debug.Print &quot;arraySize is = &quot; &amp; arraySize
  
  k = 0
  ReDim strArray(0 To arraySize - 1, 0 To 71 - 1)
  For i = 0 To arraySize          '1st question - Does &quot;i&quot; denote the first &quot;recSet&quot; number??           '&quot;i&quot; is now 181
    For j = LBound(strArray, 2) To UBound(strArray, 2)    '2nd question - Does &quot;i&quot; denote &quot;strArray's&quot; row??
       'k = 0                                              '3rd question - Does &quot;j&quot; from line above denote &quot;strArray's&quot; column??
       For k = 0 To 71 - 1
            strArray(i, j) = recSet(k + 1) &amp; &quot;&quot;
           'strArray(i) = recSet(i) &amp; &quot;&quot;      'https://stackoverflow.com/questions/63814028/encountering-null-value-while-loading-record-set-into-the-array-in-word-vba-thr
           'i = i + 1
            j = j + 1
       Next k
    Next j
  Next i
  
  
  intRow = 2
  
  
  
  '  For Each cntCntrl In ActiveDocument.ContentControls
  '    If cntCntrl.Title = &quot;02KlientPelneImie&quot; Then cntCntrl.Range.Text = objExcel.Sheets(&quot;data&quot;).Cells(intWiersz, 4)
  '    If cntCntrl.Title = &quot;02KlientNrDowodu&quot; Then cntCntrl.Range.Text = objExcel.Sheets(&quot;data&quot;).Cells(intWiersz, 9)
  '  Next
  
  connection.Close
  End Sub
  </code></pre>
  <p>I expect mistake is somewhere here:</p>
  <pre><code>arraySize = recSet.RecordCount   'I wanted to assign total number of records to arraySize variable to use it later in ReDim statement.
  Debug.Print &quot;arraySize is = &quot; &amp; arraySize
  
  k = 0
  ReDim strArray(0 To arraySize - 1, 0 To 71 - 1)
  For i = 0 To arraySize          '1st question - Does &quot;i&quot; denote the first &quot;recSet&quot; number??           '&quot;i&quot; is now 181
    For j = LBound(strArray, 2) To UBound(strArray, 2)    '2nd question - Does &quot;i&quot; denote &quot;strArray's&quot; row??
       'k = 0                                              '3rd question - Does &quot;j&quot; from line above denote &quot;strArray's&quot; column??
       For k = 0 To 71 - 1
            strArray(i, j) = recSet(k + 1) &amp; &quot;&quot;
           'strArray(i) = recSet(i) &amp; &quot;&quot;      'https://stackoverflow.com/questions/63814028/encountering-null-value-while-loading-record-set-into-the-array-in-word-vba-thr
           'i = i + 1
            j = j + 1
       Next k
    Next j
  Next i
  </code></pre>
  <p>Immediate window:</p>
  <p><a href="https://i.stack.imgur.com/l5Cd1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l5Cd1.png" alt="enter image description here" /></a></p>