Store Table Column in Array

[MM91]

Hi I am trying to store a table column in an array for use in other module. I have the code that seems like it runs and works fine. But the array returns nothing in the messagebox. I get no errors but am not sure what I am doing wrong. Please help! thanks!

Dim StandardPartCodesArray() As String

Public Sub LoadApp()

'Set Database Workbook File Location
Dim strFilename As String: strFilename = "C:\Users\mattr\Desktop\Standard Parts Macro Test.xlsm"

'Open New Excel Instance
Dim XLapp As New Excel.Application
XLapp.Visible = False

'Open WorkBook
Dim SPwb As Excel.Workbook
Set SPwb = XLapp.Workbooks.Open(Filename:=strFilename, UpdateLinks:=3)
SPwb.Activate



'Activate Worksheet
Dim SPws As Excel.Worksheet
Set SPws = SPwb.Worksheets("Standard Parts")
SPws.Activate




'Set Table Object
Dim SPTbl As ListObject
Set SPTbl = SPws.ListObjects("StandardPartsTable")


'Store Data
Dim LastRowSPTable As Long
Dim i As Long

LastRowSPTable = SPTbl.DataBodyRange.Rows.Count

ReDim StandardPartCodesArray(1 To LastRowSPTable)

For i = 1 To TotalRows
StandardPartCodesArray(i) = SPTbl.DataBodyRange(i, 1).Value
Next

Dim messagetest As String
messagetest = StandardPartCodesArray(1)
'Msgbox Join(StandardPartCodesArray, vbCrLf)
Msgbox messagetest

SPwb.Close SaveChanges:=False
XLapp.Quit
Set XLapp = Nothing







End Sub

 

[JoeMo]

I don't see the variable TotalRows declared or given a value anywhere in your code, so at best it's value is zero. Try this instead:

LastRowSPTable = SPTbl.DataBodyRange.Rows.Count
  
   ReDim StandardPartCodesArray(1 To LastRowSPTable)

       For i = 1 To LastRowSPTable
           StandardPartCodesArray(i) = SPTbl.DataBodyRange(i, 1).Value
       Next

Also, you didn't say which column of the table you wanted to place in the array. If its the description column then modify the line immediately above the Next s by changing this:
StandardPartCodesArray(i) = SPTbl.DataBodyRange(i, 1).Value
to this:
StandardPartCodesArray(i) = SPTbl.DataBodyRange(i, 2).Value

 

[MM91]

Wow thank you so much I thought I had gone over every variable name a hundred times thank you!!

 

[JoeMo]

Wow thank you so much I thought I had gone over every variable name a hundred times thank you!!

You are welcome - thanks for the reply.