vba 将excel工作表保存到基于相同单元格的特定文件夹和文件名

Save excel worksheet to specific folder and filename based on same cell

提问by user3154638

I am trying to save a copy of a worksheet to a specific folder based on cell B8 and name the .xlsx file based on the cell range B8 too.

我正在尝试将工作表的副本保存到基于单元格 B8 的特定文件夹，并根据单元格范围 B8 命名 .xlsx 文件。

For example, a user first creates a new folder named "test1", & then enters this folder name/text into cell "B8". He/she will activate the macro after completing their work on the worksheet, & it will save a copy to the folder named "test1" and name the .xlsx file as "test1". (So the .xlsx file will be named as "testfolder1" and the folder where it is stored is also called "test1")

例如，用户首先创建一个名为“test1”的新文件夹，然后将该文件夹名称/文本输入到单元格“B8”中。他/她在完成工作表上的工作后将激活宏，并将副本保存到名为“test1”的文件夹中，并将 .xlsx 文件命名为“test1”。（因此 .xlsx 文件将被命名为“testfolder1”，并且存储它的文件夹也被称为“test1”）

I am using the following code to save a copy of the worksheet to a folder. Just can't figure out how to include the cell B8 into the SaveAs line. Too new with VB to figure it out.

我正在使用以下代码将工作表的副本保存到文件夹中。只是不知道如何将单元格 B8 包含到 SaveAs 行中。VB太新了，无法弄清楚。

Sub SaveForm()

 exampleForm = Range("B8").Value

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveSheet.Copy
With ActiveWorkbook.ActiveSheet
    .Range("42:" & Rows.Count).EntireRow.Delete xlShiftDown                      
    .Range(.Cells(1, "J"), .Cells(1, Columns.Count)).EntireColumn.Delete xlToRight   
    .Parent.SaveAs "C:\Users\JohnSmith\Desktop\ExtractedWorksheet\" &  exampleForm & ".xlsx"                 
    .Parent.Close False
End With
End Sub

Appreciate any input and hopefully my ending goal is understandable. -Thanks!

感谢任何输入，希望我的最终目标是可以理解的。-谢谢！

采纳答案by FreeMan

I thinkthis is what you're after, give it a try:

我认为这就是你所追求的，试一试：

Sub SaveForm()
Static Path as string
Static FileName as string

if len(Path) = 0 then
  Path = Range("B8")
  if right(Path,1) <> "\" then
    'make sure the path is "\" terminated
    Path = Path & "\"
  End if
else
  FileName = Range("B8")

  'Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  ActiveSheet.Copy   'not sure why you're doing this, but do so if it makes sense elsewhere in your code
  With ActiveWorkbook.ActiveSheet
    .Range("42:" & Rows.Count).EntireRow.Delete xlShiftDown                      
    .Range(.Cells(1, "J"), .Cells(1, Columns.Count)).EntireColumn.Delete xlToRight   
    .Parent.SaveAs "C:\Users\JohnSmith\Desktop\ExtractedWorksheet\" & Path & _
                   FileName & ".xlsx"                 
    .Parent.Close False
  End With
  Path = ""
  FileName = ""
End if
End Sub

If you call this code from your worksheet_OnChange event, then when cell B8 is updated, it will:

如果您从 worksheet_OnChange 事件调用此代码，则当单元格 B8 更新时，它将：

1. check to see if you have a Pathstored. If not, assume this is the Path
2. If you have a Pathalready, assume this is the FileNameand save it.

1. 检查你是否有一个Path存储。如果不是，假设这是Path
2. 如果你已经Path有了，假设这是FileName并保存它。

Leave the 'Application.ScreenUpdating` commented out until everything is working OK, then put it back in. Makes figuring out what's going on much easier.

将“Application.ScreenUpdating”注释掉，直到一切正常，然后再将其放回原处。让弄清楚发生了什么变得更加容易。

UPDATEbased on your latest comment on OP:

根据您对 OP 的最新评论进行更新：

Sub SaveForm()

  'Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  ActiveSheet.Copy   'not sure why you're doing this, but do so if it makes sense elsewhere in your code
  With ActiveWorkbook.ActiveSheet
    .Range("42:" & Rows.Count).EntireRow.Delete xlShiftDown                      
    .Range(.Cells(1, "J"), .Cells(1, Columns.Count)).EntireColumn.Delete xlToRight   
    .Parent.SaveAs "C:\Users\JohnSmith\Desktop\ExtractedWorksheet\" & _
                   Range("B8")  & "\" & FileName & ".xlsx"                 
    .Parent.Close False
  End With
  Path = ""
  FileName = ""
End Sub

回答by sedreddin

Here is one that I have created for a project that I worked on.

这是我为我参与的一个项目创建的一个。

1. I first named a cell (through Excel user interface-formula-name manager-define name) and called it prform_prnumber.
2. I passed the value in that to a variant variable in vba and called it prnumber.
3. I then used that variable as name in the exporttopdf method.
4. I kept the path as that of the workbook.

1. 我首先命名了一个单元格（通过 Excel 用户界面-公式-名称管理器-定义名称）并将其命名为 prform_prnumber。
2. 我将其中的值传递给 vba 中的一个变体变量，并将其命名为 prnumber。
3. 然后我在 exporttopdf 方法中使用该变量作为名称。
4. 我保留了工作簿的路径。

This code will run (once the button is clicked) in the active worksheet:

此代码将在活动工作表中运行（单击按钮后）：

Sub exporttopdf()

Dim prnumber As Variant
Set prnumber = ActiveWorkbook.Names("prform_prnumber").RefersToRange

ActiveSheet.ExportAsFixedFormat xlTypePDF, ActiveWorkbook.Path & "/" & filesavename & ".pdf", , , False

End Sub