我代码上只下了.Range()写入资料,

但是资料格式却变成文字,

一做运算就会异常

怎样才可以写入EXCEL资料,资料不被变动,又能在EXCEL内对资料做数值运算的方法?
除了自己在VB内先算好再写入外.

木有附件？

你检查你的单元格 的格式，是不是全部设置了为文本格式，或者你写入资料时，代码里指定了文本格式。

看你的 第二张图，所有的数据前面都有一个绿色三角形，表示这是文本格式保存的数字。

我测试了，EXCEL表格内可以进行计算。代码如下：

Private Sub Command1_Click()
Dim EXAPP As Excel.Application
Dim WB As Excel.Workbook
Dim sht As Excel.Worksheet
Dim temp() As String
Set EXAPP = CreateObject("excel.application")
Set WB = EXAPP.Workbooks.Open("c:\test.xlsx")
Set sht = WB.Worksheets("Sheet1")
temp = Split("012,324.45,adbc,030.32", ",")
sht.Range(cells(1, 1), cells(1, 1 + UBound(temp))).Value = temp
WB.Save
WB.Close
Set sht = Nothing
Set WB = Nothing
Set EXAPP = Nothing
End Sub

即使把E1设置为“文本”，excel计算如下图

再找找其它原因
不知道itemlistdata(i)中字符串如何，我在split函数的源字符串中加入不可见字符，比如vbtab后，就不能计算了。



[ 本帖最后由 lianyicq 于 2015-2-2 09:32 编辑 ]

1.EXCEL档案是自动产生的，应该没有办法事先设定单元格.
2.写入资料时，代码里并无指定了文本格式(或是说我也不知道怎么去指定).
3.EXCEL绿色三角型的确是表示文本格式保存的纪录(但是我也不知道是怎么来的).

关于EXCEL的代码 :

