Extracting Data - Open vs Closed


V

VBA Noob

Hi,

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



Code:
--------------------

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
.NewSearch
'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)

'CODE HERE

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

--------------------



Thanks

VBA Noob
 
Ad

Advertisements

Ad

Advertisements

V

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

Top