Using Excel data in Word VBA macro

emilycwb

New Member
Joined
Feb 1, 2012
Messages
1
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
One way would be to use a Find() routine.

Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,459
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top