Need help to open certain file

elmnas

Board Regular
Joined
Feb 20, 2015
Messages
206
Hi guys I got this file:
UCHP_Translation2.xls (the file can have any name)

The file contains loads sheets and different columns
sv,es,ru etc.


now I need a a script, that check in the file if the columns contain text then check in a folder after the filename (UCHP_Translation2.xls) the file I have open, + sheetname + the column cell data then open the file.

see picture below:









I have made this code but it doesn't work can you please help me out?


Code:
Sub OpenLangFile()
Dim myLangCol As String
Dim myPath As String
Dim myFile As String
Dim mySheetname As String
Dim myLangFile As String


myPath = Application.ActiveWorkbook.Path


For Each sht In ActiveWorkbook.Worksheets
Set rng = sht.UsedRange
Set MyRange = rng




For Each MyCol In MyRange.Columns
For Each myCell In MyCol.Cells






'MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)


If myCell.Interior.Color = vbBlue Then
If myCell.Font.Color = vbRed Then




myLangCol = MyCol.Cells(1, 1).Text
myPath = Application.ActiveWorkbook.Path
myFile = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
mySheetname = ActiveSheet.Name
myLangFile = myPath & "\" & myFile & "_" & mySheetname & "_" & myLangCol & ".xls"


'Set noLangFilen = Workbooks.Open(myLangFile)


MsgBox myLangFile


'myLangColTxtFilter = MyCol.Cells(1, 1).Text


'MsgBox MyFile






End If


'MsgBox "" & Mycell.Column
'Cells(Mycell.Row, 2).Copy
'Mycell.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
'SkipBlanks:=False, Transpose:=False
    
            End If
        Next
    Next
Next








End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,100,195
Messages
5,473,080
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top