I can not seem to get this to work right. I found a code sniplet from
Joseph Rubin's site and it works as far as what I want so far, but
where there are blank cells, it puts a 0 (zero) in its place. So I
tried to write something that would find and replace those, well that
took alot of time to run, and usually ended up locking up Excel. I
think it has something to do with the .value = .value but not sure..
can someone take a look and suggest some corrections.

I need it to still open a "browse to" window, instead of hard coding
to a directory, the project this is going in, can be where ever the
user saves it at, so it needs this flexiblity.

For this particular sheet, it can have 10 rows or 100 rows, or more..
so it needs to read all of column A thru H. If there is some way of
reading until the end of the last cell of that sheet then that would
work too.. but columns A thru H are filled out.

Private Sub CommandButton1_Click()
' Gets the data from the Personnel Sheet

GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Personnel", "A:H"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
FName As String, sName, cellRange As String)

'Code By: Joseph Rubin
'http://www.exceltip.com/st/Getting_Values_From_A_Closed_Workbook_using_VBA_in _Microsoft_Excel/357.html
'11-18-2004

With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & FName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With

-------

Thanks in advance.

Joe Derr

go here
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.ht