create folder and save file in folder

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
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
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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?
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,165
Members
417,128
Latest member
Xianter

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
Top