+ Reply to Thread
Results 1 to 8 of 8

Create File Folder

  1. #1
    Andy
    Guest

    Create File Folder

    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

  2. #2
    Fredrik Wahlgren
    Guest

    Re: Create File Folder


    "Andy" <[email protected]> wrote in message
    news:[email protected]...
    > 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



  3. #3
    Guest

    Re: Create File Folder

    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
    >-----Original Message-----
    >
    >"Andy" <[email protected]> wrote in

    message
    >news:[email protected]...
    >> 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
    >
    >
    >.
    >


  4. #4
    Fredrik Wahlgren
    Guest

    Re: Create File Folder


    <[email protected]> wrote in message
    news:[email protected]...
    > 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



  5. #5
    Bob Phillips
    Guest

    Re: Create File Folder

    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)


    <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > >-----Original Message-----
    > >
    > >"Andy" <[email protected]> wrote in

    > message
    > >news:[email protected]...
    > >> 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
    > >
    > >
    > >.
    > >




  6. #6
    gocush
    Guest

    Re: Create File Folder

    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" wrote:

    > 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)
    >
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > >-----Original Message-----
    > > >
    > > >"Andy" <[email protected]> wrote in

    > > message
    > > >news:[email protected]...
    > > >> 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
    > > >
    > > >
    > > >.
    > > >

    >
    >
    >


  7. #7
    Bob Phillips
    Guest

    Re: Create File Folder

    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)


    "gocush" <[email protected]/delete> wrote in message
    news:[email protected]...
    > 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" wrote:
    >
    > > 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)
    > >
    > >
    > > <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > > >-----Original Message-----
    > > > >
    > > > >"Andy" <[email protected]> wrote in
    > > > message
    > > > >news:[email protected]...
    > > > >> 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
    > > > >
    > > > >
    > > > >.
    > > > >

    > >
    > >
    > >




  8. #8
    gocush
    Guest

    Re: Create File Folder

    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,

    "Bob Phillips" wrote:

    > 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)
    >
    >
    > "gocush" <[email protected]/delete> wrote in message
    > news:[email protected]...
    > > 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" wrote:
    > >
    > > > 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)
    > > >
    > > >
    > > > <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > > > >-----Original Message-----
    > > > > >
    > > > > >"Andy" <[email protected]> wrote in
    > > > > message
    > > > > >news:[email protected]...
    > > > > >> 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
    > > > > >
    > > > > >
    > > > > >.
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1