Dear all...

Please help. I'm really seek for an advice.

I'm having consolidate button in my main worksheet and if clik on it,
it will sum all the value in each cell that is taken from different
excel file. Which I don't specify the name of the file. My problem are
:
1) if my value has refrence example: in C7 my sum value is 300
my C9 is having =ROUND(C7/7*12,2). my sum value instead of taking the
sum value it become REF. Return Error 2023

2) I think my style is not efficient enough so any suggestion? The
point I have to read from close file is because user doesn't want to
see all the worksheet.

Sub Consolidate1()

Dim SheetName As Worksheet
Dim emptyRows As Long

On Error Resume Next

ReadDataFromAllWorkbooksInFolder

MsgBox "Process completed.", vbInformation + vbOKOnly
End Sub

__________________________________________________

Sub ReadDataFromAllWorkbooksInFolder()

Dim FolderName As String, exName As String, r As Long, cValue As
Variant
Dim exList() As String, FileCnt As Integer, fcnt As Integer
Dim jRow As Long, RowCnt As Integer, updValue As Variant
Dim Lr As Long 'Last Row

FolderName = "C:\budget\sabah" ' create list of workbooks in
foldername

FileCnt = 0
exName = Dir(FolderName & "\" & "*.xls")
While exName < ""
FileCnt = FileCnt + 1
ReDim Preserve exList(1 To FileCnt)
exList(FileCnt) = exName
exName = Dir
Wend
If FileCnt = 0 Then Exit Sub ' get values from each workbook


For fcnt = 1 To FileCnt

For jRow = 7 To 34 'for jRow =3 t0 RowCnt

cValue = GetInfoFromClosedFile(FolderName, exList(fcnt),
"Sheet1", "C" & jRow)

If fcnt = 1 Then
Cells(jRow, 3).Formula = cValue
End If

If fcnt 1 Then
updValue = Sheets("Sheet1").Range("C" & jRow).Value
updValue = updValue + cValue
Cells(jRow, 3).Formula = updValue
End If

Next jRow
''
Next fcnt


End Sub

__________________________________________________ _

Private Function GetInfoFromClosedFile(ByVal exPath As String, _
exName As String, wsName As String, cellRef As String) As Variant

Dim arg As String
GetInfoFromClosedFile = ""
If Right(exPath, 1) < "\" Then exPath = exPath & "\"
If Dir(exPath & "\" & exName) = "" Then Exit Function
arg = "'" & exPath & "[" & exName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)


End Function

I'm having problem in arg, return Error 2023


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Error 2023 is a #Ref error. You need to examine the string you build for
arg and make sure it represents a valid reference.

--
Regards,
Tom Ogilvy

"nuha1578" wrote in message
...

Dear all...

Please help. I'm really seek for an advice.

I'm having consolidate button in my main worksheet and if clik on it,
it will sum all the value in each cell that is taken from different
excel file. Which I don't specify the name of the file. My problem are
:
1) if my value has refrence example: in C7 my sum value is 300
my C9 is having =ROUND(C7/7*12,2). my sum value instead of taking the
sum value it become REF. Return Error 2023

2) I think my style is not efficient enough so any suggestion? The
point I have to read from close file is because user doesn't want to
see all the worksheet.

Sub Consolidate1()

Dim SheetName As Worksheet
Dim emptyRows As Long

On Error Resume Next

ReadDataFromAllWorkbooksInFolder

MsgBox "Process completed.", vbInformation + vbOKOnly
End Sub

__________________________________________________

Sub ReadDataFromAllWorkbooksInFolder()

Dim FolderName As String, exName As String, r As Long, cValue As
Variant
Dim exList() As String, FileCnt As Integer, fcnt As Integer
Dim jRow As Long, RowCnt As Integer, updValue As Variant
Dim Lr As Long 'Last Row

FolderName = "C:\budget\sabah" ' create list of workbooks in
foldername

FileCnt = 0
exName = Dir(FolderName & "\" & "*.xls")
While exName < ""
FileCnt = FileCnt + 1
ReDim Preserve exList(1 To FileCnt)
exList(FileCnt) = exName
exName = Dir
Wend
If FileCnt = 0 Then Exit Sub ' get values from each workbook


For fcnt = 1 To FileCnt

For jRow = 7 To 34 'for jRow =3 t0 RowCnt

cValue = GetInfoFromClosedFile(FolderName, exList(fcnt),
"Sheet1", "C" & jRow)

If fcnt = 1 Then
Cells(jRow, 3).Formula = cValue
End If

If fcnt 1 Then
updValue = Sheets("Sheet1").Range("C" & jRow).Value
updValue = updValue + cValue
Cells(jRow, 3).Formula = updValue
End If

Next jRow
''
Next fcnt


End Sub

__________________________________________________ _

Private Function GetInfoFromClosedFile(ByVal exPath As String, _
exName As String, wsName As String, cellRef As String) As Variant

Dim arg As String
GetInfoFromClosedFile = ""
If Right(exPath, 1) < "\" Then exPath = exPath & "\"
If Dir(exPath & "\" & exName) = "" Then Exit Function
arg = "'" & exPath & "[" & exName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)


End Function

I'm having problem in arg, return Error 2023


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/