Please help: Coding Problem

Hi folks,

I posted my question couple day ago, but the answer can't solve my problem.
Please help......

I need a help on my problem. I have the following code to consolidate all
the spreadsheet files within a folder into a master spreadsheet(test.xls).

Dim strFolder As String
Dim strFile As String


strFolder = "c:\NewFolder\"
strFile = Dir("c:\NewFolder\*.xls")

If Len(Dir(strFolder, vbDirectory)) = 0 Then
MsgBox "Folder does not exist."
Exit Sub
End If

Workbooks.Add
Worksheets(1).Name = "Data"
ActiveWorkbook.SaveAs Filename:="c:\NewFolder2\test" & _
Format(Date, "mmddyy") & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Worksheets("Data").Range("A1") = "F1"
Worksheets("Data").Range("B1") = "F2"

i = 2

Do While Len(strFile) > 0
With Worksheets("Data").Cells(i, 1)
.Value = "='" & strFolder & "[" & strFile & "]" & "Data" &
"'!C8"
.Value = .Value
End With
strFile = Dir()
i = i + 1
Loop


End Sub

After I ran the code, I have the following problems.
1. The code will generate some of the data twice from the same file.
2. It put �#REF!� into the cells of master spreadsheet.
3. It put �38353� into the cells instead of date value - �mm/dd/yyyy�
4. It put 0 into the cell if the source file�s cell is blank.
Could anyone tell me the way to fix the problem?

Thanks in advance.

Tim

1: Can't see from the code why this would happen - This is the line that
takes the data from a file in your folder:
..Value = "='" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
I don't see how this could possibly repeat for the same file twice, since
the loop seems to step through the files properly. To test it might be worth
temporarily adding a line of code right after the one I gave above:
Cells(i,2).Value = "'" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
This would show the cell address with the file name it is coming from in the
2nd column of the result sheet; by looking at this you can see where those
repeated values are coming from and it ought to give a clue why you are
seeing the repeated values.

2: If it is putting "#REF!" it means it can't find the address specified by
the line referred to above. Now, C8 cannot be the problem since ANY sheet
has a cell C8; str Folder is specified and the code would not work unless
that was valid; StrFile comes from your Dir function and it is unlikely that
would be wrong (unless someone renames or deletes the file between when it is
found by Dir() and when you try to reference it!), so the most likely thinng
is that it cannot find a worksheet named 'Data.' Again, if you use the trick
I mentioned above you can show the reference Excel is using on those lines
that come out as '#REF!' and check those workbooks to see if they have a
sheet named 'Data.'

3: 38353 is a date (all dates in Excel are based on integer values), but it
is formatted incorrectly so it is showing as a numeric value. You need to
adjust the formatting: after the line .Value = .Value put the line
..NumberFormat = "mm/dd/yyyy"

