Closed workbook and extract sheet by cell value

I have a closed workbook and I want to be able to extract a worksheet from it. I am able to extract with this code here but I was hoping to extract with name of a cell value.

Say if cell is C1= Sheet2, thats the sheet i want to extract without manually going in to VBA and changing "Sheetnames$"

I just dont know how I can implement that.

Thanks for the help!

[VBA]
Option Explicit

'you can extract data from a closed file by using an
'XLM macro. Credit for this technique goes to John
'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm

Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

[/VBA]

Replace:

Const SheetName$ = "Sheet1"

with:

SheetName$ = Range("C1").Value

I tried it but it didnt work, any ideas?

Using that method, you must "know" the name of the worksheet. If you are going to get some variable worksheet value, use that method to get the value from a "known" worksheet name and cell first.

That method will fail if the worksheet name does not exist. Not all workbooks have a Sheet1.

But i do know the name of the sheets, the only variance is Month

For example. the sheet is named MayWhole, AprWhole, etc.