Gathering data from all xl files in a folder

  • Thread starter Thread starter Okieviking
  • Start date Start date
O

Okieviking

Unfortunately I haven't used VBA in several years. I used to know how to do
this. I have a folder where a daily report will be placed in the form of a
new Excel sheet every day. The only files in the folder will be these daily
reports. I want to monitor certain values on that report, so I need to
import the data from a few cells an put into a different spreadsheet. (Is it
easier for the "master file" to be in the same folder, or a different
folder?) The reports have Macro's and links, so when I open the file there
will be two popups (enable Macro, and update sheet). I don't need to enable
the Macro nor update the sheet. I need the data from cells B13 and B26 in
the report, put into columns for me to plot. Could someone help me out?
Also, the file names will indicate date or order. Will the files in the
folder be accessed in a specified order, or will I have to extract the data
from within the report?
 
Place the files in a different folder and try the below macro from your
master workbook..The values from these two cells will be picked up and
updated in the active sheet for the master workbook ColA/ColB

Sub Macro()

Dim strFile As String, strFolder As String, lngRow As Long
Dim ws As Worksheet, wb As Workbook

strFolder = "c:\" 'Adjust to suit
lngRow = 1 'Starting row number in master file
Set ws = ActiveSheet
Application.ScreenUpdating = False
strFile = Dir(strFolder & "*.xls", vbNormal)
Do While strFile <> ""
Set wb = Workbooks.Open(strFile, ReadOnly = True)
ws.Range("A" & lngRow) = wb.ActiveSheet.Range("B13").Value
ws.Range("A" & lngRow) = wb.ActiveSheet.Range("B26").Value
lngRow = lngRow + 1
wb.Close False
Set wb = Nothing
strFile = Dir
Loop
Application.ScreenUpdating = True

End Sub



If this post helps click Yes
 
Thanks Jacob! It works great! However, the people posting the reports save
it on different worksheets as the active sheet. How do I specify I want the
data from sheet 1?
 
Never mind that last request. Sheet 1 turned out to be a hidden sheet I was
unaware of.

Problem solved. Thanks again for the help!
 
Hi "Okieviking"

Good to hear it helped....

If you want to specify the sheet name; try the below
ws.Range("A" & lngRow) = wb.Sheets("Sheet3").Range("B13").Value

If you want to specify the sheet number; if you dont know the name (second
sheet)
ws.Range("A" & lngRow) = wb.Sheets(2).Range("B13").Value


If this post helps click Yes
 
Back
Top