|
发表于 2016-6-14 09:24
|
显示全部楼层
本楼为最佳答案
读取至文本文件,然后从文本文件将公式还原到指定工作表中,最新代码如下:
由于是用FSO来操作,所以工作簿请需引用对应DLL,如下图:
- 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
- Sub 读取文本文件并放入指定工作表()
- Dim fso As Scripting.filesystemobject
- Dim mt As Scripting.textstream
- Dim myfile$, i&, sht$, b As Boolean, rng$, fm$
- myfile = Application.GetOpenFilename("TXT Files (*.txt), *.txt", 0, "选定文件", , False)
- If myfile = "" Then Exit Sub
- Set fso = New Scripting.filesystemobject
- Set mt = fso.OpenTextFile(Filename:=myfile, IOMode:=ForReading)
- sht = "测试" '公式插入那个工作表
- With mt
- Do Until .AtEndOfStream
- i = i + 1
- If i Mod 2 = 1 Then
- b = False
- rng = .ReadLine
- Else
- b = True
- fm = .ReadLine
- End If
- If b = True Then Sheets(sht).Range(rng).Formula = fm
- Loop
- .Close
- End With
- MsgBox "公式还原成功!"
- End Sub
复制代码 |
|