Code Problem

[Guest]

Hi folks,

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.

 

[Bob Phillips]

After I ran the code, I have the following problems.

3. It put "38353" into the cells instead of date value - "mm/dd/yyyy"

Format these cells as dates

4. It put 0 into the cell if the source file's cell is blank.

Suppress zeroes - Tools>Options>View, uncheck Zero Values

 

[Guest]

Hi Bob,

Thanks for your code but it does not work as I expected.

Thanks.

Tim.