getting workbook names and data

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

Sheila

Stil lin my endeavour to find file names in a particular folder, I
have found this formula that returns the name of ONE only file name in
the folder this book is in. How do I change this to show ALL the
books in the folder?

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-5)

Also, once I have done that, I nned to extract the text that will be
in 2 cells of these books on the one sheet that will be in the book
(sheet1). Is there a way of doing that? Text I require will be in C3
and H3 and these text cells can be placed in the same cells in the
workbook I am summarising things in (ie - C3 and H3)

Any help would be appreciated.

Sheila
TIA
 
Hi, Sheila--
Are you trying to get a list of all Excel workbooks in a particular folder?
Or is it a certain set of workbooks? If so, are their names common? Then
you want to take the value from cells C3 and H3 on sheet1 in each of these
workbooks and list them in columns C and H in your target (summary)
workbook?

Davi
 
1. Yes - Workbooks in a particular folder
2. No, like above - ALL workbooks in this location - c:\jobs
3. No, the names are all different
4. Correct, I want to list the file names in column A, with the
details from Cell C3 & H3 to appear in C column and H column. Each
workbook stored contains only ONE worksheet - Sheet1

Thanks Davi, hope that will help you help me.

Sheila

Hi, Sheila--
Are you trying to get a list of all Excel workbooks in a particular folder?
Or is it a certain set of workbooks? If so, are their names common? Then
you want to take the value from cells C3 and H3 on sheet1 in each of these
workbooks and list them in columns C and H in your target (summary)
workbook?

Davi


Sheila said:
Stil lin my endeavour to find file names in a particular folder, I
have found this formula that returns the name of ONE only file name in
the folder this book is in. How do I change this to show ALL the
books in the folder?

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-5)

Also, once I have done that, I nned to extract the text that will be
in 2 cells of these books on the one sheet that will be in the book
(sheet1). Is there a way of doing that? Text I require will be in C3
and H3 and these text cells can be placed in the same cells in the
workbook I am summarising things in (ie - C3 and H3)

Any help would be appreciated.

Sheila
TIA
Click to expand...
Click to expand...
 
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




Sheila said:
1. Yes - Workbooks in a particular folder
2. No, like above - ALL workbooks in this location - c:\jobs
3. No, the names are all different
4. Correct, I want to list the file names in column A, with the
details from Cell C3 & H3 to appear in C column and H column. Each
workbook stored contains only ONE worksheet - Sheet1

Thanks Davi, hope that will help you help me.

Sheila

Hi, Sheila--
Are you trying to get a list of all Excel workbooks in a particular folder?
Or is it a certain set of workbooks? If so, are their names common? Then
you want to take the value from cells C3 and H3 on sheet1 in each of these
workbooks and list them in columns C and H in your target (summary)
workbook?

Davi


Sheila said:
Stil lin my endeavour to find file names in a particular folder, I
have found this formula that returns the name of ONE only file name in
the folder this book is in. How do I change this to show ALL the
books in the folder?
Click to expand...
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("fil e
name",A1))-FIND("[",CELL("filename",A1))-5)

Also, once I have done that, I nned to extract the text that will be
in 2 cells of these books on the one sheet that will be in the book
(sheet1). Is there a way of doing that? Text I require will be in C3
and H3 and these text cells can be placed in the same cells in the
workbook I am summarising things in (ie - C3 and H3)

Any help would be appreciated.

Sheila
TIA
Click to expand...
Click to expand...
Click to expand...
 
Thats perfect thank Davi, your a genius!!!

sheila

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




Sheila said:
1. Yes - Workbooks in a particular folder
2. No, like above - ALL workbooks in this location - c:\jobs
3. No, the names are all different
4. Correct, I want to list the file names in column A, with the
details from Cell C3 & H3 to appear in C column and H column. Each
workbook stored contains only ONE worksheet - Sheet1

Thanks Davi, hope that will help you help me.

Sheila

Hi, Sheila--
Are you trying to get a list of all Excel workbooks in a particular folder?
Or is it a certain set of workbooks? If so, are their names common? Then
you want to take the value from cells C3 and H3 on sheet1 in each of these
workbooks and list them in columns C and H in your target (summary)
workbook?

Davi


Stil lin my endeavour to find file names in a particular folder, I
have found this formula that returns the name of ONE only file name in
the folder this book is in. How do I change this to show ALL the
books in the folder?


=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("fil
Click to expand...
e
name",A1))-FIND("[",CELL("filename",A1))-5)

Also, once I have done that, I nned to extract the text that will be
in 2 cells of these books on the one sheet that will be in the book
(sheet1). Is there a way of doing that? Text I require will be in C3
and H3 and these text cells can be placed in the same cells in the
workbook I am summarising things in (ie - C3 and H3)

Any help would be appreciated.

Sheila
TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top