CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

How to get the sheet name using GetOpenFilename in VLOOKUP

I am using this code down below to use a VLOOKUP in another file that you select using the GetOpenFilename. I want shtName to be the name of the sheet in the file that you select, but whenever I step through it, it is always the name of the sheet that I am working in and putting the VLOOKUP in.

I have shtName in my VLOOKUP and it doesn't show anything when I step through it. X shows the filename and path, but shtName right after shows nothing. But my VLOOKUP ends up working anyway and it puts the sheet in the formula.

Why is that? I want to be able to do it myself and so I know I get the sheet name from the file you are selecting.

Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String

' Promt
strPrompt = "Please select the last Kronos Full File before the dates of this HCM Report." & vbCrLf & _
    "This will be used to find the Old Position, Org Unit, and Old Cost Center." & vbCrLf & _
    "For example, if the date of this report is 7-28-17 thru 8-25-17, the closest Kronos Full File you would want to use is 7-27-17."

' Dialog's Title
strTitle = "Last Kronos Full File for Old Positions"

'Display MessageBox
iRet = MsgBox(strPrompt, vbOK, strTitle)

Dim LR As Long
Dim X As String
Dim lNewBracketLocation As Long

X = Application.GetOpenFilename( _
    FileFilter:="Excel Files (*.xls*),*.xls*", _
    Title:="Choose the Kronos Full File.", MultiSelect:=False)

MsgBox "You selected " & X
'Find the last instance in the string of the path separator "\"
lNewBracketLocation = InStrRev(X, Application.PathSeparator)
'Edit the string to suit the VLOOKUP formula - insert "["
X = Left$(X, lNewBracketLocation) & "[" & Right$(X, Len(X) - lNewBracketLocation)

shtName = ActiveWorkbook.Worksheets(1).name

LR = Range("E" & Rows.Count).End(xlUp).Row



Range("T2").Formula = "=VLOOKUP($E2,'" & X & "]shtName'!$B$1:$AP$99999,15,0)"
Stop
Range("T2").AutoFill Destination:=Range("T2:T" & Range("E" & Rows.Count).End(xlUp).Row)
Stop
Range("T2:T" & Range("E" & Rows.Count).End(xlUp).Row).Select
Stop
Range("U2").Formula = "=VLOOKUP($E2,'" & X & "]shtName'!$B$1:$AP$99999,41,0)"
Range("U2").AutoFill Destination:=Range("U2:U" & Range("E" & Rows.Count).End(xlUp).Row)
Range("U2:U" & Range("E" & Rows.Count).End(xlUp).Row).Select
Range("V2").Formula = "=VLOOKUP($E2,'" & X & "]shtName'!$B$1:$AP$99999,18,0)"
Range("V2").AutoFill Destination:=Range("V2:V" & Range("E" & Rows.Count).End(xlUp).Row)
Range("V2:V" & Range("E" & Rows.Count).End(xlUp).Row).Select
Cells.Select
Cells.EntireColumn.AutoFit

Upvotes: 0

Views: 1280

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Something like the following should give you the worksheets name out of a file

Dim wbk As Workbook
Set wbk = Workbooks.Open(Filename:="YOUR_FILE_PATH", ReadOnly:=True)

Dim shtName As String
shtName = wbk.Worksheets(1).Name
wbk.Close

Note: We can open the workbook in read only mode if we don't plan to change anything.

Additionally I recommend (for a good code following good practices):

  • Always specify a worksheet.
    Eg for every Range("") like Worksheets("YourSheetName").Range("")
    Or use With statements:

    With Worksheets("YourSheetName")
    .Range("A1").Value = 5   'recognize the starting full stop referring to the with statement
End With

  • Same for every Rows, Columns, Cells, etc.

  • Avoid using .Select, .Activate and Selection. at all.
    (there are many tutorials out there in the Internet how to avoid them).
  • Use Option Explicit and declare all your variables before use.
    (avoids many issues, especially typos).

Upvotes: 2

Related Questions