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

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

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,785
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top