#1
|
|||
|
|||
Different suffix per entry
Hello All,
I have a macro that creates a folder structure. In column A I enter a Plan Number to create an upper level folder. With Column B it creates sub-folders where the upper level folder name is added adjacent to the sub-folder name followed by various suffixes. Currently I use a macro to insert three blank spaces between each entry in column A and I copy and paste four different concatenation formulas adjacent each text string in column A. Is there a better way to achieve an output like that below? I wasn't sure if I should initially repeat the PN string as a contiguous column, add the varying suffixes, convert the formulas to values then delete the last three PN strings so only the first row is a unique entry. Then again maybe it could use/repeat the value in column A until it changes and start over again. Any suggestions wouyld be appreciated. Column A__|__Column B PN09589___|__PN09589 - Calculations __________|__PN09589 - Capacities __________|__PN09589 - Correspondence __________|__PN09589 - Inspections PN09590___|__PN09590 - Calculations __________|__PN09590 - Capacities __________|__PN09590 - Correspondence __________|__PN09590 - Inspections PN09591___|__PN09591 - Calculations __________|__PN09591 - Capacities __________|__PN09591 - Correspondence __________|__PN09591 - Inspections PN09592___|__PN09592 - Calculations __________|__PN09592 - Capacities __________|__PN09592 - Correspondence __________|__PN09592 - Inspections Regards, Dave T |
#2
|
||||
|
||||
I'm missing part of your question. You start out saying your macro creates folders, but you finish up talking about (apparently) a display in an Excel worksheet. Are you saying your VBA program correctly creates the directories on your hard drive, but you're just not sure about the best way to display their names in the worksheet...or what?
|
#3
|
|||
|
|||
Sorry for the confusion Bob,
Maybe I should have left that part out... After the data is in the format like that shown in my previous post I copy and paste it into another workbook that creates the folders and sub folders (that part works well). So if we forget about how I intend to use the end result, my problem is.... I usually start out with a contiguous list of unique Plan Numbers. I then add the PN text and pad the number part with leading zeros so it is PN followed by five numbers or nine numbers. The question I was trying to pose is how to add the name from column A as a prefix to at least four entries (which would eventually become the sub folders). A have attached a workbook with a very basic example, where column A is the padded contiguous unique data. In column C I have (currently) just copied and pasted this to every fourth row. I have attached a very basic mockup copy if that helps. I hope this makes more sense. Regards, Dave T |
#4
|
||||
|
||||
Dave
Doing this as two separate macros seems like extra work. If you already have the code that creates the folder structure, why don't you just modify that to take the Part Numbers list (Column A of you sample doc) and do all of the rest in code? You haven't explained the 5 or 9 digit padding well. How is the code supposed to work out whether it is padding to 5 digits or to 9? Is the space you added in front of the folder names in your sample intentional?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
||||
|
||||
The following is an example to create the folder structure you described with 5 digit padding and only taking inputs from the part numbers you select before running it. Note that you need to set a Reference to the Microsoft Scripting Runtime.
Code:
Sub MakeFolders() 'requires reference to Microsoft Scripting Runtime Dim subFolders() As String, sPath As String, aCell As Range Dim iNum As Long, sCell As String, sFolder As String, i As Integer Dim sPathF As String, sPathSubF As String Dim fso As New FileSystemObject sPath = "C:\Temp\" subFolders = Split("Calculations|Capacities|Correspondence|Inspections", "|") For Each aCell In Selection sCell = Trim(aCell.Value) iNum = Mid(sCell, 3) sFolder = "PN" & Format(iNum, "00000") sPathF = sPath & sFolder If Not fso.FolderExists(sPathF) Then fso.CreateFolder sPathF For i = LBound(subFolders) To UBound(subFolders) sPathSubF = sPathF & "\" & sFolder & " - " & subFolders(i) If Not fso.FolderExists(sPathSubF) Then fso.CreateFolder sPathSubF Next i Next aCell End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#6
|
|||
|
|||
Wow what can I say...
Completely bypassed my multitude of steps in one quick go. Thank you, thank you, thank you The reason for the padding is that the folders are stored within a records management system. The PN and the five numbers or nine numbers with padding ensures they are all sorted in numeric order when viewed in Explorer. Plan numbers between 1 and 99999 will always have five digit padding. Plan numbers between 100000 and 999999999 will always have nine digit padding. I can easily work with what you have done regarding the nine digit numbers. Thank you again Andrew, very much appreciated. Regards, David |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Title and Suffix in contacts | narvan21 | Outlook | 0 | 12-22-2017 02:00 AM |
Batch add suffix to PDF files in folder | Dave T | Windows | 2 | 03-01-2016 08:07 PM |
Macro to rename multiple Word file with same suffix | ozil61 | Word VBA | 2 | 05-06-2014 07:36 AM |
Outlook Restriction? php link suffix | MWE | Outlook | 0 | 11-17-2010 07:25 PM |
Name suffix in Reference Manager | bellczar | Word | 0 | 03-08-2010 03:14 PM |