提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何用单元格值保存Excel文件?

当您使用工作簿时,您是否曾想过基于单元格内容保存Excel文件? 例如,您在单元格A1中有“销售价格”,并且您希望将该工作簿保存为名称销售价格。 当然,您可以将名称输入到另存为对话框中,然后保存它。 但是这对您的工作量来说是一种效率低下的方法。 今天,我会谈谈解决它的快速技巧。

使用VBA代码保存具有特定单元格值的Excel文件

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

箭头蓝色右泡 使用VBA代码保存具有特定单元格值的Excel文件


以下VBA代码可能会帮助您将工作簿保存为指定的单元格值,请按照以下步骤操作:

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications窗口.

2。 点击 插页 > 模块,并将以下代码粘贴到 模块窗口.

VBA代码:保存具有特定单元格值的Excel文件

Private Sub filename_cellvalue()
'Update 20141112
Dim Path As String
Dim filename As String
Path = "C:\Users\dt\Desktop\my information\"
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
End Sub

3。 然后按 F5 键执行代码,并且您的工作簿已被命名为单元格A1的单元格内容。 看截图:

文档保存与 - 细胞增值1

备注:

1。 只需将A1修改为上述代码中的其他单元格引用即可将任何单元格值指定为文件名。

2。 您的活动工作簿将被保存到文件夹中 C:\ Users \ dt \ Desktop \我的信息\ (当你粘贴你的位置而不是它的位置时,最后的斜线必须保留),你可以根据需要更改路径。


相关文章:

如何在Excel中保存文件的日期和时间?

