Create File Folder

[Andy]

I am new to this VBA thing so any help would be welcome.

I need to create a file folder for all of the companies
listed in column F of my worksheet. I have a button on
the worksheet with the following line of code attached
but can not see how to move down to the next row.

Also is there a way to check if the folder exists, if it
does to ignore it and move to the next row.

Thanks

Andy


Private Sub CommandButton1_Click()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createfolder("E:/Customers and Contacts/")

a.Close


End Sub

 

[Fredrik Wahlgren]

I am new to this VBA thing so any help would be welcome.

I need to create a file folder for all of the companies
listed in column F of my worksheet. I have a button on
the worksheet with the following line of code attached
but can not see how to move down to the next row.

Also is there a way to check if the folder exists, if it
does to ignore it and move to the next row.

Thanks

Andy


Private Sub CommandButton1_Click()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.createfolder("E:/Customers and Contacts/")

a.Close


End Sub

This is what I found. It looks pretty similar to your code except for the
"/" you have at the end of the path. Remove it and see what happens. Oh,
make sure the folder doesn't exist when you run the code. You may want to do
that in your code in order to avoid errors.
Sub CreateFolder
Dim fso, fldr
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.CreateFolder("C:\MyTest")
Response.Write "Created folder: " & fldr.Name
End Sub/Fredrik

 

[Guest]

Fredrik

Thanks, but how do I add the company name on each row of
my worksheet to the end of E:/customers and contacts/

I need to get for example

E:/customers and contacts/companyA
E:/customers and contacts/companyB
E:/customers and contacts/companyC

This will all come from column F on my worksheet.

Thanks in advance

Andy

 

[Fredrik Wahlgren]

Fredrik

Thanks, but how do I add the company name on each row of
my worksheet to the end of E:/customers and contacts/

I need to get for example

E:/customers and contacts/companyA
E:/customers and contacts/companyB
E:/customers and contacts/companyC

This will all come from column F on my worksheet.

Thanks in advance

Andy

I'm not sure I understand. Do you have the paths in some cells? In that case
you must loop over this range. Here's a link:
http://www.ozgrid.com/VBA/VBALoops.htm

/Fredrik

 

[Bob Phillips]

Private Sub CommandButton1_Click()
Dim i As Long
Dim cLastRow As Long

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To cLastRow
mkdir cells(i,"A").Value
Next i

End Sub

This assumes your folder names start in A1, and go down column A, and that
the folder E:/customers and contacts/exists already.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Does this work:

Private Sub CommandButton1_Click()
Dim i As Interger
Dim LRow As integer

LRow = Cells(Rows.Count,"F").End(xlUp).Row
For i = 1 To LRow
MkDir "E:/Customers and Contacts/" & cells(i,"F").Value
Next i

End Sub

 

[Bob Phillips]

Is this a question to me or the OP? If so, it is basically the same as mine,
but doesn't match his specification, namely the cell contains the full path.
And you should not use an Integer variable for the line count, as there are
more lines in an Excel spreadsheet than an integer can hold.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

I enterpreted the following:
*******************************
I need to get for example

E:/customers and contacts/companyA
E:/customers and contacts/companyB
E:/customers and contacts/companyC
******************************
to mean the OP wanted all his new folders in a specific location and that
his file containing the call may NOT be in that location (E:/Customers....
Therefore I included the location path in the code.

In testing this I see I should have also included the error trapping:
On Error Resume Next
to bypass the case where the folder already exists.

Yes, you're right regarding the declaration of i and LRow as Long rather than
Integers as a general best programming policy. In this particular case I
think the program would overload creating new folders before reaching 32.767
folders,