Get values from 200 different workbooks

[jeh]

Hello Everyone,

I need some advice and some help.

I have a sheet that I use to track when a person has a line out of service, there only three reasons that a person will have a line out. After I fill out this sheet, workbook is copied and pasted to a file. What I want to do is find out which of the three reasons these lines were out of service. There are currently 200 files in that I need to get this information from. So the help part of my question is: How do I go about getting the cell values from four different cells from each one of these workbooks without manually opening everyone of them.

The advice part: What is the best way of keeping a running order of when the line was taking out, and for what reason.

What I need from each workbook.

A) Cell C1....Type
B) Cell G10...Date
C) Cell B10...Who Gave the OK
D) Cell B16...Who did the work



I hope I'm making sense. Thanks for your time,

Jim

 

[PaddyD]

Jeh,

re the 'help' - not enough info to proceed. I guess you'll need a macro that loops through all the files in a directory, extracts the relevant data & copies it as a new record in another workbook. So - obvious questions first - where are your files, what do you want the data to go etc...

re the 'advice' - nothing specific other than store your info in the most efficient way possible, where efficient means (a) don't duplicate data and (b) don't set it up in a way that makes querying your data difficult.

See the links here

http://www.mrexcel.com/board/viewtopic.php?topic=20119&forum=2

for some detailed info.

Post back with more details...


Paddy

 

[jeh]

Hi Paddy,

Yes. That is what I need. What I had in mind was a seperate workbook that could be used to get the data from these files.

The files are in a file G:COMMONDISPATCHTag'sCompleted
All of the files are structured like this "00215R.xls"(the 215 could be a number between 0&225).

The information needed from the files are
Sheet1
A) Cell C1 Value....Type
B) Cell G10 Value...Date
C) Cell B10 Value...Who Gave the OK
D) Cell B16 Value...Who did the work

Thanks for all your help Paddy...Past and present,

Jim

 

[PaddyD]

OK Jim - how's your coding (I ask 'cos mine's crap, & I'm not really in an environment where I can test things out at the moment) - see if you can fit these to your needs, post back if not...

Paddy

 

[rogerm]

I use the following code - attached to a button on a new sheet.

Hope it works for you.

 

[rogerm]

I use the following code - attached to a button on a new sheet.

Hope it works for you.

Private Sub CommandButton1_Click()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant, dValue As Variant, eValue As Variant, fValue As Variant, gValue As Variant, hValue As Variant, jValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:Your FolderYour Subfolder" 'Subsitute your own folder names
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 2
Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "C1")
dValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "G10")
eValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "B10")
fValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "B16")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Cells(r, 3).Formula = dValue
Cells(r, 4).Formula = eValue
Cells(r, 5).Formula = fValue

Next i
End Sub
Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

 

[jeh]

Hi Roger Marshall,

Thanks very much for the code! I have been able to get this far:

When I run the code, it generates a list of all of the workbooks in the folder, but I can't seem to get the values of the cells that you have defined in the lower routine.


"On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function"

Should there be a Macro4 as part of this?

Thanks for your time,

Jim

 

[rogerm]

I'm using excel 2000 and took this out of a a section of code I've built up to read one of the folders in my mailbox, extract all the spreadsheet attachments from it, print them out,strip the data I need from them onto a summary sheet and then back them up to another folder.

Unfortunately I'm not very good at VBA and have managed to piece this all together from various tips on this site and from other bits and pieces. It works for me but I'm not sure how/why. If you like I can E-mail you the workbook so you can try it/adapt it yourself.

 

[jeh]

Hi Roger, that would be great!

Thank you very much!!!

Jim