如何在Excel中的标题栏或工具栏中显示文件路径?


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Morena · 24 days ago
    I have a control button for saving as PDF with a specific name to a specific location already working, but I want to modify the code as the filename is the name of the active sheet plus the value of a specific cell from another sheet. How do I do that? This is what I have for saving as PDF:


    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    filename:="\\U:\Documents\My_IT_Stuff\T&A\PP11", _
    OpenAfterPublish:=False
    Application.ScreenUpdating = True
    End Sub

    Thank you!
  • To post as a guest, your comment is unpublished.
    Izabela · 25 days ago
    Hi. I am very excited it could work but for me in it is saying "BASIC runtime error. Sob-procedure or function procedure not defined." and arrow indicating line 6. What should I do?
  • To post as a guest, your comment is unpublished.
    Bruna · 2 months ago
    Se a célula de referencia estiver em uma outra planilha já aberta? Como faço?
  • To post as a guest, your comment is unpublished.
    Tim · 7 months ago
    Hi, I am trying to use this code but when i try to execute I get a runtime 1004 error and it highlights row 7 as invalid: ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
    How can I resolve this? Im using office 365
  • To post as a guest, your comment is unpublished.
    Ahmad · 9 months ago
    Hi friends,
    I have a problem and would be glad if there is someone to help me
    I have a file at drive "F" and folder "ABS" - my filename is "Listdata"
    In an Excel file on my desktop I want cells(4,4) to retieve data from "ABS" sheet(1) and cell (2,2)
    The below code
    Sub Macro1()
    activesheet.Cells(4, 4).Value = " F:\ABS\" & listdata & ".xlsx" & Sheets(1).Cells(2, 2).Value
    End Sub
    but it does not work correctly
    Thanks Friends
  • To post as a guest, your comment is unpublished.
    radulus · 1 years ago
    hi all!

    How can save repetitive input in the same cells, in other cells, with a Macro?

    In sheet1, I input data in A1, B1, C1, for n-times in a day. In sheet2, i need save all this records, in A2, B2, C2, A3, B3, C3, etc. Tnx!
  • To post as a guest, your comment is unpublished.
    Rajiv · 1 years ago
    how to create a macro to save multiple sheets as with their cell values as file names
    Thanks,
    Rajiv
  • To post as a guest, your comment is unpublished.
    Bruce McIntyre · 1 years ago
    Ok so i got this working good using an in sheet button. Now i would like it to just save normally after the initial save as. Can this be done? Using the same save button?
    Thanks Bruce
  • To post as a guest, your comment is unpublished.
    Janice Burbage · 1 years ago
    What if you want to you just want to define the filename but have the option to pick the file path each time you save?

    Also can you specify multiple cells for the filename. The cells are merged also.

    Save to different file paths each time

    Use multiple cells for the filename
  • To post as a guest, your comment is unpublished.
    Stef · 2 years ago
    Please help... I've managed to do everything. But it saves the docs into My Documents instead of the specified server...



    Private Sub CommandButton1_Click()
    Dim Path As String
    Dim FileName1 As String
    Dim FileName2 As String
    Dim FileName3 As String
    Dim FileName4 As String
    Dim FileName5 As String
    Path = "J:\Protection Services\Documents\Booysendal security"
    FileName1 = Range("A2")
    FileName2 = Range("B2")
    FileName3 = Range("C2")
    FileName4 = Range("D2")
    FileName5 = Range("E2")
    ActiveWorkbook.SaveAs Filename:=FileName1 & "-" & FileName2 & "-" & FileName3 & "-" & FileName4 & "-" & FileName5 & ".xls", FileFormat:=xlNormal
    End Sub
  • To post as a guest, your comment is unpublished.
    Keenan · 2 years ago
    Hi,
    What should i add if I wanted to save the active worksheet but using the sheet 1 cell value?
  • To post as a guest, your comment is unpublished.
    Chris · 2 years ago
    This works great... Thank you, but I need to save as a PDF... Any thoughts?

    Thanks

    Chris
    • To post as a guest, your comment is unpublished.
      Martin S · 2 years ago
      same as above but replace ActiveWorkbook.SaveAs with: "ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _"C:\input filenamne.pdf", Quality:= _
      xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
      OpenAfterPublish:=True
  • To post as a guest, your comment is unpublished.
    Kelvin · 2 years ago
    I need to save as a .csv file.
    how would I change this to make it save as a .csv file?
    Private Sub filename_cellvalue()
    'Update 20141112
    Dim Path As String
    Dim filename As String
    Path = "C:\Doorlister\import\DoorJob\"
    filename = Range("AC2")
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal
    End Sub

    Thank you in advance for your help!
    • To post as a guest, your comment is unpublished.
      George · 1 years ago
      I think i did something wrong in here ...

      It gives me an error in line Sheets("shitname").Select

      Probably i did something wrong when i named the "shitname" like the cell K10

      i want to change the sheet to another sheet that is called exactly like the cell K10


      ' gicsel Macro
      '
      ' Keyboard Shortcut: Ctrl+Shift+C
      '
      Selection.Copy
      Sheets("debagat").Select
      Rows("8:8").Select
      ActiveSheet.Paste
      Dim Path As String
      Path = "C:\cgxml\"
      Dim filename As String
      filename = Range("A8")
      Dim shitname As String
      shitname = Range("K10")
      Sheets("shitname").Select
      Application.CutCopyMode = False
      ActiveWorkbook.SaveAs filename:=Path & filename & ".xml", FileFormat:=xltxt, CreateBackup:=False
      Sheets("TOTAL").Select
      ActiveCell.Offset(1, 0).EntireRow.Select
      End Sub
  • To post as a guest, your comment is unpublished.
    Ionel D · 2 years ago
    You guys are lifesavers. Thanks!
  • To post as a guest, your comment is unpublished.
    Ken Gaines · 3 years ago
    I have a template that I don't want to be written over so that the user is required to save the file named by date entered into one of the fields.
    I don't want the user to have to open tools then select / run macro. They might just as well enter the filename.
    • To post as a guest, your comment is unpublished.
      Martin S · 2 years ago
      you can add the =NOW() as a parameter to your filename in the cell before you extract it in the Macro (and also place a standard button in the corner of the page so that the emplyee wont have to open any tools
  • To post as a guest, your comment is unpublished.
    Greg · 3 years ago
    This is pretty cool... but there seems to be a bug that I'm not sure how to correct. I'm using this in a macro-enabled template.
    I open the template,
    I make changes/updates,
    I hit F5 to run the macro... and... a "Go To" dialog box pops up expecting me to key a "reference" in.

    However, if I hit ALT F11, and THEN hit F5, the new .xls file is saved as expected.

    Do I always need to open up the Visual Basic module to run this "save" procedure?
  • To post as a guest, your comment is unpublished.
    Alana · 3 years ago
    Hi,

    Mine will only save as the path and file name. I am only wanting the file name referenced from the cell to save only. Can you please help me?
  • To post as a guest, your comment is unpublished.
    Dan · 3 years ago
    Chris.
    You can concatinate it in a separate cell and then refernce that cell to be your file name value.
  • To post as a guest, your comment is unpublished.
    Chris · 4 years ago
    module does not save in .XlsX ;

    think it has something to do with line 7;; ".xls", FileFormat:=xlNormal;

    Also how can I modify to take the value from 3 different cells concatenated?
    for instance: first name , last name , date; in line 6 of the code?