VBA Excel将文件保存在Excel宏创建的文件夹中

debugcn 发表于 Dev

MKR

我想将文件保存在... excel宏创建的文件夹中。

此处介绍了vba excel文件夹的创建过程：

在此之后，我将其分配给我的情况：

 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

文件夹已创建的位置。根据前面的查询，我保存文件的方式如下：

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

现在，我尝试在某种程度上将它们组合在一起，其中方式1是：

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

在这里我得到了错误：预期的函数或变量

据我了解，因为我无法在代码内部获取外部宏。

之后，我尝试了另一种方法：

 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

我关闭了该name变量，因为它与相同fldrtitle。

结果如下图所示，我的文件被保存在创建的文件夹旁边：

有没有机会将其保存在此文件夹中？

詹姆斯·柯比

想你想要这个：

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

编辑

通过分离文件夹的创建并没有真正获得任何好处，这可能会更好：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。