excel - 如何保存正在打开的文件？

Question

我有一个适用于一个特定文件的宏。

我怎样才能使它适用于我的所有文件？具体来说，如何更改文件名以保存正在打开的文件？

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Range("A:A,B:B").Select
    Range("B1").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineStacked
    ActiveChart.SetSourceData Source:=Range( _
        "'cwapp5_MemCPU-Date-Mem'!$A:$A,'cwapp5_MemCPU-Date-Mem'!$B:$B")
    ChDir "D:\WayneCSV"
    ActiveWorkbook.SaveAs Filename:="D:\WayneCSV\cwapp5_MemCPU-Date-Mem.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Answer 1

With ActiveSheet.Shapes.AddChart
    .ChartType = xlLineStacked
    .SetSourceData Source:=Range( _
    "'cwapp5_MemCPU-Date-Mem'!$A:$A,'cwapp5_MemCPU-Date-Mem'!$B:$B")
End With

ChDir "D:\WayneCSV"
ActiveWorkbook.SaveAs Filename:="D:\WayneCSV\" & *YourFileNameHere* &".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

将 YourFileNameHere 替换为您要保存文件的名称。

或者，如果您只想保存带有更改的事件工作簿

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.FullName, _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

如果您想遍历 "D:\WayneCSV"中所有可能的工作簿或文件，请告诉我 make it work for all my files? 的含义。天气，这意味着打开 Excel 工作表、工作簿或“D:\WayneCSV”内所有扩展名为 *.xlsx 的文件

编辑:

Dim StrFile As String

StrFile = Dir("D:\WayneCSV\*.CSV") ' Looks up each file with CSV extension

Do While Len(StrFile) > 0 ' While the file name is greater then nothing
     Workbooks.Open Filename:= "D:\WayneCSV\" & StrFile ' Open current workbook

 ActiveSheet.Shapes.AddChart.Select ' Add a chart
 ActiveChart.ChartType = xlLineStacked ' Add a chart type
 ActiveChart.SetSourceData Source:=Range("$A1:$B1", Range("$A1:$B1").End(xlDown)) ' Set the source range to be the used cells in A:B on the open worksheet 
 With ActiveChart.Parent
     .Height = .Height*1.5 'Increase Height by 50% 
     .Width = .Width*1.5   'Increase Width by 50%
 End With 

'Note the setting of the source will only work while there are no skipped blank if you 
'have empty rows in the source data please tell me and i can provide you with another
' way to get the information 


ActiveWorkbook.SaveAs Filename:="D:\WayneCSV\" & StrFile & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False  ' Save file as excel xlsx with current files name 

ActiveWorkbook.Close ' Close when finished before opening next file this can be removed if you'd like to keep all open for review at the end of loop.

StrFile = Dir ' Next File in Dir
Loop

让我知道它是否有效，因为没有您的文件夹和数据我无法测试。但它应该工作。