Extracting data from multiple files

smith4

New Member
Joined
Jul 13, 2004
Messages
16
I have 150 files and I need to extract four pieces of information from each file into one master file. An example of the data I have cell A1 - Name, cell B1 - Bob Hod, cell A2 - Item, cell B2 - 7 LCD screens, cell A3 - Desc, cell B3 - Needed for use, cell B4 - in the office, cell A5 - Price, cell B5 - 10,000.
I am looking for a marco/vba to extract all of the information in column B or where the value=title from all of these files into one master file. I would prefer not to resort to copy and paste.

Any help is appreciated, thanks.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

smith4

New Member
Joined
Jul 13, 2004
Messages
16
Got it but if someone is ever in the same situtation here is the code:

Sub Extracting_Data()
Dim basebook As Workbook
Dim mybook As Workbook
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Cnum As Integer
Dim cell As Range

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\Round2"
'file path
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear

rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)

' This will add the workbook name in column A if you want
basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name

' Copy the cell values from each cell in one row starting in column B
Cnum = 2
For Each cell In mybook.Worksheets(1).Range("B1,B2,B3,B4,B5")
basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value
Cnum = Cnum + 1
Next cell

mybook.Close False
rnum = rnum + 1
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

This site is a life saver
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Thank you for sharing your code. Now we can all learn from your solution.
 

andy_

New Member
Joined
Apr 29, 2015
Messages
18
Hi all

Similar to this code above I am trying to get the code to copy a specific named range in each workbook. So instead of copying the contents of cells B1 to B5 my code would reference the named range "Total". Which is a single cell in each workbook.
As I understand I would need to reference the range at the start of the code? Dim rng as Range ? Am I on the right track?

Thanks
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
Andy I don't think that U are hijacking this thread and it sounds like a reasonable request. So within the context of the above code, instead of "For each cell etc." U would simply do this for sheet1 A1 of the open wb and sheet 1 of the files retrieved ...
Code:
basebook.Worksheets(1).Cells(1, "A").Value = mybook.Worksheets(1).Range("Total").Value
HTH. Dave
 
Last edited:

Forum statistics

Threads
1,141,023
Messages
5,703,801
Members
421,318
Latest member
cg_cartoonexcel

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