Please help: Coding Problem

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 
T

Tom Ogilvy

Comment out this line.
.Value = .Value
Click to expand...

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

Thanks Tom.

Tim.

Tom Ogilvy said:
Comment out this line.
.Value = .Value
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Problem 2
Need help for a loop 2
append file name to a table 8
Import Excel to Access (used range only) 2
Problem: Workbook contents vanish 2
Importing multiple Files 10
Static Variable 2
Multiple .CSV into One DB 12

Top