View Full Version : Closed workbook and extract sheet by cell value

09-20-2012, 12:43 PM
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!

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 & "\"

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)
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

09-20-2012, 12:53 PM

Const SheetName$ = "Sheet1"


SheetName$ = Range("C1").Value

09-20-2012, 12:56 PM
I tried it but it didnt work, any ideas?

Kenneth Hobs
09-20-2012, 01:31 PM
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.

09-20-2012, 02:33 PM
But i do know the name of the sheets, the only variance is Month

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