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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top