importing file names and data - change to original

  • Thread starter Thread starter Sheila
  • Start date Start date
S

Sheila

Hi all,

i have a change to the original requirement that I had with this
problem when I thought that each workbook would be just one worksheet,
now it seems there would be 6. What I want to do is to import all the
file names from a specific folder (c:\Jobs) into my worksheet, listing
them down the A Column, then alongside all of these job names, i need
in B1=data from sheet 1, cell g1, then c1=data from sheet 1, cell g2,
d1=data from sheet1, g3, d1=data from sheet1, g4, e1=data from sheet1,
I35, e1=data from Sheet2, I35, f1=Sheet3, I35, G1=data from Sheet4,
I35, h1=data from Sheet5, I35 and finally i1=data from Sheet 6, I35

Of course, if no data exists in these cells I wish for nothing to be
entered.

I have tried working with what i already have (thanks Davi):

Sub summarize_jobs()

Range("A2").Select
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:\jobs"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.FormulaR1C1 = Right(.FoundFiles(i),
Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
ActiveCell.Offset(0, 2).FormulaR1C1 = "='" &
..FoundFiles(i) &
"'!R3C3"
ActiveCell.Offset(0, 2).Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 5).FormulaR1C1 = "='" &
..FoundFiles(i) &
"'!R3C8"
ActiveCell.Offset(0, 5).Copy
ActiveCell.Offset(0, 5).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -7).Select
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.CutCopyMode = False
End Sub

but am lost as to how to make these changes to get so much more
information. Please can someone help?

The last little glitch is this. I also require it to put information
into cell j1 from sheet1, J2. This will be YES or NO, and from that,
I wish k35 to add all the cells showing figures in the I35 data cells
(e1:i1) unless the word YES appears in j1, then the total will be
zero. In other words, e1:j1 = costs of jobs, if YES appears, then the
cost of the jobs is not taken into consideration in k35.

I hope that makes sense. If not, would be happy to do a manual
worksheet showing info required for someone who would like to help me.

Again, TIA

sheila
 
sorry, should read:

Hi all,

i have a change to the original requirement that I had with this
problem when I thought that each workbook would be just one worksheet,
now it seems there would be 6. What I want to do is to import all the
file names from a specific folder (c:\Jobs) into my worksheet, listing
them down the A Column, then alongside all of these job names, i need
in B1=data from sheet 1, cell g1, then c1=data from sheet 1, cell g2,
d1=data from sheet1, g3, e1=data from sheet1, g4, f1=data from sheet1,
I35, g1=data from Sheet2, I35, h1=Sheet3, I35, i1=data from Sheet4,
I35, j1=data from Sheet5, I35 and finally k1=data from Sheet 6, I35

Of course, if no data exists in these cells I wish for nothing to be
entered.

I have tried working with what i already have (thanks Davi):

Sub summarize_jobs()

Range("A2").Select
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "C:\jobs"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.*"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
ActiveCell.FormulaR1C1 = Right(.FoundFiles(i),
Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
ActiveCell.Offset(0, 2).FormulaR1C1 = "='" &
..FoundFiles(i) &
"'!R3C3"
ActiveCell.Offset(0, 2).Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 5).FormulaR1C1 = "='" &
..FoundFiles(i) &
"'!R3C8"
ActiveCell.Offset(0, 5).Copy
ActiveCell.Offset(0, 5).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -7).Select
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.CutCopyMode = False
End Sub

but am lost as to how to make these changes to get so much more
information. Please can someone help?

The last little glitch is this. I also require it to put information
into cell l1 from sheet1, J2. This will be YES or NO, and from that,
I wish m1 to add all the cells showing figures in the I35 data cells
(e1:j1) unless the word YES appears in l1, then the total will be
zero. In other words, e1:j1 = costs of jobs, if YES appears, then the
cost of the jobs is not taken into consideration in m1.

I hope that makes sense. If not, would be happy to do a manual
worksheet showing info required for someone who would like to help me.

Again, TIA

sheila
 
Back
Top