hi, firstly thanks to those that helped me get this far. I've learnt a lot along the way, but need a bit more assistance please if anyone can help.
i have a spreadsheet that is a listing for all projects or jobs my business has running. What i am trying to do is after having created the file folder for a job (for which a macro exists), then populate the folder with the necessary documents within the folder. i have solved creation of the folders, but now want to automatically name and place the folder. i want to look in the Projects directory to see if there is a folder existing based on the last line of text, (in this case 5278, Line 41), then search for Customer Folder 5278, and create a sub folder using the job no, eg: 52780002. The difficulty is automatically using the last line of data each time, rather than do it manually.
The existing VBA code to create the folder is:
Option Explicit
Last edited by a moderator: Mar 26, 2018
Currently, the user starts to type the customer number in column D which then autofills. the customer number in column A will autofill column D. The user then filters Column C to see what the last number was for that customer, type the next job number in Column B, and then Column C is auto populated with the 8 digit job number.
You will see that there is a Create New Folder macro button, that takes the user to the directory, but then they have to manually type the 8 digit job number and description, per:
what i want to do is create the 8 digit sub folder under the 4 digit customer number, and copy across the various sub directory that is already populated with the various documentation required.
i have a spreadsheet that is a listing for all projects or jobs my business has running. What i am trying to do is after having created the file folder for a job (for which a macro exists), then populate the folder with the necessary documents within the folder. i have solved creation of the folders, but now want to automatically name and place the folder. i want to look in the Projects directory to see if there is a folder existing based on the last line of text, (in this case 5278, Line 41), then search for Customer Folder 5278, and create a sub folder using the job no, eg: 52780002. The difficulty is automatically using the last line of data each time, rather than do it manually.
The existing VBA code to create the folder is:
Option Explicit
Code:
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "C:\Users\Design Laptop1\Dropbox (RMR Engineering)\RMR Engineering - General\RMR - Projects\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function
Sub Testxl()
Dim FName As String
Dim WbName As String
Dim Search As String
Dim Prompt As String
Dim Title As String
Dim MyDir1 As String
Dim MyDir2 As String
Dim Passed As Long
MyDir1 = "\Project Files"
On Error GoTo Err:
FName = GetFolderPath
If FName <> vbNullString Then
Prompt = "Please Input a Directory Name (Customer no. and Name)"
Title = "Name"
Search = InputBox(Prompt, Title)
If Search = "" Then Exit Sub
End If
FName = FName & "\" & Search
MkDir FName
ActiveWorkbook.SaveAs FName & "\" & Search & ".xls"
MkDir ActiveWorkbook.Path & MyDir1
'Test for existence of new folders.files Passed = 1
GetAttr (FName)
Passed = 2
GetAttr (FName & "\" & Search & ".xls")
Passed = 3
GetAttr (ActiveWorkbook.Path & MyDir1)
End
'Sheets("Sheet1").Range("B1").Value = Search **Add if you require the name to be recorded in your spreadsheetErr:
Select Case Err
Case 53: MsgBox "File/Folder not created. Failed at step " & Passed
Case 75: MsgBox "Folder already exists"
End Select
End SubCurrently, the user starts to type the customer number in column D which then autofills. the customer number in column A will autofill column D. The user then filters Column C to see what the last number was for that customer, type the next job number in Column B, and then Column C is auto populated with the 8 digit job number.
You will see that there is a Create New Folder macro button, that takes the user to the directory, but then they have to manually type the 8 digit job number and description, per:
what i want to do is create the 8 digit sub folder under the 4 digit customer number, and copy across the various sub directory that is already populated with the various documentation required.
Last edited by a moderator: