|
发表于 2016-6-14 21:48
|
显示全部楼层
本楼为最佳答案
本帖最后由 老司机带带我 于 2016-6-14 21:50 编辑
乐乐2006201506 发表于 2016-6-14 21:42
假如我的源文件“保存公式.xlsm”在“E盘的Excel文件夹中”(路径即“E:\Excel”),而要保存的文本 ...
晕倒,是我之前那个帖子理解错了,怪不得我想为什么把文本文件的路径要放进去,原来是放EXCEL文件的路径,这是对的,加以区分,目的明确 代码如下:- 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", "") & Replace(Replace(ThisWorkbook.Path, "", " "), ":", " ") & ".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
复制代码 |
|