Sequentially Opening Workbooks

[sbev]

I use an Excel 2007 macro laid out as

...

Windows("Filename.xlsx").Activate

...

ActiveWorkbook.Save
ActiveWindow.Close

...

It works fine but I have to edit filename to open up the next workbook and there are 400+ workbooks in the directory.

Is there a way the workbook names can be put into an array and then loop through the macro doing all the workbooks in one run? Or an alternative approach to sequentially activating workbooks?

Any suggestions appreciated.

 

[SkipVought]

Hi,

You have to EDIT the file name to open the NEXT workbook.

Could you please explain that process in detail along with the LOGIC as to how you logically get from one name to the next name?

 

[sbev]

Say the first time the macro is run, (let's call it) Workbook1.xlsx is activated. Then I change the name to Workbook2, run the macro, change the name to Workbook3, run the macro again, and on and on.

 

[SkipVought]

Are we programming for a hypothetical or an actual? The devil is in the details.

Dim i as Integer

For i = 1 to 401
   With Workbooks.Open("Workbook" & i & ".xlsx")
'????????????
       .Save
       .Close
   End With
Next

So what happens between the OPEN and the Save/Close?

 

[sbev]

After OPEN (a) sheet 2 to the last sheet are grouped, (b) a cell is copied, (c) the result is pasted into another open workbook (call it Otherbook), (d) B2:K2 is copied from the now grouped sheets in Otherbook, (e) the result is pasted as values into the OPEN book, (f) a certain cell is selected, (g) The sheets are ungrouped, (h) the workbook is saved and closed. Otherbook remains open. Then I edit the macro to get the name of the next workbook to be opened and the macro is run again.

There are a few other steps in the macro like ungrouping the sheets in Openbook before pasting into it since the number of sheets in the workbooks being opened varies from book to book. The macro works OK, it's just that I want to avoid having to put in the name of the next workbook that has to be opened.

Is your With, End With Necessary?

I assume that what you propose will work for most of the workbooks, but what about books with names like Fred.xlsx, Jane.xlsx, etc.

 

[SkipVought]

The other workbook is ThisWorkbook that contains the code and may also contain the sheets that you interact with the other workbooks.

There is really too much ambiguous stuff here. "the result is pasted...into the OPEN book" well there are at least two open??? DETAIlS is what we need.

With...End With is not necessary, but it is nice code and it works. There are lots of ways to skin a cat. This is one way that I do it.

How do you LOGICALLY get from Fred to Jane? You doo it with a per-determined list.

 

[sbev]

Whoops, I understand your confusion. I've provided a bad description (I'm a newbie at VBA). When the macro is run, Workbook1 is already open and active. So let me try again.

Workbook1 open and active. Then (a), (b), (c) and (d). Now comes Windows("Workbook1.xlsx").Activate. Then (e) on.

What I want is (1) to have the macro open the workbooks (but not the one I call Otherbook) and do away with me having to edit the Windows("Workbook1.xlsx").Activate line.

Sorry for the mess I've made of it.

 

[sbev]

I've used your loop and concatenation to sequentially open the workbooks and modify the Window.Active line. Everything works great. Thanks a lot for your help.

How do I set up a pre-determined list? Do I put the names in an array dimensioned as variant?

 

[SkipVought]

These workbooks that you are opening one by one are in a folder in some path.

What's the path to those Workbooks, because we'll need that to ACTUALLY open workbooks in this macro.

Otherbook is the workbook that this macro will reside in.

 

[sbev]

The folder is C:\Work\Database\Data Workbooks\

 

[SkipVought]

Are ALL the workbooks to be opened by the macro, in this path?

Are ALL the workbooks in this path to be opened by the macro?

If the answer to both is yes, then the next question is, can the workbooks to be opened, be opened in any order?

If that's a yes, then we'll use another kind of loop to determine which workbook to open next.

Otherwise, if you have a mixture of Workbook1.xlsx and tom.xlsx, dick.xlsx & Harry.xlsx, we'll need a specific list od workbook names to drive the process.

 

[sbev]

Are ALL the workbooks to be opened by the macro, in this path? Yes

Are ALL the workbooks in this path to be opened by the macro? No

Please don't put anymore time into this. Thanks to you I have solved 98% of the problem. The Tom, Dick and Harry workbooks are few in number and can be handled individually.

Thanks again.

 

[SkipVought]

In addition to the answers to the above questions, here are some additional questions.

After OPEN (a) sheet 2 to the last sheet are grouped, (b) a cell is copied,

exactly what cell is copied on what sheet in the workbook that the macro just opened?

(c) the result is pasted into another open workbook (call it Otherbook),

exactly where does the paste occur (sheet & range) in ThisWorkbook?

(d) B2:K2 is copied from the now grouped sheets in Otherbook,

How did the sheets get grouped in Otherbook (ThisWorkbook) as the process (a) only called for grouping in the workbook that the macro just opened?

(e) the result is pasted as values into the OPEN book,

So this appears that stuff gets copied from ThisWorkbook to the workbook that the macro just opened.

(f) a certain cell is selected,

Another ambiguous statement. A cell in what workbook/sheet/range? can't help you with meaninglessness!

(g) The sheets are ungrouped,

The sheets in which workbook, as this also seems nebulous?

There are a few other steps in the macro like ungrouping the sheets in Openbook before pasting into it since the number of sheets in the workbooks being opened varies from book to book.

This need further amplification.

The macro works OK, it's just that I want to avoid having to put in the name of the next workbook that has to be opened.

Well if the macro works okay, let's see the macro. I'm assuming that it does all the stuff that I had questions about that seem ambiguous to me. It will be a piece of cake! We'll just create a loop to open each of the workbooks, by whatever method is appropriate and then your macro can do the rest inside the '???????, right!

 

[Andrzejek]

You may also retrieve all files from particular location (folder) by this code:

Sub ShowMeAllFiles()
Dim strMyFolder As String
Dim strFileName As String

strMyFolder = "C:\Work\Database\Data Workbooks\ "

strFileName = Dir(strMyFolder)

Do While strFileName <> ""
    MsgBox strFileName
    strFileName = Dir
Loop

End Sub

To get your Workbooks, you may check for the extension, and you want to by-pass your Otherbook

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.