时间戳

首页 > 解决方案 > VBA Excel将文件保存在Excel宏创建的文件夹中

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

问题描述

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

vba excel文件夹创建过程已经介绍到这里：

在 Excel VBA 中创建文件夹和子文件夹

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

 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.

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

在此处输入图像描述

有没有机会把它保存在这个文件夹中？

标签： excelvba

解决方案

认为你想要这个：

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

推荐阅读