Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-01-2020, 06:33 AM
Dave T Dave T is offline Different suffix per entry Windows 7 64bit Different suffix per entry Office 2013
Advanced Beginner
Different suffix per entry
 
Join Date: Nov 2014
Location: Australia
Posts: 46
Dave T is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-01-2020, 06:48 AM
BobBridges's Avatar
BobBridges BobBridges is offline Different suffix per entry Windows 7 64bit Different suffix per entry Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 675
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 04-01-2020, 03:28 PM
Dave T Dave T is offline Different suffix per entry Windows 7 64bit Different suffix per entry Office 2013
Advanced Beginner
Different suffix per entry
 
Join Date: Nov 2014
Location: Australia
Posts: 46
Dave T is on a distinguished road
Default

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
Attached Files
File Type: xlsx Folder data.xlsx (8.7 KB, 2 views)
Reply With Quote
  #4  
Old 04-01-2020, 11:26 PM
Guessed's Avatar
Guessed Guessed is offline Different suffix per entry Windows 10 Different suffix per entry Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,627
Guessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of light
Default

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
Reply With Quote
  #5  
Old 04-01-2020, 11:51 PM
Guessed's Avatar
Guessed Guessed is offline Different suffix per entry Windows 10 Different suffix per entry Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,627
Guessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of lightGuessed is a glorious beacon of light
Default

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
Reply With Quote
  #6  
Old 04-02-2020, 12:17 AM
Dave T Dave T is offline Different suffix per entry Windows 7 64bit Different suffix per entry Office 2013
Advanced Beginner
Different suffix per entry
 
Join Date: Nov 2014
Location: Australia
Posts: 46
Dave T is on a distinguished road
Default

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
Reply With Quote
Reply

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
Different suffix per entry 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft