Using Excel data in Word VBA macro

[emilycwb]

Hi!
I am trying to write a macro which will use data in an excel file to detect the language of a word file. Basically, I have a word file named p18273602_1046_1_.doc, where 1046 is a language code. This changes depending on the language (i.e. p18273602_7_1_.doc).

In an Excel file, I have a language map where each language code is matched with a language. Below is an example of how the excel file is set up with the language in column A and the language code in column B.

AR 1
BR 1046
DE 7
SP 10
CS 4

What I want to do is open a message box with the language based on the language code. So for the file p18273602_1046_1_.doc, I want the macro to detect the 1046 language code and then show a message box that says something to the effect of "The language is: BR"

So far, I have written some code to isolate the language code in the file name:

myDocName = ActiveDocument.Name
posFromL = InStr(myDocName, "_")
posFromR = InStr(posFromL + 1, myDocName, "_")
langCodeDigits = (posFromR - posFromL) - 1
myDocStartName = Left(myDocName, posFromR - 1)
langCode = Right(myDocStartName, langCodeDigits)

How I would call the data in the Excel file, search for the language code in the excel file, and get the corresponding language?

 

[bertie]

One way would be to use a Find() routine.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] myDocName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] posFromL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rngFound [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] myLang [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 
   myDocName = "p18273602_7_1_.doc"
   posFromL = InStr(1, myDocName, "_", vbTextCompare)
 
   [COLOR=green]'strip out the stuff you don't need[/COLOR]
   [COLOR=green]'and be left with the language code[/COLOR]
   myDocName = Val(Mid(myDocName, posFromL + 1, Len(myDocName)))
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR] [COLOR=green]'in case value is not found[/COLOR]
      [COLOR=darkblue]With[/COLOR] Sheets("[COLOR=red]Sheet1[/COLOR]")
         [COLOR=darkblue]Set[/COLOR] rngFound = .[COLOR=red]Columns(2).[/COLOR]Find(What:=[COLOR=red]myDocName[/COLOR], _
                        After:=.[COLOR=red]Cells(1, 2),[/COLOR] _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
 
   [COLOR=green]'output[/COLOR]
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngFound [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      myLang = rngFound.Offset(, -1).Value
      MsgBox "Language is: " & myLang
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]