Get values from 200 different workbooks

jeh

Active Member
Joined
Apr 27, 2002
Messages
250
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Active Member
Joined
Apr 27, 2002
Messages
250
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
May 12, 2002
Messages
53

ADVERTISEMENT

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

Hope it works for you.
 

rogerm

Board Regular
Joined
May 12, 2002
Messages
53
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

Active Member
Joined
Apr 27, 2002
Messages
250

ADVERTISEMENT

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

Board Regular
Joined
May 12, 2002
Messages
53
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

Active Member
Joined
Apr 27, 2002
Messages
250
Hi Roger, that would be great!

Thank you very much!!!

Jim
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top