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

Question

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:

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

What that `Createfolder` from the workbook name creation, wants to be? — FaneDuru, Feb 19 '20 at 16:48
fldrpath = "H:\ProfileV2\Desktop\Pre planning NBU\ Alex list new\" & fldrname This is my folder name. Once I use the 2nd solution, there is no error. In the 1st solution the error is thrown at this line: Custom_Name = ThisWorkbook.Path & Createfolder & "NBU " & name & " - Opportunity list.xlsx" — Geographos, Feb 19 '20 at 16:52
Like I previously said `& Createfolder &` makes no sense... `Createfolder` is not a function to return a path. Even if it would be, in conjunction with `ThisWorkbook.Path` it makes no sense even more... When you need building a path which looks peculiar, try `Debug.Print ThisWorkbook.Path & Createfolder & "NBU " & fldrtitle & " - Opportunity list.xlsx"` and see what it returns. — FaneDuru, Feb 19 '20 at 17:01
OK, How about the last code - my 2nd approach? Because the file is saved just next to the folder I believe, that the mismatch in the code is marginal. Thank you for your hint. — Geographos, Feb 19 '20 at 17:15
Your both codes use the same wrong approach in building the name. I gave you a piece of advice. Using `Debug.Print` in order to se with your eyes what that string construction returns... — FaneDuru, Feb 19 '20 at 17:18
Could you write it down as an answer? I am a bit lost, to be honest. Thank you in advance — Geographos, Feb 19 '20 at 17:40
I tried your code writihng sth like this: ... 'name = Worksheets("Sheet1").Range("A2").Value Debug.Print ThisWorkbook.Path & Createfolder & "NBU " & fldrtitle & " - Opportunity list.xlsx" End Sub But now there is no reaction at all. — Geographos, Feb 20 '20 at 09:54

James Kirkby · Accepted Answer · 2020-02-19T18:02:28.773

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

VBA says: Ambiguous name detected pointing the Function Createfolder() line — Geographos, Feb 19 '20 at 17:25
Your `Function Createfolder()` does not return anything neither. It must be `Function Createfolder() As String`, or declare`CreateFoder` variable at the module level. — FaneDuru, Feb 19 '20 at 17:25

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

1 Answers1