如何通过引用两个单元格自动命名电子表格？ [英] How do I name a spreadsheet automatically, by referencing two cells?

问题描述

我刚刚完成了一个自动生成报告的一个精巧的宏。它工作得很好，但是我需要它根据两个单元格中的数据自动命名电子表格。

I've just finished writing a spiffy macro for automatically generating reports. It works well, but I need it to automatically name the spreadsheet according to the data in two cells.

基本上，此宏创建一个新的电子表格，将信息复制到其中，并创建每月需要的相关数据透视表。

Essentially, this macro creates a new spreadsheet, copies the information over to it, and creates the relevant pivot-tables which are required monthly.

作为其中的一部分，我创建了一个仪表板，用于生成报告，其中包含说明和报告涉及的日期范围。它目前正在创建电子表格新报告。有没有办法创建新的电子表格，并自动将其命名为报告01.01.15至01.02.15？

As part of this I've created a dashboard for generating the report with instructions and a date range the report is to relate to. It currently creates the spreadsheet "NEW REPORT". Is there a way of creating the new spreadsheet and naming it something along the lines of "Report 01.01.15 to 01.02.15" automatically?

我有日期范围作为两个单独的单元格，我知道我必须确保日期范围是使用允许的字符（IE 01.01.15而不是01/01/15） - 我正确的说有一种方式告诉用户他们把日期放在不正确的分隔符上？

I've got the date range as two separate cells, and I'm aware I'll have to make sure the date range is one that will use allowed characters (I.E. 01.01.15 rather than 01/01/15) - am I right in saying there's a way of telling the user they've put the dates in with the incorrect separators?

推荐答案

Option Explicit 
Sub SaveAs() 

    Dim FileName    As String 
    Dim FilePath    As String
    Dim FName       As String 

    FilePath = "C:\Temp" 
    FileName = Sheets("Sheet1").Range("A1").Text
    FName = Sheets("Sheet1").Range("B1").Text
    ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & FName

End Sub 

---

保存在今天的日期

---

To save it on today's date

Dim sSave       As String
sSave = "Reports " & Format(Date, "dd-mm-yyyy")

---

或明天日期

---

Or tomorrow Date

"Reports" & Format(Date + 1, "dd-mm-yyyy")

---

对于文件格式参见示例

ThisWorkbook.SaveAs Filename:=FilePath, fileformat:=52 

这些是Excel 2007-2013中的主要文件格式

These are the main file formats in Excel 2007-2013

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

Or maybe you want to save the one worksheet workbook to csv, txt or prn.

".csv": FileFormatNum = 6
".txt": FileFormatNum = -4158
".prn": FileFormatNum = 36

---

要仅保存一张工作表作为新的工作簿，则需要在保存之前复制工作表

---

To Save only one Sheet as new Workbook then you need to copy the sheet before saving it

Option Explicit
Sub SaveAs()
    Dim Sht As Worksheet
    Dim FileName As String
    Dim FilePath As String

    FilePath = "C:\Temp"
    FileName = Sheets("Sheet1").Range("A1").Text

    Set Sht = ActiveWorkbook.Sheets("Sheet1")

    Sht.Copy

    ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName

End Sub

---

要将多张表另存为新的工作簿，请使用 表格（数组（Sheet1，Sheet2））。复制

---

To Save Multiple sheets as new Workbook then use Sheets(Array("Sheet1", "Sheet2")).Copy

Option Explicit
Sub SaveAs()
    Dim Sht As Worksheet
    Dim Book As Workbook
    Dim FileName As String
    Dim FilePath As String

    FilePath = "C:\Temp"
    FileName = Sheets("Sheet1").Range("A1").Text

    Set Book = ActiveWorkbook

    With Book
        .Sheets(Array("Sheet1", "Sheet2")).Copy
    End With

    ActiveWorkbook.SaveAs FileName:=FilePath & "\" & FileName

End Sub

这篇关于如何通过引用两个单元格自动命名电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！