asked on

Cycle through workbooks in a folder

Hi,
This may not be possible using vba in Excel but as my grandad used to say, "if ya don't ask, ya don't get!"

I have a specific folder "WeeklyCeva", that workbooks are dumped into every week. The number of workbooks vary from 6 to 20 and somtimes more. The names of the workbooks vary a lot too. The only constant that I can see is that they all have the suffix ".xlsx". There are no other files of any type in this folder.

All the workbooks in the folder have just one sheet with data, named "Sheet1", (I know.., it's silly).

I have a specific workbook called "CevaCurrent" on my computer. I first delete all the existing sheets except sheet one, but I clear the contents of sheet 1. I then copy and paste the values of all the sheet1's, from all the workbooks in folder "WeeklyCeva", to their own sheet in "CevaCurrent".

Once this is done I have macros set up to collate all the relevant data from all the sheets in "CevaCurrent" onto one sheet into a final workbook.

Is there a way of automating this first sequence using VBA?

That is to say;
Delete the sheets in "CevaCurrent" and clear the contents of remaining sheet, "sheet1"; Cycle through all the workbooks in folder "WeeklyCeva"; copy the whole of sheet1 from each of these workbooks; then paste the values into new consecutive sheets in the workbook named "CevaCurrent"?

I have looked on here but cannot see anything that I could do some jiggery-pokery with to make it work.

Saqib Husain

Try this code. Insert it in the VBA pane for CevaCurrent workbook

Sub collectweeklyworkbooks()
Dim wb As Workbook
Dim ws As Worksheet
Dim fnam As String
Dim fpath As String
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then ws.Delete
Next ws
fpath = "C:\Documents and Settings\Administrator\Local Settings\Temp\"
fnam = Dir(fpath & "*.xlsx")
Do While fnam <> ""
Set wb = Application.Workbooks.Open(fpath & fnam)
ActiveSheet.Name = wb.Name
ActiveSheet.Move after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
fnam = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Open in new window

Stephen Byrom

ASKER

Thanks for your suggestion.
It works well, to a point.
What it does is delete the sheets in CevaCurrent workbook except sheet 1, good so far,
but It doesn't clear the contents of sheet 1, and it makes a completly new workbook with all the correct sheets, rather than inserting the sheets into CevaCurrent (ThisWorkbook).

Sorry if this sounds ungrateful, I don't mean it to.

I will muddle around with the code to see if I can get it to do the above.

Thanks

Saqib Husain

It doesn't clear the contents of sheet 1,

Add this line after line 9
Thisworkbook.sheets("sheet1").cells.clearcontents

You should be placing the code in the CevaCurrent workbook and that should insert the worksheets in it. To make sure you have done this correctly you should
right-click on the sheet tab name for the CevaCurrent worksheet
select fiew code
From the VBA window select Insert > module
Place the code here.

Stephen Byrom

ASKER

Hi again,
I managed to get the sheet1 to clear with.
Worksheets("Sheet1").Cells.ClearContents
inserted after line 9
but I can't get the copied sheets into the workbook, it copies them to a new workbook.

Stephen Byrom

ASKER

oops,
our posts must have been submitted together

Stephen Byrom

ASKER

I have put the code in a Module.
I have now also attached the same code to "Sheet1"

I have attached the workbook in question to maybe clarify it.

Stephen Byrom

ASKER

lets try that again
CevaCurrent.xlsm

Saqib Husain

If it still does not work then try this modification

Option Explicit

Sub CollectCevaSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim cwb As Workbook
Dim fnam As String
Dim fpath As String
Set cwb = ActiveWorkbook
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then ws.Delete
Next ws
Worksheets("Sheet1").Cells.ClearContents
fpath = "C:\Users\StevieB\Documents\CevaWeekly"
fnam = Dir(fpath & "*.xlsx")
Do While fnam <> ""
Set wb = Application.Workbooks.Open(fpath & fnam)
ActiveSheet.Name = wb.Name
ActiveSheet.Move after:=cwb.Worksheets(cwb.Worksheets.Count)
fnam = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Stephen Byrom

ASKER

I think I know where the problem is, although I don't know how to get around it.

this line, "ActiveSheet.Name = wb.Name"

seems to want to name the active sheet in that workbook as the wb name.
I don't think that will work, as the workbook names are all different, but all similar to this, "M0030912008". There is only one sheet in each of the workbooks and that is always called "Page 1". These workbooks are "dumped" there from a PDF converter each week.
I need each of the "Page 1" sheets from each of the workbooks pasting into
fpath = "C:\Users\StevieB\Documents\CevaWeekly"

In my original post I mistakenly said the sheets were named "Sheet1" and not "Page 1" as they are.

Sorry to be a pain

Saqib Husain

Now I am getting confused.

Where should the Page1 of all the files go?

Stephen Byrom

ASKER

I would like this to happen

Delete the sheets in "CevaCurrent" and clear the contents of remaining sheet, "sheet1"; Cycle through all the workbooks in folder "WeeklyCeva"; copy the whole of "Page 1" from each of these workbooks and paste the values into new consecutive sheets in the workbook named "CevaCurrent"?

Thanks for your time

PS.
The new sheets in wb.CevaCurrent do not have to be named, they can just have the generic names of sheet1, 2 ,3 etc

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Byrom

ASKER

Absolutely Brilliant!! First Class!!

You're a star, thank you so much for your time, you've saved me a bunch of mine every week!

Thanks so much

Saqib Husain

if ya don't ask, ya don't get!