主题：批量改为数值

 去到对应的单元格，把公式清除即可。

 

Dim Book As New XLS.Book() '定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0) '引用工作簿的第一个工作表
Sheet(1,0).Formula = ""
Book.Save("c:\reports\test.xls")

呃，楼主，你得进步

 

Dim aa As String
aa=vars("aa")
For Each file As String In filesys .GetFiles(ProjectPath & aa)
    If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
        Dim Book1 As New XLS.Book(file)
        Dim Sheet1 As XLS.Sheet = Book1.Sheets("资产负债表")
        Sheet1(1,0).Formula = ""
        Book1.Save(file)
    End If
Next

 你试试这样

 

Dim aa As String
aa=vars("aa")
For Each file As String In filesys .GetFiles(ProjectPath & aa)
    If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
        Dim Book1 As New XLS.Book(file)
        Dim Sheet1 As XLS.Sheet = Book1.Sheets("资产负债表")
        For n As Integer = 0 To 200
            Dim temp As object = Sheet1(n,n).Value
            Sheet1(n,n).Formula = ""
            Sheet1(n,n).Value = temp
        Next
        Book1.Save(file)
    End If
Next

 回复11楼，不会改变原格式啊，它只是去除公式和修改值啊。

 回复13楼，绕了一大圈。你如果要做粘贴数值的效果，就直接用vba去做，几句代码的事。

 

 请上传excel表格，说明哪些单元格要转换。

代码，(mark ExceL粘贴)

 

Dim App As New MSExcel.Application
Dim Wb As MSExcel.Workbook = App.WorkBooks.open("d:\test.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range = Ws.UsedRange
rg.Copy
rg.PasteSpecial(Paste:=MSExcel.XlPasteType.xlPasteValues, Operation:=MSExcel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False)
Wb.Save
App.Quit

 

前两个是foxtable自己封装的，能解决大部分问题；

 

后两个是vba，类似excel的vba，能做的功能更多。

 

再说一句，你得进步，就是一个循环。

 

Dim App As New MSExcel.Application
Dim Wb As MSExcel.Workbook = App.WorkBooks.open("d:\test.xls")
For Each Ws As MSExcel.WorkSheet In Wb.WorkSheets
    Dim Rg As MSExcel.Range = Ws.UsedRange
    rg.Copy
    rg.PasteSpecial(Paste:=MSExcel.XlPasteType.xlPasteValues, Operation:=MSExcel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False)
Next
Wb.Save
App.Quit

完整代码

 

Dim App As New MSExcel.Application
Dim aa As String
aa=vars("aa")
For Each file As String In filesys .GetFiles(ProjectPath & aa)
    If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
        Dim Wb As MSExcel.Workbook = App.WorkBooks.open(file)
        For Each Ws As MSExcel.WorkSheet In Wb.WorkSheets
            Dim Rg As MSExcel.Range = Ws.UsedRange
            rg.Copy
            rg.PasteSpecial(Paste:=MSExcel.XlPasteType.xlPasteValues, Operation:=MSExcel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False)
        Next
        Wb.Save
    End If
Next
App.Quit

 

什么意思，什么链接。