VBA - Making new folder in directory

mvervair

New Member
Joined
Aug 3, 2016
Messages
27
Hi all, after much lurking I'm here with a real question.

I'm trying to use VBA to create a new folder named after the client, then save an excel file in that new folder.

I'm able to create the folder, but my folder name output is not what I was expecting (it's coming out as a 10 digit number rather than a name). Here's a sample of the important stuff in vba:

Code:
Sub cust_install_()
Dim copier As Workbook
Dim paster As Workbook
Dim custname As Variant            'this is a customer's last and first name separated by a comma
ReDim custname(0 To 1)       
Dim flnm As String
'Dim flnm2 As String

''''' code, mostly copy and pasting

Set custname = Split(ActiveCell, ", ")
flnm = custname(0)
'flnm2 = custname(1, 1)  --- removed this because I kept getting a Error 9: sub script out of range 

MkDir "U:\Common\" & flnm                     ' to name the directory after the last name of the customer

paster.SaveAs Filename:="U:\Common\" & flnm  & "\" & Range("A13") & " install form"

When I run this, the program completes. But when I look at the output for the folder it is a ten digit number. I know I could just Dim the custname As String and it would work, but I really want to remove the comma and just have the last name and first letter of the first name as the folder's name. Obviously I'm self taught in vba and have huge holes in my skills.

Any ideas would be appreciated!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,305
Try...

Code:
Dim custname As String
Dim aCustName() As String
Dim flnm As String
Dim flnm2 As String

custname = ActiveCell.Value
If Len(custname) = 0 Then
    MsgBox "Activecell is blank.", vbExclamation
    Exit Sub
End If

aCustName = Split(custname, ", ")

flnm = aCustName(0)
If UBound(aCustName) > 0 Then
    flnm2 = aCustName(1)
End If

Notice that it checks whether the active cell is blank. And it also checks whether a first name exists. You may want to change these to suit your needs.

Hope this helps!
 
Last edited:

mvervair

New Member
Joined
Aug 3, 2016
Messages
27
Big thanks Dom! The if statements gave me some ideas to make this a little more user-friendly. Also using aCustName(0) works for naming the directory, I must've screwed something up in declaring and / or setting my custname in the first iteration.

However, in my pursuit of naming the folder with the last name and first name initial (ex: SmithJ), I tried to use add aCustName(1, 1) to the MkDir statement and it gave a Run-time error 9.

Code:
MkDir "U:\Common\" & aCustName(0) & aCustName(1, 1)

Any help is appreciated.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,305
The Split function returns a zero-based, one-dimensional array. Therefore, in order to get the first name, you should use...

Code:
aCustName(1)

And, since you're only interested in the initial, you would use...

Code:
Left(aCustName(1),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,232
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top