Code to locate directory and file

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

How about this?

Code: Select all

                            Worksheets(rC.Value).Move
                            ' **** Begin of modification ****
                            Dim sQue As String
                            sQue = oFolder
                            sQue = Mid(oFolder, Len(sTopPath) + 1)
                            sQue = Left(sQue, InStrRev(sQue, "\") - 1)
                            ActiveWorkbook.SaveAs sDestPath & sYear & " - " & Replace(sQue, "\", " - ") & " - " & rC.Value & ".xlsx", FileFormat:=51
                            ' **** End of modification ****
                            ActiveWorkbook.Close False
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Just a few emotions of gratitude....
:bravo: :bow: :cloud9: :kiss: :hailpraise: :heart: :king: :love: :ribbon: :thumbup: :yep:
TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Morning,

I've just tested the code on the actual directory and file system
It is running well... :smile: TX

One question:
See the image below. It is creating a huge list of VBAProjects? Should I be Setting Objects to Nothing to release memory, or is there anything I should do in the macro to close the workbook or close VBA Projects?

I thought this was covered by these two statements:

Code: Select all

wbText.Close False
Workbooks(sSRCWB).Close False
1.jpg
Any advice?
TX
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

I see that you have removed the line

ActiveWorkbook.Close False

below the line

ActiveWorkbook.SaveAs ...
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Argh...that might be the issue!
I was transferring the macro we worked on over the weekend into the actual file.
Then I did a bit more cleanup. I must have thought that line was a duplicate on the Workbooks(sSRCWB).Close further down the code.

i'll add it in again and test.
TX :stupidme:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

The line

Workbooks(sSRCWB).Close False

is outside the loop, so it is executed only once. It closes the workbook with the range defining which files and funds to process.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Yes, you are correct. It closes the Files and Funds filter file.
It was a lapse in focus...I was doing this before my coffee.
Appreciate your alertness on this Monday morning. :thumbup:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.