• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

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.
0
Stephen Byrom
Asked:
Stephen Byrom
  • 8
  • 6
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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

0
 
Stephen ByromAuthor Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Stephen ByromAuthor Commented:
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.
0
 
Stephen ByromAuthor Commented:
oops,
our posts must have been submitted together
0
 
Stephen ByromAuthor Commented:
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.
0
 
Stephen ByromAuthor Commented:
lets try that again
CevaCurrent.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
Stephen ByromAuthor Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Now I am getting confused.

Where should the Page1 of all the files go?
0
 
Stephen ByromAuthor Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Ok then try this. I have not tested it.
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 <> ""
cwb.worksheets.add
Set wb = Application.Workbooks.Open(fpath & fnam)
ActiveSheet.cells.copy cwb.activesheet.cells
wb.close
fnam = Dir()
Loop
Application.DisplayAlerts = True
End Sub 

Open in new window

0
 
Stephen ByromAuthor Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
if ya don't ask, ya don't get!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now