CODEWITHSUNDEEP
vbaexcel
Sherbetdab
Sherbetdab

Reputation: 127

Workbook variable name from drop down box

I have 10 workbooks with near identical names "???????? Service Planner.xlsm" where ???????? is a place name. They all have identical tab names and identical column headers. Instead of going into the files 1 by 1 i'd like to have a seperate file that will lookup the information that i need. I'd like to have a drop down box with the "????????" part of the file name, when selected the correct file would open and my seperate file would populate by using vlookup.

I've used worksheet change before and have target.value as a sheet name, i thought i could do the same and use it as part of the workbook name but no matter what i try i can't get it to work. Can anyone give me some tips or point me in the right direction?

Below is the formula i use when it's different sheets i want to look up.

With Range("F5:L" & lRow)
   .Formula = "=IF($C5=""Night"",VLOOKUP($A5,'\\server\drive name\Folder\[filename.xlsm]" & Target.Value & "'!$A:$I,F$1,0)
   .Value = .Value
End With

Any help would be appreciated.

Upvotes: 0

Views: 52

Answers (1)

Vityata
Vityata

Reputation: 43585

If you make a specific custom function, excluding some part of the name of your application, it could look like this:

Option Explicit

Sub TestMe()

    Debug.Print getApplicationName(" Service Planner.xlsm - Excel")

End Sub

Public Function getApplicationName(nameToExclude As String) As String

    Dim length As Long
    length = Len(Application.Caption) - Len(" Service Planner.xlsm - Excel")
    getApplicationName = Left(Application.Caption, length)

End Function

The property used is Application.Caption which returns the whole name of the file + - Excel at the end.

Upvotes: 1

Related Questions