get data from another Excel file(not Record) 追蹤

0
Avatar
fernand obama

hello, in my printing template there are abbreviations that indicate the properties of the product for example "A11, A08, A07". A product may contain one or more of these abbreviations. And in another excel file there is a table showing what these abbreviations mean.

I want to print the meanings of the abbreviations in the product label I created.

I can not add that second database as inner joint, because these are not record.

therefore, I tried to read that data with "Event control Scripts"., but I'm not a experienced user so I cant get I want.

I added some screenshot and code about my project. Can anyone help me.

Thanks in advance.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible= false

Set objWorkbook = objExcel.Workbooks.Open _
("W:\03_Product_data\Rating_plate_data\Maschinenoption.xlsx")

Dim r As Range
Dim abb(3) As String
Dim s As Variant

if len(Format.NamedSubStrings("OPTIONEN").Value) < 4 then
abb(0)= left(Format.NamedSubStrings("OPTIONEN").Value, 3)
abb(1).delete
abb(2).delete
abb(3).delete
elseif len(Format.NamedSubStrings("OPTIONEN").Value) < 4 and len(Format.NamedSubStrings("OPTIONEN").Value) < 9 then
abb(0)= left(Format.NamedSubStrings("OPTIONEN").Value, 3)
abb(1)= mid(Format.NamedSubStrings("OPTIONEN").Value, 6, 8)
abb(2).delete
abb(3).delete
elseif len(Format.NamedSubStrings("OPTIONEN").Value) < 11 and len(Format.NamedSubStrings("OPTIONEN").Value) < 14 then
abb(0)= left(Format.NamedSubStrings("OPTIONEN").Value, 3)
abb(1)= mid(Format.NamedSubStrings("OPTIONEN").Value, 6, 8)
abb(2)= mid(Format.NamedSubStrings("OPTIONEN").Value, 11, 13)
abb(3).delete
elseif len(Format.NamedSubStrings("OPTIONEN").Value) < 16 then
abb(0)= left(Format.NamedSubStrings("OPTIONEN").Value, 3)
abb(1)= mid(Format.NamedSubStrings("OPTIONEN").Value, 6, 8)
abb(2)= mid(Format.NamedSubStrings("OPTIONEN").Value, 11, 13)
abb(3)= mid(Format.NamedSubStrings("OPTIONEN").Value, 16, 18)
end if

For Each s In abb
For Each r In objExcel.Cells.CurrentRegion.Find(s)
abb(s)= abb(s) + r.Offset(0, 4).Value
Next r
Next s

Value = abb(s)

登入寫評論。