T
Tim
Hi folks,
My following code is working ok if I only need to pull in couple cells of
data from each spreadsheet.
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
So far, I have over hundred of cells data in each spreadsheet to pull in.
It will kill me to define all the column heading and cell ID in the code. I
would like to create a spreadsheet call "Structure" and have the following
data:
Field Name Cell ID
F1 C8
F2 D1
F3 H4
Then have a loop in my code to automatice loop through the sheet to pull in
the column name and Cell ID. The loop need to be a dynamic loop because the
Field will be increase or decrease. I tried so many ways but could not work
out. Please show me how to do the loop.
Any help will be appreciated.
Tim.
My following code is working ok if I only need to pull in couple cells of
data from each spreadsheet.
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
So far, I have over hundred of cells data in each spreadsheet to pull in.
It will kill me to define all the column heading and cell ID in the code. I
would like to create a spreadsheet call "Structure" and have the following
data:
Field Name Cell ID
F1 C8
F2 D1
F3 H4
Then have a loop in my code to automatice loop through the sheet to pull in
the column name and Cell ID. The loop need to be a dynamic loop because the
Field will be increase or decrease. I tried so many ways but could not work
out. Please show me how to do the loop.
Any help will be appreciated.
Tim.