Extracting Data - Open vs Closed


VBA Noob


I've got 20 spreadsheets called Week 1 to Week 20 each has two sheets
called Wk 42 T and Week 42 R. They are stored in a shared area on the K
Drive. I want to copy all the data from Column M in both sheets (around
180 to 200 lines) and then transfer the data to a workbook called
"summary" with two sheets called "Totals T" & "Total R".

I've was thinking of using the below code to open the workbooks but is
this the best way??.

If it is the best way I need help with selecting the data from the
Workbooks called Week 1 to Week 20 in the K drive and then looping
through each workbook and the two worksheets and pasting that data in
Column A in "Totals T" (all info from Wk 42 T) & "Total R" (all info
from Wk 42 R) in the workbook called "Summary"

Any help greatly appreciated


Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
'Change path to suit
.LookIn = "C:\MyDocuments\TestResults"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)


wbResults.Close SaveChanges:=True

Next lCount
End If
End With

On Error Goto 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub



VBA Noob





VBA Noob

Thanks Ron.

Just what the doctor ordered.


PS I use your e-mail attachment code all the time. Works a treat too.

Keep up the good work

VBA Noob

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question