Question

我想在excel里用做个控件用VBA来把当前文件另存为一个txt文本，该txt文本的名字有固定格式，如补充中

名字有时间日期和3位随即数组成：YyyyMmDdhhmmssxxx.txt

Answer 1

'打开文件在菜单栏上有一个"保存TXT文件"按扭
Sub auto_open()
MenuBars(xlWorksheet).Reset
Set mycommandbar = CommandBars("standard")
Set mybutton = mycommandbar.Controls.Add(Type:=msoControlButton)
With mybutton
.Style = msoButtonCaption
.Caption = "保存TXT文件"
.Enabled = True
.OnAction = "SaveText"
End With
End Sub
Sub auto_close()
Set mycommandbar = CommandBars("standard")
For Each mybutton In mycommandbar.Controls
If mybutton.Caption = "保存TXT文件" Then mybutton.Delete
Next
End Sub
Sub SaveText()
Dim File As String
sj = Format(Now, "YYYYMMDDhhmm") & Int(Rnd() * 1000)
File = ThisWorkbook.Path & "\" & sj & ".txt"
On Error Resume Next
Kill File
Sheet1.Copy
ActiveWorkbook.SaveAs Filename:=File, FileFormat:=xlCSV
ActiveWorkbook.Close SaveChanges:=False
MsgBox "文件保存成功!"
End Sub