Code Problem
 

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 files cell is blank.
Could anyone tell me the way to fix the problem?

Thanks in advance.

Tim.

Code Problem
 

"Tim" wrote in message
...
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 - ToolsOptionsView, uncheck Zero Values

Code Problem
 

Hi Bob,

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

Thanks.

Tim.

"Bob Phillips" wrote:


"Tim" wrote in message
...
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 - ToolsOptionsView, uncheck Zero Values