LinkBack URL
About LinkBacks
I am looking for a simple piece of code that loops thru all files in a folder (so opening them and close when done).
Then after opening perform my main code.
When main code is done, close workbook and go to the next file in the folder.
I am looking for a simple piece of code that loops thru all files in a folder (so opening them and close when done).
Then after opening perform my main code.
When main code is done, close workbook and go to the next file in the folder.
Are all your source files in the same structure?
This is straightforward with Power Query (Get & Transform Data). Post sample workbooks.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Yes they are but why would it matter?
It just needs to cycle thru the excel files in a folder, open them, run the main code (that copies fields from the file to my main book) and then close the file again.
Need to fix it with normal VBA for Excel (no power query)
Why not use Power Query? It's perfect for this type of requirement. You have Excel 2016, so the functionality is built in. It's much quicker than VBA opening every file...
Something like:
Please Login or Register to view this content.
Could be I do not know power query and not sure if I and my colleagues (who I am making this for have it).
Well is was playing around with a simple code from the net trying to amend it for my needs:
The StrFile is adepted to my needs opening every thing that begins with that text up to the weeknumber.Please Login or Register to view this content.
But I do not know where to put my main code to get it to work.
With respect, you don't know the VBA to do what you need, either...Originally Posted by rpinxt
As I said, you have Excel 2016 which has Power Query built in. Which Excel version are your colleagues using?
Happy to write the precise query you need, if you upload a sample source workbook, and indicate which data you're trying to collate.
Well I'm building vba models for year now.
No I am no expert but our company has lots of benfits from it.
This is my main code as VBA has to lookin different tabs with different length to pull out the numbers (so maybe it is not as simple as you think)
It is where it opens the workbook to copy from I need a loop that opens all the files in the folder one by one.Please Login or Register to view this content.
Main code should do the rest.
ps:normally I only open 1 data book, but this problem needed to have multiple books in a row openend and that why i was asking for help.
This combines data from all worksheets in all files (with name beginning `02. Daily Week`) in your specified folder.
Give it a try. You might like it.Please Login or Register to view this content.
Well yes if it is better and faster I am sure I will like it.
But it is no flat data source file.
I will attach 1 weekday from 1 of the files.
Be aware all these tabs are not the same length.
And I only need 2 amounts per tab from each book.
So not sure of combining all that data (it is for 1 year so 52 books times 5 tabs...) is usefull.
So you want the Actual and Planned values from Columns C and D, for the last row in Column C containing data, for each worksheet in each file?
Try this:
Please Login or Register to view this content.
Where would I put that code? And how do I activate it.
Further I would only want the last amounts in columns C and D for Actual and Plannend.
For the 5 weekdays in the file (nothing of the other sheets)
New information: workbooks contain more sheets then required. Okay. Here's an amended query to filter only worksheets Mon - Fri:
For how to use Power Query code, see link in my signature (https://excel.solutions/2017/11/powe...te-code-video/)Please Login or Register to view this content.
(edited to make code a little easier to read)
Last edited by Olly; 10-16-2019 at 06:43 AM.
Hey rpinxt,
If you're still looking for a VBA solution, you might want to have a look at this:
https://www.computergaga.com/blog/lo...esystemobject/
https://exceloffthegrid.com/vba-code...r-sub-folders/
No offence, Olly :pPlease Login or Register to view this content.
You're probably much more of an expert than I am.
Bram
Last edited by dunnobe; 10-16-2019 at 08:53 AM.
Thanks Olly and Bram.
I indeed found a solution in the classic VBA code
I will look into the power query solution, maybe I can unravel how to use itPlease Login or Register to view this content.
Hey rpinxt,
Write down your entire code.
No doubt someone can give you some advice on making your code more efficient.
https://analysistabs.com/vba/optimiz...macros-faster/
Speed is everything.
Bram
Last edited by dunnobe; 10-18-2019 at 09:22 AM.
I agree dunnobe.
But I am ok with the speed of my macro
And yes the real wizards can most likely make it much more fast.
But then the code that they are using I do not understand anymore
I like to also understand what is going on. Would have to study on their code and unfortunately I do not have that time at the moment
ps: forgot to close the thread. Sorry.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks