create folder and save file in folder

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
Office Version
  1. 365
Platform
  1. Windows
hi all - this one is a bit beyond me and need some help. the code below will create my file in the shared network directory, but how can i change this to make a folder with the same name as the file name and then save the file in that folder?

Code:
Option Explicit
Sub SvMe()
     'Saves filename as value of A1 plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Worksheets("Lists").Range("J1").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & Format$(Now(), "dd.mm.yy hhmm AMPM") & ".xlsm"
     ' Change directory to suit your PC, including USER NAME

 ActiveWorkbook.SaveAs "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & newFile
 
     
End Sub

TIA
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
OK so i have this.... it creates the new folder. but not saving the file into the new folder

Code:
Option Explicit
Sub SvMe()
     'Saves filename as value of A1 plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Worksheets("Lists").Range("J1").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & Format$(Now(), "dd.mm.yy hhmm AMPM") & ".xlsm"

    MkDir "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & fName
    
     ' Change directory to suit your PC, including USER NAME
        ChDir _
    "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & fName
    ActiveWorkbook.SaveAs Filename:=newFile
 
     
End Sub
 
Upvote 0
this is now saving the file... still not into the correct folder though

Code:
Option Explicit
Sub SvMe()

    Dim newFile As String, fName As String
    Dim pcrfolderexists As String
   
     ' Don't use "/" in date, invalid syntax
    fName = Worksheets("Lists").Range("J1").Value

        'check if already have a temp folder made on this computer
        pcrfolderexists = Dir("\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & fName, vbDirectory)

        'if not make one
        If pcrfolderexists = vbNullString Then
        MkDir "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & fName
        End If

     'Saves filename as value of A1 plus the current date
     'Change the date format to whatever you'd like, but make sure it's in quotes
        newFile = fName & " " & Format$(Now(), "dd.mm.yy hhmm AMPM") & ".xlsm"

    ActiveWorkbook.SaveAs "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\" & fName & newFile
    
End Sub
 
Upvote 0
Hi Try Below code
VBA Code:
Option Explicit
Sub SaveFileCreatFolder()
    Dim savepath As String
    Dim Fname As String
    savepath = "C:\Users\Arvind\Desktop\PDF"
    Fname = Cells(1, 1).Value
    savepath = savepath & "\" & Fname
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & "-" & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & "\" & Fname
End Sub
 
Upvote 0
Hi Try Below code
VBA Code:
Option Explicit
Sub SaveFileCreatFolder()
    Dim savepath As String
    Dim Fname As String
    savepath = "C:\Users\Arvind\Desktop\PDF"
    Fname = Cells(1, 1).Value
    savepath = savepath & "\" & Fname
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & "-" & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & "\" & Fname
End Sub
hi kevatarvind - your code worked.... sort of.

Fname = cells(10,1).value ... this worked but i got it wrong.
Fname = cells(1,10).value .... i get this error: The specified dimesnion is not valid for the current chart type.

the cell J1 contains the name of the file (and path folder) which has a formula in it... dos this matter?
 
Upvote 0
couple of tweaks and i have it.... thanks

Code:
Sub SaveFileCreatFolder()
    Dim savepath As String
    Dim Fname As String
    Fname = Cells(1, 10).Value
    savepath = "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\"
    savepath = savepath & Fname & "\"
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & " " & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & "\" & Fname
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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