Import Table Values into the Multidimensional Array

[dj44]

Folks,

hope all you great people are doing good today:)

I am trying to make a multidimensional array from my table.

I am trying to import the contents of the table into the array - I am not sure if I am explaining it correctly.


15487

How do I point my array to a table.


You see I have lots of tables and then I have some vba macros that use arrays - so i need to learn the skill of pointing an array to a table.

Now I have been trying to get my array to work for a while, but its gone bust.:doh:



Sub MultiDimensionalArrayTable()

Dim oRng As Word.Range
Dim lngIndex As Long

Dim FindTVD() As String
Dim AppendFSH() As String
Tablelocation As string


Tablelocation = "C\Users\DJ-PC\Desktop\TVDTable.doc"

Set Document = ActiveDocument
Set Tablelocation = Documents.Open(FileName:=strpath, Visible:=False)


'Column 1 - Find TVD

'FindTVD = Split(" TVD 1028,TVD 9084,TVD 9084")

FindTVD = ActiveDocument.Tables(1).Cell(i, 1).Range.Text


'Column 2 - Append the FSH

AppendFSH = ActiveDocument.Tables(1).Cell(i, 2).Range.Text

'AppendFSH = Split("FSH073,FSH045,FSH732,FS345")




For lngIndex = 0 To UBound(FindTVD)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = FindTVD(lngIndex)
.MatchWholeWord = True

' do the important work

.Replacement.Text = FindTVD(lngIndex) & AppendFSH(lngIndex)
.Execute Replace:=wdReplaceAll

End With
Next
lbl_Exit:
Exit Sub

End Sub





I saw this and got the idea that I can point my array to the table.

But this code is ancient, I couldn't get this to work with my table above





Sub arraytest()

Dim pTable As Word.Table
Dim pArray() As Variant
Dim pRow As Long
Dim pColumn As Long

Set pTable = Selection.Tables(1)

ReDim pArray(1 To pTable.Rows.Count, 1 To pTable.Columns.Count)
For pRow = 1 To pTable.Rows.Count
For pColumn = 1 To pTable.Columns.Count
pArray(pRow, pColumn) = pTable.Cell(pRow, pColumn)
Next
Next


Debug.Print pArray(2, 2) 'prints content of table


'Debug.Print pArray(1, 2) 'prints content of table

End Sub



I got stuck here trying to combine these 2.

https://groups.google.com/forum/m/#!topic/microsoft.public.word.vba.general/oUJNJC8YUVU

Folks do let me know how i can point this mulitdimendional array to my external table.

I thank you for your expert advice on my naivete multidimensional array offering

& for your kind valuable time :grinhalo:

Enjoy your weekend

DJ

[gmaxey]

Sub MultiDimensionalArrayTable()
Dim oRng As Word.Range
Dim lngRow As Long, lngCol As Long, lngIndex As Long
Dim arrData() As String
Dim oDoc As Document, oDocSource As Document
Dim oTbl As Table
Set oDoc = ActiveDocument
Set oDocSource = Documents.Open(FileName:="D:\Table.docm", Visible:=False)
'Here is how you can load the contents of a table (excluding a header row) into an array.
Set oTbl = oDocSource.Tables(1)
ReDim arrData(oTbl.Rows.Count - 2, oTbl.Columns.Count - 1)
For lngRow = 2 To oTbl.Rows.Count
For lngCol = 1 To oTbl.Columns.Count
arrData(lngRow - 2, lngCol - 1) = Left(oTbl.Cell(lngRow, lngCol).Range.Text, Len(oTbl.Cell(lngRow, lngCol).Range.Text) - 2)
Next lngCol
Next lngRow
oDocSource.Close wdDoNotSaveChanges
For lngIndex = 0 To UBound(arrData)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = arrData(lngIndex, 0)
.MatchWholeWord = True
.Replacement.Text = arrData(lngIndex, 0) & arrData(lngIndex, 1)
.Execute Replace:=wdReplaceAll
End With
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

[dj44]

Greg,

I can't believe it! :thumb

What a smashing man you are - for doing this,

I can't say how chuffed and chuffed I am - I can use all those old macros languishing in my hardrive now thanks to you!

You can see how my code went bust - not helped by my googling endeavors who present 10 year old vba now that that was a joke and a half.

All this time I've been up and down with this array thing.

Some one told me to put the array into excel matrix with a dynamic range and concatenate or something but that was too complicated for me,

These tables have to be normally transposed, and that vba editior is very difficult to deal with long array strings.

That underscore _ stopped working with my long array - so my macro stopped working that was another headache.:steamed:

Anyway Greg , you have saved me stress and made my weekend a chill one now



also thanks to your help - i found lots of old files - i thought i had lost - they were there all along, that recursion worked

Thanks Greg - impeccable coding skills and to do it in a second - fastest coder around :grinhalo:, i only went out to get the milk for coffee

you enjoy your great weekend now good man

I owe you big buddy cheers and gratitude:beerchug:

DJ

[gmaxey]

You're welcome. Glad to help.

[dj44]

Hi Greg,

good day to you and forum today.:)

just had a quick follow up question.

do you remember i was trying to color my account numbers with the array, and that was difficult to do.


15541

I thought the multidimensional array can pick up the RGB values now that you did the advanced work on the array.


http://www.vbaexpress.com/forum/showthread.php?55065-Convert-RGB-Color-in-an-Array&highlight=

i did this from

Dim arrData() As String

to

Dim arrData() As long

Then I made a variable

ColorTheAccountNo = arrData(lngIndex, 1).Range.Font.Color ' This is column 2 - holds the RGB array strings

I did




With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = arrData(lngIndex, 0)
.MatchWholeWord = True
.MatchWildcards = True

.Replacement.Font.Color = ColorTheAccountNo


- but it still did not pick up the RGB string.:doh:


and then stuck again:sad2:

I don't know what it could be

thank you for your advice and help

DJ

[gmaxey]

dj44,

No I don't remember. Reviewing this thread I don't see anything about coloring account numbers. Regardles, whatever made you think that some long variable which you have apparently not declared has a .range method?

ColorTheAccountNo = arrData(lngIndex, 1).Range.Font.Color ' This is column 2 - holds the RGB array strings

It seems that you already know that the RGB function depends on an array. Accordingly, you have to define that array in your table e.g.,:

104,34,139 not RGB(104,34,139)

then something like this:



Dim arrColors() as String
For lngIndex = 0 To UBound(arrData)
arrColors = Split(arrData(lngIndex, 1), ",")
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = arrData(lngIndex, 0)
.MatchWholeWord = True
.Replacement.Font.Color = RGB(arrColors(0), arrColors(1), arrColors(2))
.Execute Replace:=wdReplaceAll
End With
Next lngIndex

[dj44]

Hello Greg,

Thank you for assisting with the mulitdimensional array again.

Now these 2 lines below did the fine job:checkmark



arrColors = Split(arrData(lngIndex, 1), ",")

.Replacement.Font.Color = RGB(arrColors(0), arrColors(1), arrColors(2))



Now i did declare my variable , but that did not work so i changed it from string, to long to range.

Growing pains of the would be novice coder
your code writing technique is exemplary - it makes good sense to me,

I could not find anything as good on this multidimensiaonal array to help me :read: - so i thought i better ask, otherwise i will be as dim as the broken array, im trying to fix for a long time:grinhalo:

not any more

I will sure read up more on the multidimensional arrays


Thank you for helping again

You all have a great evening now

cheers :beerchug:

DJ