VBA - Making new folder in directory

[mvervair]

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:

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!

 

[Domenic]

Try...

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!

 

[mvervair]

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.

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

Any help is appreciated.

 

[Domenic]

The Split function returns a zero-based, one-dimensional array. Therefore, in order to get the first name, you should use...

aCustName(1)

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

Left(aCustName(1),1)

 

[mvervair]

Great explanation, thanks for clarifying!

 

[Domenic]

You're very welcome!

Cheers!