In this case, your program would also need to create the new folder upon saving the first report for the year. The destination path usually starts off a root maintained by the user in a Worksheet cell, a VBA constant, or an environment variable. Of course, I could create folders per customers, not per years, and any other combination I desire to best serve my business needs. You can see that the customer name was also stripped of any spaces and preceded also by the customer number as it is maintained in the system.
I pad the customer number with zeros for sorting the files in the folder properly and for visual clarity. The following are all examples of cases in which I needed to construct file names as part of projects producing reports of all sorts:. Perhaps you can identify your case in the above list or add other use-cases to it. One way or another, saving files from Excel programs is a much-needed task, and today you are going to have the VBA code to do just that — properly.
Following the above example of full path and file name, we need to construct the following path and have the function return this path as a string to its calling function:. The function receives the report year as an argument; however it can be calculated in any other way, of course. I often construct an array with the customer report information and pass over the array as a pointer to the function handling the report. For clarity and brevity, I omitted error handling, however they may be needed here as well.
If needed, I am creating the folder with the MkDir command. How do I know if it is needed? I am calling a function that checks if the folder exists. Thread starter Simbo Start date Oct 23, Simbo Board Regular. Joined May 28, Messages Hi all, I currently have this code: Code:.
Excel Facts. Click here to reveal answer. Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM. Joined Apr 19, Messages 23, Hi If you always want the first 3 directories in the structure then: Code:.
Thanks for the reply. Is there a way to return the previous folder rather than the first 3? Please define "previous folder" - which folder exactly are you referring to? Is this what you are looking for Rich BB code :. Thanks for the response, Rick. Please excuse my naivity as I dont know a lot about VB. Again, Richard's code gives the correct path up to the first 3 directories but as stated this would need to be amended depending on number of sub folders once the file is moved.
Is there a way to return the path up to the current folder? So it will automatically pick up the number of folders if there is more than 3? So the file with the code in is in folder "Citi RBS Daily Batch Totals" but could the code automatically return the full path leading up to that folder? Basically I need that path so that later on in the code I can reference another file within it.
My apologies if my explanation is confusing. Maybe it would be better to post the entire code and explain what I am trying to achieve. Please ensure all information is present and then retry. The sheet will now be cleared and saved automatically. Please call Rosemarie Exell to confirm" Range "C7".
Select 'opens and accesses the sheet number form Range "B4:B". Select Cells. Activate 'finds batch number 1 in range B5:B ActiveCell. The parameters are listed ' below, and all are optional. See examples. You'll have to write special code ' to retrieve that if you're interested. If fResult Then ' You might care to check the Flags member of the ' structure to get information about the chosen file.
Can you include some actual explanation as to why this is an answer? Paste this code in the module. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.
Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
0コメント