Private Sub Transform(sh As Integer)
Dim xlApp As New EXCEL.Application 
Dim xlBook As EXCEL.Workbook 
Dim xlsheet As EXCEL.Worksheet 
Dim xlRang As EXCEL.Range
Dim SheetNum As Integer, i As Integer
Dim File_name As String

    File_name = Mid$(FileNameArray(0), 1, InStrRev(FileNameArray(0), "_")) & Format(Now, "yyyymmddhhmmss") & ".xlsx"
    Text1.Text = Mid$(File_name, InStrRev(File_name, "\") + 1): Text1.ToolTipText = File_name

    If SheetNum = 0 Then
        Set xlApp = CreateObject("Excel.Application")           
'        xlApp.Visible = True                                    
        xlApp.Visible = False                                   
        Set xlBook = xlApp.Workbooks.Add
        Set xlBook = xlApp.Workbooks.Item("Book1")              
        If UBound(FileNameArray) > 0 Then
            For i = 1 To ((UBound(FileNameArray) + 1) + sh)
                If i > 3 Then
                    Set xlsheet = xlBook.Sheets.Add
                Else
                    Set xlsheet = xlBook.Sheets(i)
                End If
                With xlsheet
                    .Cells.Font.Name = "Gulim"                
                    .Cells.Font.Size = 10                    
                    .Cells.ColumnWidth = 4
                    .Select
                    .Cells.Borders.LineStyle = xlContinuous        
                    .Cells.Borders.Weight = xlThin                 
                    .Cells.Borders.ColorIndex = 15                 
                    xlApp.ActiveWindow.Zoom = 75                  
                End With
                DoEvents
            Next i
        End If
    End If
    
    Call ToEXCELItemList(1, xlBook)
    Call ToEXCELBinMap(2, xlBook)
    Call ToEXCELTestInfo(4, xlBook)
    If sh <> 0 Then
        Call ToEXCELEveryItem(5, sh, xlBook)
    End If
    
    If IsFileExist(File_name) = False Then
        xlApp.ActiveWorkbook.SaveAs (File_name)
    Else
        MsgBox "Error!!!"
    End If
    
    xlApp.Visible = True
    xlApp.UserControl = True
      
    Set xlRang = Nothing
    Set xlsheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    ProgressBar1.Value = 100
    
End Sub

Private Sub ToEXCELItemList(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet         
Dim i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String, iMax As Double, iMin As Double

    Set xlsheet = xlBook.Sheets(SheetNumber)
    
    xlsheet.Name = "TestItemList"
    xlsheet.Activate
    xlsheet.Cells.HorizontalAlignment = xlCenter
    
    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列
    
    With xlsheet
        For i = 0 To UBound(ItemListData)
            Temp = Split(ItemListData(i), ",")
            .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp
            If i > 4 Then
                iMax = 0: iMin = 0
                For j = 0 To UBound(Temp)
                    If j > 8 Then
                        If ((Trim$(Temp(1)) <> "") And (Trim$(Temp(j)) <> "")) Then
                            iMax = CDbl(Temp(4)): iMin = CDbl(Temp(3))
                            If Trim$(Temp(2)) <> "F" Then
                                If ((CDbl(Temp(j)) < iMin) Or (CDbl(Temp(j) > iMax))) Then
                                    .Cells(StartNum + i, StartNum1 + j).Font.Color = vbRed
                                End If
                            End If
                        End If
                    End If
                    DoEvents
                Next j
            End If
            DoEvents
            Erase Temp
            ProgressBar1.Value = (i / UBound(ItemListData)) * 100
        Next i
        
        .Columns.AutoFit
        .Rows.AutoFit
        .Range("J6").Activate
        ActiveWindow.FreezePanes = True
        .Columns("I:C").Group
        
    End With
End Sub

Private Sub ToEXCELBinMap(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet         
Dim iSheet As Integer, i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String

    For iSheet = 0 To 1
        Set xlsheet = xlBook.Sheets(SheetNumber + iSheet)
        xlsheet.Name = IIf(i = 0, "HW", "SW")
        xlsheet.Activate
        xlsheet.Cells.HorizontalAlignment = xlCenter
        
        StartNum = 1                                                ''行
        StartNum1 = 1                                               ''列
        
        With xlsheet
            For i = 0 To UBound(HWBinMapData)
                Temp = IIf(iSheet = 0, Split(HWBinMapData(i), ","), Split(SWBinMapData(i), ","))
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp
                
                If Color = True Then
                    If i > 3 Then
                        For j = 0 To UBound(Temp)
                            If j > 0 Then
                                If Temp(j) <> "" Then
                                    If CheckBinData(2, Temp(j)) = True Then
                                        .Cells(StartNum + i, StartNum + j).Interior.Color = &HFF00&
                                    Else
                                        .Cells(StartNum + i, StartNum + j).Interior.Color = &H8080FF
                                    End If
                                End If
                            End If
                        Next j
                    End If
                End If
                DoEvents
                Erase Temp
            Next i
            .Columns.AutoFit
            .Rows.AutoFit
        End With
    Next iSheet
    
End Sub

Private Sub ToEXCELTestInfo(SheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet         
Dim i As Integer, j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String

    Set xlsheet = xlBook.Sheets(SheetNumber)
    xlsheet.Name = "TestInfo"
    xlsheet.Activate
    xlsheet.Cells.HorizontalAlignment = xlCenter
    
    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列
    
    With xlsheet
        
        For i = 0 To UBound(TestDataInfo)
            Temp = Split(TestDataInfo(i), ",")
            If (UBound(Temp) = -1) Then
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + 0)).Value = ""
            Else
                .Range(.Cells(StartNum + i, StartNum1), .Cells(StartNum + i, StartNum1 + UBound(Temp))).Value = Temp
            End If
            DoEvents
            Erase Temp
        Next i
        .Columns.AutoFit
        .Rows.AutoFit
        .Columns("A:C").HorizontalAlignment = xlGeneral
    End With
    
End Sub

Private Sub ToEXCELEveryItem(SheetNumber As Integer, LastSheetNumber As Integer, xlBook As Object)
Dim xlsheet As EXCEL.Worksheet         
Dim i As Integer, j As Integer
Dim Tmp As String

    For i = 0 To UBound(SelectItem, 2)
        If SelectItem(2, 0) <> "" Then
            If ((Trim$(SelectItem(2, i)) <> "") And (InStr(SelectItem(2, i), ",") <> 0)) Then
                
                Set xlsheet = xlBook.Sheets(SheetNumber + i)
                
                Tmp = Trim$(Mid$(SelectItem(2, i), InStr(SelectItem(2, i), ",") + 1))
                Tmp = Trim$(Mid$(Tmp, InStrRev(Tmp, "-") + 1))
                
                xlsheet.Name = Tmp & "_Chart"
                xlsheet.Activate
                xlsheet.Cells.HorizontalAlignment = xlCenter
                
                Call GenerateChart(xlsheet, i, xlsheet.Name)
            End If
        End If
        
        DoEvents
        
        If UBound(SelectItem, 2) <> 0 Then
            ProgressBar2.Value = CInt((i / UBound(SelectItem, 2)) * 100)
        End If
    Next i
    
    ProgressBar2.Value = 100
    
End Sub

Private Sub GenerateChart(xlsheet As Object, iCount As Integer, SheetName As String)
Dim oChart As EXCEL.Chart
Dim MyCharts As EXCEL.ChartObjects
Dim MyCharts1 As EXCEL.ChartObject

Dim j As Integer, StartNum As Integer, StartNum1 As Integer
Dim Temp() As String, Temp1() As String, Temp2() As String, Temp3() As String, Tmp() As String
Dim TempString1 As String, TempString2 As String, TempString3 As String
Dim Max As Double, Min As Double, ICNum As Integer

    StartNum = 1                                                ''行
    StartNum1 = 1                                               ''列
    
    With xlsheet
    
        Temp = Split(SelectItem(2, iCount), ",")
            
        If UBound(Temp) = 1 Then
            
            TempString1 = ItemListData(2)
            Temp1 = Split(TempString1, ",")
            
            TempString2 = ItemListData(3)
            Temp2 = Split(TempString2, ",")
            
            TempString3 = ItemListData(iCount + 5)
            Temp3 = Split(TempString3, ",")
            
            ICNum = UBound(Temp3)
            
            For j = 8 To UBound(Temp3)
                
                If Trim$(Temp3(j)) <> "" Then
                    If j = 8 Then
                        .Cells(StartNum, StartNum1 + 0).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 0) = "X"
                        .Cells(StartNum, StartNum1 + 0).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 1).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 1) = "Y"
                        .Cells(StartNum, StartNum1 + 1).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 2).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 2) = "Site"
                        .Cells(StartNum, StartNum1 + 2).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 3).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 3) = "HW | SW"
                        .Cells(StartNum, StartNum1 + 3).Interior.ColorIndex = 40
                        .Cells(StartNum, StartNum1 + 4).HorizontalAlignment = xlCenter
                        .Cells(StartNum, StartNum1 + 4) = "Result"
                        .Cells(StartNum, StartNum1 + 4).Interior.ColorIndex = 40
                        
                        .Range("A1:R1").Borders.LineStyle = xlContinuous
                        
                        .Cells(StartNum, StartNum1 + 7) = "MAX = " & Format(Temp3(6), "0.000000")
                        Max = CDbl(Temp3(6))
                        .Cells(StartNum, StartNum1 + 7).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 9) = "MIN = " & Format(Temp3(7), "0.000000")
                        Min = CDbl(Temp3(7))
                        .Cells(StartNum, StartNum1 + 9).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 11) = "AVG = " & Format(Temp3(8), "0.000000")
                        .Cells(StartNum, StartNum1 + 11).HorizontalAlignment = xlLeft
                        .Cells(StartNum, StartNum1 + 13) = "Unit = " & Temp3(5)
                        .Cells(StartNum, StartNum1 + 13).HorizontalAlignment = xlLeft
                    Else
                        Tmp = Split(Temp1(j), "|")
                        .Cells(StartNum + j - 8, StartNum1 + 0) = Tmp(0)                                'X
                        .Cells(StartNum + j - 8, StartNum1 + 1) = Tmp(1)                                'Y
                        .Cells(StartNum + j - 8, StartNum1 + 2) = Tmp(2)                                'Site
                        .Cells(StartNum + j - 8, StartNum1 + 3) = Temp2(j)                              'Bin
                        .Cells(StartNum + j - 8, StartNum1 + 4) = Format(Temp3(j), ".000000")           'Result
                        Erase Tmp
                    End If
                End If
                DoEvents
            Next j
            
            Erase Temp1, Temp2, Temp3
            
        End If
        
        .Columns.AutoFit
        .Rows.AutoFit
        
        Erase Temp
        
        Set MyCharts = .ChartObjects
        Set MyCharts1 = MyCharts.Add(200, 28, 700, 600)  

        Set oChart = MyCharts1.Chart
