Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code | Excel Discussion (Misc queries) | |||
CODE PROBLEM | Excel Discussion (Misc queries) | |||
code problem | Excel Programming | |||
vba code problem | Excel Programming | |||
vba code problem | Excel Programming |