About Us

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" wrote in message
...
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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with my coding problem?	Dan the Man[_2_]	Excel Worksheet Functions	3	September 11th 07 12:02 AM
First attempt at VBA coding problem	Rick in NS	New Users to Excel	9	January 12th 06 05:11 PM
Coding problem	John[_105_]	Excel Programming	3	June 22nd 05 05:39 PM
Problem coding a Sort	Peter Chatterton[_2_]	Excel Programming	3	September 21st 04 10:41 PM
Excel VBA : Coding Problem	wuming[_16_]	Excel Programming	2	July 28th 04 02:19 AM

Menu

About Us