4: Using a reference to a blank cell always gives the result 0. You could
adjust your code like this:
With Worksheets("Data").Cells(i,1)
If Evaluate("'" & strFolder & "[" & strFile & "]" & "Data" & "'!C8" = ""
Then
.Value = ""
Else
.Value = "='" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
.Value = .Value
End If

HTH!

"Tim" wrote:

> Hi folks,
>
> I posted my question couple day ago, but the answer can't solve my problem.
> Please help......
>
> I need a help on my problem. I have the following code to consolidate all
> the spreadsheet files within a folder into a master spreadsheet(test.xls).
>
> Dim strFolder As String
> Dim strFile As String
>
>
> strFolder = "c:\NewFolder\"
> strFile = Dir("c:\NewFolder\*.xls")
>
> If Len(Dir(strFolder, vbDirectory)) = 0 Then
> MsgBox "Folder does not exist."
> Exit Sub
> End If
>
> Workbooks.Add
> Worksheets(1).Name = "Data"
> ActiveWorkbook.SaveAs Filename:="c:\NewFolder2\test" & _
> Format(Date, "mmddyy") & ".xls", FileFormat:=xlNormal, _
> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> CreateBackup:=False
>
> Worksheets("Data").Range("A1") = "F1"
> Worksheets("Data").Range("B1") = "F2"
>
> i = 2
>
> Do While Len(strFile) > 0
> With Worksheets("Data").Cells(i, 1)
> .Value = "='" & strFolder & "[" & strFile & "]" & "Data" &
> "'!C8"
> .Value = .Value
> End With
> strFile = Dir()
> i = i + 1
> Loop
>
>
> End Sub
>
> After I ran the code, I have the following problems.
> 1. The code will generate some of the data twice from the same file.
> 2. It put “#REF!” into the cells of master spreadsheet.
> 3. It put “38353” into the cells instead of date value - “mm/dd/yyyy”
> 4. It put 0 into the cell if the source file’s cell is blank.
> Could anyone tell me the way to fix the problem?
>
> Thanks in advance.
>
> Tim

Comment out this line.

> .Value = .Value

then go through your sheets and debug your formulas.

I don't see where it would return data from the same workbook twice.

Examine the formulas returning #Ref to see why

38353 is how excel stores the date

? cdate(38353)
01/01/2005

you just need to format the cell

Best you can do about the zero is try to make the formula test the results

rather than
= link
try
=if(link="","",link)

--
Regards,
Tom Ogilvy



"Tim" <[email protected]> wrote in message
news:[email protected]...
> Hi folks,
>
> I posted my question couple day ago, but the answer can't solve my
problem.
> Please help......
>
> I need a help on my problem. I have the following code to consolidate all
> the spreadsheet files within a folder into a master spreadsheet(test.xls).
>
> Dim strFolder As String
> Dim strFile As String
>
>
> strFolder = "c:\NewFolder\"
> strFile = Dir("c:\NewFolder\*.xls")
>
> If Len(Dir(strFolder, vbDirectory)) = 0 Then
> MsgBox "Folder does not exist."
> Exit Sub
> End If
>
> Workbooks.Add
> Worksheets(1).Name = "Data"
> ActiveWorkbook.SaveAs Filename:="c:\NewFolder2\test" & _
> Format(Date, "mmddyy") & ".xls", FileFormat:=xlNormal, _
> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> CreateBackup:=False
>
> Worksheets("Data").Range("A1") = "F1"
> Worksheets("Data").Range("B1") = "F2"
>
> i = 2
>
> Do While Len(strFile) > 0
> With Worksheets("Data").Cells(i, 1)
> .Value = "='" & strFolder & "[" & strFile & "]" & "Data" &
> "'!C8"
> .Value = .Value
> End With
> strFile = Dir()
> i = i + 1
> Loop
>
>
> End Sub
>
> After I ran the code, I have the following problems.
> 1. The code will generate some of the data twice from the same file.
> 2. It put "#REF!" into the cells of master spreadsheet.
> 3. It put "38353" into the cells instead of date value - "mm/dd/yyyy"
> 4. It put 0 into the cell if the source file's cell is blank.
> Could anyone tell me the way to fix the problem?
>
> Thanks in advance.
>
> Tim

Hi K,

Thank you for your suggestion.

The first and second problem can be resolve if I remove the following code:

If Len(Dir(strFolder, vbDirectory)) = 0 Then
MsgBox "Folder does not exist."
Exit Sub
End If

But I can't because of the verification purpose.

I tried the code:

If Evaluate("'" & strFolder & "[" & strFile & "]" & "Data" & "'!C8") = ""
Then
.Value = ""
Else
.Value = "='" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
.Value = .Value
End If

But I got the error message: "type missmatch". Do you know why?

The date problem was taken care.

Thanks a lot.

Tim.
"K Dales" wrote:

> 1: Can't see from the code why this would happen - This is the line that
> takes the data from a file in your folder:
> .Value = "='" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
> I don't see how this could possibly repeat for the same file twice, since
> the loop seems to step through the files properly. To test it might be worth
> temporarily adding a line of code right after the one I gave above:
> Cells(i,2).Value = "'" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
> This would show the cell address with the file name it is coming from in the
> 2nd column of the result sheet; by looking at this you can see where those
> repeated values are coming from and it ought to give a clue why you are
> seeing the repeated values.
>
> 2: If it is putting "#REF!" it means it can't find the address specified by
> the line referred to above. Now, C8 cannot be the problem since ANY sheet
> has a cell C8; str Folder is specified and the code would not work unless
> that was valid; StrFile comes from your Dir function and it is unlikely that
> would be wrong (unless someone renames or deletes the file between when it is
> found by Dir() and when you try to reference it!), so the most likely thinng
> is that it cannot find a worksheet named 'Data.' Again, if you use the trick
> I mentioned above you can show the reference Excel is using on those lines
> that come out as '#REF!' and check those workbooks to see if they have a
> sheet named 'Data.'
>
> 3: 38353 is a date (all dates in Excel are based on integer values), but it
> is formatted incorrectly so it is showing as a numeric value. You need to
> adjust the formatting: after the line .Value = .Value put the line
> .NumberFormat = "mm/dd/yyyy"
>
> 4: Using a reference to a blank cell always gives the result 0. You could
> adjust your code like this:
> With Worksheets("Data").Cells(i,1)
> If Evaluate("'" & strFolder & "[" & strFile & "]" & "Data" & "'!C8" = ""
> Then
> .Value = ""
> Else
> .Value = "='" & strFolder & "[" & strFile & "]" & "Data" & "'!C8"
> .Value = .Value
> End If
>
> HTH!
>
> "Tim" wrote:
>
> > Hi folks,
> >
> > I posted my question couple day ago, but the answer can't solve my problem.
> > Please help......
> >
> > I need a help on my problem. I have the following code to consolidate all
> > the spreadsheet files within a folder into a master spreadsheet(test.xls).
> >
> > Dim strFolder As String
> > Dim strFile As String
> >
> >
> > strFolder = "c:\NewFolder\"
> > strFile = Dir("c:\NewFolder\*.xls")
> >
> > If Len(Dir(strFolder, vbDirectory)) = 0 Then
> > MsgBox "Folder does not exist."
> > Exit Sub
> > End If
> >
> > Workbooks.Add
> > Worksheets(1).Name = "Data"
> > ActiveWorkbook.SaveAs Filename:="c:\NewFolder2\test" & _
> > Format(Date, "mmddyy") & ".xls", FileFormat:=xlNormal, _
> > Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> > CreateBackup:=False
> >
> > Worksheets("Data").Range("A1") = "F1"
> > Worksheets("Data").Range("B1") = "F2"
> >
> > i = 2
> >
> > Do While Len(strFile) > 0
> > With Worksheets("Data").Cells(i, 1)
> > .Value = "='" & strFolder & "[" & strFile & "]" & "Data" &
> > "'!C8"
> > .Value = .Value
> > End With
> > strFile = Dir()
> > i = i + 1
> > Loop
> >
> >
> > End Sub
> >
> > After I ran the code, I have the following problems.
> > 1. The code will generate some of the data twice from the same file.
> > 2. It put “#REF!” into the cells of master spreadsheet.
> > 3. It put “38353” into the cells instead of date value - “mm/dd/yyyy”
> > 4. It put 0 into the cell if the source file’s cell is blank.
> > Could anyone tell me the way to fix the problem?
> >
> > Thanks in advance.
> >
> > Tim

Thanks Tom.

Tim.

"Tom Ogilvy" wrote:

> Comment out this line.
>
> > .Value = .Value
>
> then go through your sheets and debug your formulas.
>
> I don't see where it would return data from the same workbook twice.
>
> Examine the formulas returning #Ref to see why
>
> 38353 is how excel stores the date
>
> ? cdate(38353)
> 01/01/2005
>
> you just need to format the cell
>
> Best you can do about the zero is try to make the formula test the results
>
> rather than
> = link
> try
> =if(link="","",link)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Tim" <[email protected]> wrote in message
> news:[email protected]...
> > Hi folks,
> >
> > I posted my question couple day ago, but the answer can't solve my
> problem.
> > Please help......
> >
> > I need a help on my problem. I have the following code to consolidate all
> > the spreadsheet files within a folder into a master spreadsheet(test.xls).
> >
> > Dim strFolder As String
> > Dim strFile As String
> >
> >
> > strFolder = "c:\NewFolder\"
> > strFile = Dir("c:\NewFolder\*.xls")
> >
> > If Len(Dir(strFolder, vbDirectory)) = 0 Then
> > MsgBox "Folder does not exist."
> > Exit Sub
> > End If
> >
> > Workbooks.Add
> > Worksheets(1).Name = "Data"
> > ActiveWorkbook.SaveAs Filename:="c:\NewFolder2\test" & _
> > Format(Date, "mmddyy") & ".xls", FileFormat:=xlNormal, _
> > Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> > CreateBackup:=False
> >
> > Worksheets("Data").Range("A1") = "F1"
> > Worksheets("Data").Range("B1") = "F2"
> >
> > i = 2
> >
> > Do While Len(strFile) > 0
> > With Worksheets("Data").Cells(i, 1)
> > .Value = "='" & strFolder & "[" & strFile & "]" & "Data" &
> > "'!C8"
> > .Value = .Value
> > End With
> > strFile = Dir()
> > i = i + 1
> > Loop
> >
> >
> > End Sub
> >
> > After I ran the code, I have the following problems.
> > 1. The code will generate some of the data twice from the same file.
> > 2. It put "#REF!" into the cells of master spreadsheet.
> > 3. It put "38353" into the cells instead of date value - "mm/dd/yyyy"
> > 4. It put 0 into the cell if the source file's cell is blank.
> > Could anyone tell me the way to fix the problem?
> >
> > Thanks in advance.
> >
> > Tim
>
>
>