Thank you soo much both! It works like a charm with a revision implemented in code.
(with testing i found little mistake in revision - line num. 9):
varArr = xlFillvarArray(strWorkbook, strSheet)
should be just this, for combined codes to work:
varArr = xlFillArray(strWorkbook, strSheet)
So, for anybody else who might want to use this, there is combined code from vba masters in one piece :
Option Explicit
Sub ColourValues()
'Original by Graham Mayor. Modified by Greg Maxey
Const strWorkbook As String = "C:\Users\skopa\Desktop\Předpisy a normy po oblastech.xlsm" 'Change to your path.
Const strSheet As String = "Sheet1"
Dim varArr() As Variant
Dim lngCols As Long
Dim oRng As Range
On Error Resume Next
varArr = xlFillArray(strWorkbook, strSheet)
For lngCols = 0 To UBound(varArr, 2)
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = varArr(0, lngCols) 'Change 0 to whatever your Excel colummn index is minus 1.
With .Replacement
.Font.ColorIndex = wdBlue
.Text = "^&"
End With
.Execute Replace:=wdReplaceAll
End With
Next lngCols
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub
Private Function xlFillArray(strWorkbook As String, _
strRange As String) As Variant
'Graham Mayor - http://www.gmayor.com - 24/09/2016
Dim RS As Object
Dim CN As Object
Dim iRows As Long
strRange = strRange & "$]" 'Use this to work with a named worksheet
'strRange = strRange & "]" 'Use this to work with a named range
Set CN = CreateObject("ADODB.Connection")
'Set HDR=NO for no header row
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strRange, CN, 2, 1
With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function