|
本帖最后由 乐乐2006201506 于 2016-6-14 21:07 编辑
怎样将下面代码中创建的文本文件名和打开文件的文件名(保存公式)一致,并使保存的文本文件名内含打开文件的路径。假如原文件(保存文件.xlsm)在桌面,即将文本文件保存为“保存公式 C Users YYB Desktop.txt”,谢谢!
- Sub 读取公式并存入文本文件()
- Dim FormulaCells As Range, Cell As Range
- Dim FormulaSheet As Worksheet
- Dim Row As Integer, myfile$, objFolder
- Dim fso As Scripting.filesystemobject
- Dim mt As Scripting.textstream
- '创建Range对象
- On Error Resume Next
- Set objShell = CreateObject("Shell.Application")
- Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
- '没有找到公式
- If FormulaCells Is Nothing Then
- MsgBox "当前工作表中没有公式!"
- Exit Sub
- End If
- Set objFolder = objShell.BrowseForFolder(0, "请选择文件存放位置", 0, 0)
- If Not objFolder Is Nothing Then
- mypath = objFolder.self.Path
- Else
- mypath = ""
- Exit Sub
- End If
- myfile = mypath & "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & ".txt"
- Set fso = New Scripting.filesystemobject
- Set mt = fso.createtextfile(Filename:=myfile, overwrite:=True)
- '读取公式,同时在状态栏中显示进度。
- For Each Cell In FormulaCells
- mt.write Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & vbCrLf
- mt.write Cell.Formula & vbCrLf
- Next
- mt.Close
- MsgBox "公式保存成功!"
- End Sub
乐乐2006201506 发表于 2016-6-14 20:27
将文本文件保存为“保存公式 C Users YYB Desktop.txt”可以吗?即将“\”,“:”换 ... 能跟我讲讲这么做的目的吗?{:101:} - Sub 读取公式并存入文本文件()
- Dim FormulaCells As Range, Cell As Range
- Dim FormulaSheet As Worksheet
- Dim Row As Integer, myfile$, objFolder
- Dim fso As Scripting.FileSystemObject
- Dim mt As Scripting.TextStream
- '创建Range对象
- On Error Resume Next
- Set objShell = CreateObject("Shell.Application")
- Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
- '没有找到公式
- If FormulaCells Is Nothing Then
- MsgBox "当前工作表中没有公式!"
- Exit Sub
- End If
- Set objFolder = objShell.BrowseForFolder(0, "请选择文件存放位置", 0, 0)
- If Not objFolder Is Nothing Then
- mypath = objFolder.self.Path
- Else
- mypath = ""
- Exit Sub
- End If
- myfile = mypath & "" & Replace(Replace(mypath, "", " "), ":", " ") & Replace(ThisWorkbook.Name, ".xlsm", "") & ".txt"
- Set fso = New Scripting.FileSystemObject
- Set mt = fso.CreateTextFile(Filename:=myfile, overwrite:=True)
- '读取公式,同时在状态栏中显示进度。
- For Each Cell In FormulaCells
- mt.Write Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & vbCrLf
- mt.Write Cell.Formula & vbCrLf
- Next
- mt.Close
- MsgBox "公式保存成功!"
- End Sub
复制代码
|
|