+ Reply to Thread
Results 1 to 6 of 6

Excel macro to create directry based on cell value & save filename to folder from cell

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    Maysville, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Excel macro to create directry based on cell value & save filename to folder from cell

    I am trying to create a directory (which this part works) but I need to save the file with the same cell value to that folder
    Here is the macro I am working with but I get an error at "fileName = .Range("E50").Text.Copy" & it won't save the file. Could someone tell me how to make this work please?
    I appreciate your time.

    [code]
    Sub CreateFolderAndCopy()
    Dim fileName As String

    With Sheets(1)
    If (.Range("E50") = vbNullString) Then Exit Sub

    On Error Resume Next
    MkDir "C:\Share\Incoming\" & .Range("E50")
    On Error GoTo 0

    fileName = .Range("E50").Text.Copy
    End With


    ActiveWorkbook.SaveAs "C:\Share\" & fileName

    End Sub
    [\code]
    Last edited by jrr1962; 09-12-2012 at 12:54 PM.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Excel macro to create directry based on cell value & save filename to folder from cell

    in E50 you have new dir name or file name ?
    show me the content of E50

    fileName = .Range("E5").Text.Copy is wrong remove .copy
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    Maysville, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel macro to create directry based on cell value & save filename to folder from cell

    E50 is just a number derived from a serial number of a tool. This will be different each time. The page is a template with E50 being inputted by the person filling in the form. (ex d50 says serial number & the will input "123456" to cell E50.)
    Is this what your asking?

  4. #4
    Registered User
    Join Date
    09-12-2012
    Location
    Maysville, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel macro to create directry based on cell value & save filename to folder from cell

    Thanks. It saves the file, but not in the new directory created with the macro as needed.

  5. #5
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Excel macro to create directry based on cell value & save filename to folder from cell

    MkDir "C:\Share\Incoming\" & .Range("E50") - this code creates a new dir named Range("E50")

    fileName = .Range("E50").Text - this code assign to fileName Range("E50").Text

    dir and file have the same name, correct ?

  6. #6
    Registered User
    Join Date
    09-12-2012
    Location
    Maysville, Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel macro to create directry based on cell value & save filename to folder from cell

    Quote Originally Posted by patel45 View Post
    MkDir "C:\Share\Incoming\" & .Range("E50") - this code creates a new dir named Range("E50")

    fileName = .Range("E50").Text - this code assign to fileName Range("E50").Text

    dir and file have the same name, correct ?

    Yes, they are the same part number, but the folder will end up with other documents in it.

+ 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