'        Debug.Print xlsheet.Cells(StartNum + 1, StartNum1 + 5)
        With oChart
            .ChartType = xlXYScatterSmooth
            .SetSourceData xlsheet.Range(xlsheet.Cells(StartNum + 1, StartNum1 + 4), xlsheet.Cells(StartNum + ICNum - 9, StartNum1 + 4)), xlColumns
            .Location xlLocationAsObject, SheetName
        End With

        With oChart

            .Axes(xlValue).Select
            With .Axes(xlValue)
                .MinimumScale = Min
                .MaximumScale = Max
                .MinorUnitIsAuto = True
                .MajorUnitIsAuto = True
                .Crosses = xlAutomatic
                .ReversePlotOrder = False
                .ScaleType = xlLinear
                .DisplayUnit = xlNone
            End With

            .Axes(xlCategory).Select

            With .Axes(xlCategory)    
                .MinimumScale = 0
                .MaximumScale = ICNum
                .MinorUnitIsAuto = True
                .MajorUnitIsAuto = True
                .Crosses = xlAutomatic
                .ReversePlotOrder = False
                .ScaleType = xlLinear
                .DisplayUnit = xlNone
            End With

        End With
        
    End With
    
End Sub

不知道是哪边的问题?

来源档案:

Private Sub ToEXCELBinMap(SheetNumber As Integer, xlBook As Object)
此行之后的代码都是其他EXCEL页面的...

小朋友不要学喔，叔叔有试过，若要产生150张图，速度超级慢，
有考虑要把运算部分九成转给VC去做，VB只负责做CSV转EXCEL。
若VC TO EXCEL能试出来，可能会直接跳过VB了.