CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel saving the file in the folder created by Excel macro

I would like to save my file in the folder created by...excel macro.

The process of vba excel folder creation has been presented here:

Create a folder and sub folder in Excel VBA

Following it, I have assigned it to my situation:

 Sub Createfolder ()
     Dim fso As Object
     Dim fldrtitle As String
     Dim flrdrname As String
     Dim fldrpath As String

     Set fso = CreateObject("scripting.filesystemobject")
     fldrtitle = Worksheets("Sheet1").Range("A2").Value
     fldrname = "Pre-planning NBU " & fldrtitle
     fldrpath = "H:\ProfileV2\Desktop\Pre planning NBU\ Alex list new\" & fldrname
     If not fso.FolderExists(fldrpath) Then
         fso.Createfolder (fldrpath)
     End If
 End Sub

Where the folder has been created. My way to saving the file, according to the previous query looks as follows:

Sub Save()
    Dim name As String, Custom_Name As String

    name = Worksheets("Sheet1").Range("A2").Value
    Custom_Name = ThisWorkbook.Path & "\" & "NBU " & name & " - Opportunity list.xlsx"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Custom_name, FileFormat:=51
End Sub

Now, I tried to combine them together at some point, where the way 1 was:

Sub Save()
    Call Createfolder

    Dim name As String, Custom_Name As String

    name = Worksheets("Sheet1").Range("A2").Value
    Custom_Name = ThisWorkbook.Path & Createfolder & "NBU " & name & " - Opportunity list.xlsx"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Custom_name, FileFormat:=51
End Sub

Here I am getting error: Expected Function or variable

what I understood, as I cannot fetch the external macro inside the code.

Afterwards I tried another way:

 Sub Savetofolder ()
     Dim fso As Object
     Dim fldrtitle As String
     Dim flrdrname As String
     Dim fldrpath As String
     Dim name As String, Custom_Name As String

     Set fso = CreateObject("scripting.filesystemobject")
     fldrtitle = Worksheets("Sheet1").Range("A2").Value
     fldrname = "Pre-planning NBU " & fldrtitle
     fldrpath = "H:\ProfileV2\Desktop\Pre planning NBU\ Alex list new\" & fldrname
     If not fso.FolderExists(fldrpath) Then
         fso.Createfolder (fldrpath)
     End If

 'name = Worksheets("Sheet1").Range("A2").Value
     Custom_Name = ThisWorkbook.Path & Createfolder & "NBU " & fldrtitle & " - Opportunity list.xlsx"
     Application.DisplayAlerts = False
     ActiveWorkbook.SaveAs Filename:=Custom_name, FileFormat:=51
 End Sub

I switched off the name variable, because it's the same as fldrtitle.

In the result my file is saved next to the created folder as per in the picture below:

enter image description here

Is there any chance to get it saved INSIDE this folder?

Upvotes: 0

Views: 939

Answers (1)

James Kirkby
James Kirkby

Reputation: 308

Think you want this:

Function Createfolder() As String
  Dim fso As Object
  Dim fldrtitle As String
  Dim flrdrname As String
  Dim fldrpath As String

  Set fso = CreateObject("scripting.filesystemobject")
  fldrtitle = Worksheets("Sheet1").Range("A2").Value
  fldrname = "Pre-planning NBU " & fldrtitle
  fldrpath = "H:\ProfileV2\Desktop\Pre planning NBU\ Alex list new\" & fldrname
  If not fso.FolderExists(fldrpath) Then
      fso.Createfolder (fldrpath)
  End If

  Createfolder = fldrpath
End Function

Sub Save()

  Dim name As String, Custom_Name As String

  name = Worksheets("Sheet1").Range("A2").Value
  Custom_Name = Createfolder() & "\NBU " & name & " - Opportunity list.xlsx"
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs Filename:=Custom_name, FileFormat:=51
End Sub

EDIT

You aren't really gaining anything by separating the folder creation, this would probably be better:

Sub Save()

  Dim fso As Object
  Dim fldrtitle As String
  Dim flrdrname As String
  Dim fldrpath As String
  Dim filename As String
  Dim name As String

  'Construct folder name
  fldrtitle = Worksheets("Sheet1").Range("A2").Value
  name = Worksheets("Sheet1").Range("A2").Value 'looks like this is the same as fldrtitle, could just use same variable below
  fldrname = "Pre-planning NBU " & fldrtitle
  fldrpath = "H:\ProfileV2\Desktop\Pre planning NBU\ Alex list new\" & fldrname

  'Create folder if it doesn't exist
  Set fso = CreateObject("scripting.filesystemobject")
  If not fso.FolderExists(fldrpath) Then
      fso.Createfolder (fldrpath)
  End If

  'Construct filename and save
  filename = fldrpath & "\NBU " & name & " - Opportunity list.xlsx"
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs Filename:=filename, FileFormat:=51
End Sub

Upvotes: 1

Related Questions