help with vba code to select right workbook depending on the value in the cell

[rv02]

Hi Everyone

I was wondering whether someone can help me with a VB code.

I am trying to write a code to look at the workbook in a directory based on the week number.

I cell H1 I have the week number.

What I need a code to vlookup a external workbook based on the week number.

The external workbooks are named as follows:
Sales Week 5
Sales Week 6
Sales Week 7
.
.
.
Sales Week 13

These workbooks are kept in C:\workbooks directory and are all xls files

so what I need is the code to do following:

vlookup(a1,c:\workbooks\Sales Week ?.xls,2,0)
where ? will be replace by the value in cell H1 i.e. if H1=6 then it will vlookup a1 from c:\workbooks\sales Week 6.xls.

Any help with this code is much appreciate it

[chickenfarmer]

Here's an example of a UDF that does not require any arguments. It returns the complete
path of the Microsoft Excel application on your computer:
I found the following examples, this might be some help.
Function xlPath() As String
xlPath = Application.Path
End Function
On my computer, using Microsoft Office 2013 and entering the formula =xlPath() into a
worksheet cell, this UDF returns the path C:\ProgramFiles\Microsoft Office
15\root\office15.

[chickenfarmer]

Function OpenTest(wb) As Boolean
'Declare a Workbook variable.
Dim wkb As Workbook
'Employ the On Error Resume Next statement to check for, and bypass,
'a run time error in case the workbook is not open.
On Error Resume Next
Set wkb = Workbooks(wb)
'If there is no error, the workbook is open.
If Err = 0 Then
Err.Clear
OpenTest = True
Else
'An error was raised, meaning the workbook is not open.
OpenTest = False
End If
End Function
Sub OpenOrClosed()
'Declare a String type variable that will be the workbook name.
Dim strFileName As String
strFileName = "YourWorkbookName.xlsm"
'Call the OpenTest UDF to evaluate whether or not the workbook is open.
If OpenTest(strFileName) = True Then
'For demo purposes, this message box informs you if the workbook is open.
MsgBox strFileName & " is open.", vbInformation, "FYI…"
Else
'The OpenTest UDF determines that the workbook is closed.
'A message box asks if you want to open that workbook.
Dim OpenQuestion As Integer
OpenQuestion = _
MsgBox(strFileName & " is not open, do you want to open it?", _
vbYesNo, _
"Your choice")
'Example code if you answer No, meaning you want to keep the workbook
closed.
If OpenQuestion = vbNo Then
MsgBox "No problem, it'll stay closed.", , "You clicked No."
Else
'Example code if you answer Yes, meaning you want to open the workbook.
'You need to tell the macro what the full path is for this workbook,
'so another String type variable is declared for the path.
Dim strFileFullName As String
strFileFullName = "C:\Your\File\Path\" & strFileName
'Open the workbook.
Workbooks.Open Filename:=strFileFullName
End If
End If
End Sub

[rv02]

Hi Chickenfarmer

Thanks for your quick reply.

Im not sure whether this helps, I need a vlookup formula to replace ? in a vlookup function as specified in my post above with whathver value is in Cell h1

so if h1 =6 then I need a vb code to use vlookup function like
vlookup(a1,c:\workbooks\Sales Week 6.xls,2,0)

the files where is looking all have the same name with the exception of the week no , I.e c:\workbooks\sales week 7 , c:\workbooks\sales week 8 and so on

Hoe I am being clear.

[rv02]

Sorry everyone

I think I am confusing everyone.

I will try again

I need a vba code to use a vb lookup function from a closed workbook.

The name of the workbook will be stores in my worksheet cell h1.
so if H1 = C:\Workbooks\Sales Week 6. xlsx then my code should be
vlookup(a1,h1,a:c:,2,0)

And I need to copy this formula to all the cells from a1:a100

Hope this clarifies things and sorry for the misleading title.

Thanks in advance