[help] macro to open several folders

**Gila** · 10-02-2013, 03:58 PM

Hi everyone, I need a macro that Open a speciefic directory that has several subfolders in it, open each subfolder, open all files, run a speciefic macro that I have, close the file, leave and do it on the next subfolder.

It's something like this:

> open speciefic directory "P:\CONTROLADORIA\FLUXOS EM ANDAMENTO"
> open first subfolder in "PROJETO X1"
> open first file in folder "PROJETO - FLUXO - BRA.xlsm"
> run speciefic macro AJUS
> close file "PROJETO - FLUXO - BRA.xlsm"
> Loop on all files in subfolder "PROJETO X1"
> Close subfolder
> open next subfolder...
< Loop on all subfolder in directory "P:\CONTROLADORIA\FLUXOS EM ANDAMENTO"

That will help me so much!
I'd appreciate it a lot.

Thank you!

**Gila** · 10-03-2013, 01:17 PM

Goes Up!

**patel45** · 10-04-2013, 02:46 AM

Please Login or Register  to view this content.

**Gila** · 10-16-2013, 04:07 PM

Hello!

It works perfectly!!

I would like one more thing, is there a way to close the last file opened before openning the next one?

Thank you!!!
Best regards

**patel45** · 10-16-2013, 04:44 PM

Please Login or Register  to view this content.

**Gila** · 10-16-2013, 04:51 PM

Yeah, I saw that!
But the problem is: when I copy the data from the spread sheet opened, I paste it into a "mother spread sheet" and if I do "ActiveWorkbook.Close True", it will close my active window ("mother spread sheet") and not the file just opened.

Is there a way to do that?

Thanks!!

**patel45** · 10-17-2013, 01:30 AM

the opened workbook must remain active, don't select the main

Please Login or Register  to view this content.

if problems paste here your final code

**Gila** · 10-17-2013, 07:23 AM

Please Login or Register  to view this content.

It doesn't work...

**arlu1201** · 10-17-2013, 07:46 AM

Gila,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

**patel45** · 10-17-2013, 08:31 AM

you did not post the whole final code, you have to post the subs AJUSTEC and REPETE

**Gila** · 10-17-2013, 09:18 AM

Please Login or Register  to view this content.

I've mixed the two subs.

**patel45** · 10-17-2013, 01:06 PM

use this line for opening files

Please Login or Register  to view this content.

then use wb1 for referencing ranges and closing opened workbook

**Gila** · 10-17-2013, 01:26 PM

I'm sorry I didn't understand...could you exemplify?

Thanks a lot!

**patel45** · 10-17-2013, 03:12 PM

I don't know how works your code and his goal, then I can not help you, paste here the whole code with comments, I need to know where you are using the opened workbook

**HaHoBe** · 10-17-2013, 03:30 PM

Hi,patel45,

maybe you have a look into the Application model of Excel. Any range can only be part of a worksheet and via this of a workbook so your suggested code line

Please Login or Register  to view this content.

will cause a runtime error as it lacks the worksheet as a link between the workbook and the range given.

Please Login or Register  to view this content.

ActiveSheet could be replaced with the name of the sheet which should receive the information.

Ciao,
Holger

**Gila** · 10-17-2013, 03:52 PM

Please Login or Register  to view this content.

I have one spreadsheet named "CAIXA", that is where I consolidate all the data from each spreadsheet opened through the command "Workbooks.Open mySubFolder & "\" & strFile".
But before I close the workbook, I have to copy the information and paste it into the spreadsheet "CAIXA"!
Only after that, I may close the recent opened file.

I you have any doubt, please let me know!
Thank!!

**Gila** · 10-17-2013, 04:04 PM

Thank's HaHoBe.

Supose that the name o the sheet is "CSTEST", how the code sould be?
I've tried

Please Login or Register  to view this content.

But nothing...

**HaHoBe** · 10-17-2013, 04:15 PM

Hi, Gila,

the way you applied CSTEST it would be understood to be the codename of the sheet.

Please Login or Register  to view this content.

And I wonder why in your previous post that sheet name was mentioned as CAIXA.

Ciao,
Holger

**Gila** · 10-17-2013, 04:26 PM

HaHoBe,

I just change de name, but it makes no difference to me.
I've tried again, but nothing, it's running error '9' subscript out of range.

I don't know if count, but I using excel 2013.

**HaHoBe** · 10-17-2013, 11:34 PM

Hi, Gila,

Error 9 means that no sheet of the given name is present in the workbook that holds the code, and that is something I can´t do anything about as I don´t know neither how your workbook looks nor the name of the sheets in that workbook.

Ciao,
Holger

**HaHoBe** · 10-18-2013, 01:40 AM

Hi, Gila,

I will try to get a working copy of your codes (there are a couple of flwas in there which you may explain in the meantime).

Within REPETE you use a If...Then...Else-Statement in which you copy in both statements but only paste in the Else-Statement. Is that by default as I would have expected the paste to be made after the end of the If-Statement. Besides I would not relate on Selection as much as you do in the code (I assume this macro recorder code) which could be simplified from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

from looking at the code on it´s own.

Ciao,
Holger

**Gila** · 10-18-2013, 08:48 AM

Hi HaHoBe thanks for the tips!

I'm sorry HaHobe, I made a mistake between sheet and workbook!
I will try to resume my situation:

Please Login or Register  to view this content.

All workbooks "FLUXO" has the same structure, so the sub REPETE it is just to copy the data that I need. <<<<<<<< That I already have too!

Please Login or Register  to view this content.

The problem is: Now that I have the data from the recently "FLUXO" opened, I have to past it into a mother workbook "CSTEST", the name of the sheet (inside "CSTEST") that will receive the data may be anyone...to simplify let's just call it "CAIXA". After pasted in "CAIXA" I must close the "FLUXO" before doing LOOP, and go to the next "FLUXO_1".

Please Login or Register  to view this content.

"CSTEST" contain all the MACROS and SUBS, and it must be remain open until finish the "FLUXO_n".

Thank you!!

**HaHoBe** · 10-19-2013, 03:08 AM

Hi, Gila,

to me there is no use in opening workbooks as ReadOnly but work on them as you could close the workbooks without saving at the end - so what should be the benefit of doing so?.

This code is untested (I don´t know how your data looks like), and I somehow am irritated by

the name of the sheet (inside "CSTEST") that will receive the data may be anyone

which isn´t what I am looking for if I´m trying to write code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In Repete I may be wrong about the code line

Please Login or Register  to view this content.

which may be moved into the Else-Statement instead of having it after the If-Statement (right now that line would copy that area at any time).

Ciao,
Holger

[help] macro to open several folders

LinkBack

Thread Tools

Rate This Thread

Display

[help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Re: [help] macro to open several folders

Thread Information

Users Browsing this Thread

Similar Threads

VBA code to open folders and pdf file inside the folders

Same file stored in 2 different folders does not open in one of the folders

[SOLVED] Macro to open and close all files in a folder and sub folders

open folders

[SOLVED] Open files in different folders

Bookmarks

Bookmarks

Posting Permissions