J
Joe Derr
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_...orkbook_using_VBA_in_Microsoft_Excel/357.html
'11-18-2004
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & FName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
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_...orkbook_using_VBA_in_Microsoft_Excel/357.html
'11-18-2004
With ActiveSheet.Range(cellRange)
.FormulaArray = "='" & fPath & "\[" & FName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With