Dim XLApp As New Excel.Application Dim XLWorkBook As New Excel.Workbook Dim XLWorkSheet As New Excel.Worksheet Set XLApp = CreateObject("Excel.Application") Set XLWorkBook = XLApp.Workbooks.Add Set XLWorkSheet = XLWorkBook.Worksheets(1)XLApp.Visible = TrueFor j = 0 To MSFlexGrid1.Cols - 1 If j = 0 Or j = MSFlexGrid1.Cols - 1 Then MSFlexGrid1.Col = j For i = 0 To MSFlexGrid1.Rows - 1 MSFlexGrid1.Row = i If i = 0 Or j = 0 Then XLWorkSheet.Cells(i + 1, j + 1).NumberFormatLocal = "@" XLWorkSheet.Cells(i + 1, j + 1).Value = MSFlexGrid1.Text Else XLWorkSheet.Cells(i + 1, j + 1).Value = Format(Val(MSFlexGrid1.Text), "#0.00") End If Next i End If Next j XLWorkBook.Charts.Add XLWorkBook.ActiveChart.ChartType = xlColumnClustered XLWorkBook.ActiveChart.Legend.Clear XLWorkBook.ActiveChart.SetSourceData Source:=XLWorkSheet.Range("Q2:Q16"), PlotBy:=xlColumns XLWorkBook.ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R16C1" XLWorkBook.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" XLWorkBook.ActiveSheet.Shapes("图表 1").IncrementLeft -205.5 XLWorkBook.ActiveSheet.Shapes("图表 1").IncrementTop -125.75 XLWorkBook.ActiveSheet.Shapes("图表 1").ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft XLWorkBook.ActiveSheet.Shapes("图表 1").ScaleHeight 1.97, msoFalse, msoScaleFromTopLeft XLWorkBook.ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
Dim a() As String Dim i As Integer Dim j As IntegerReDim a(10, 10)For i = 1 To 10 For j = 1 To 10 a(i, j) = i Next j Next iSet VBExcel = CreateObject("Excel.Application") Set VBExcelWorkbook = VBExcel.Workbooks.AddWith VBExcelWorkbook.Application .Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = Format(Val(a), "#0.00")
End With VBExcelWorkbook.SaveAs ("e:\123.xls") VBExcelWorkbook.Saved = True VBExcelWorkbook.Close
VBExcel.Quit Set VBExcel = Nothing Set VBExcelWorkbook = NothingEnd Sub 这边提示类型不正确,我想问,如果 Format(Val(a), "#0.00") 这个地方是二维数组,该怎么做啊?急~!
用 i=0 to 数组下标 Format(Val(a(i)), "#0.00") next i 一个一个地格式化之即可
两维数组用双重循环 比如你上面的代码在赋值的时候就可以先格式化:For i = 1 To 10 For j = 1 To 10 a(i, j) = Format(Val(i), "#0.00") Next j Next i
数组格式化之后,该怎么赋值到Excel表啊~! 还是能用 .Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = a 这句话啊?
这句话就是赋值 另外你用从"引用"里面把Excel引用一下
那我 For i = 1 To 10 For j = 1 To 10 a(i, j) = Format(Val(i), "#0.00") Next j Next i 这么做了之后,怎么把这个二维数组,保存到Excel表中,并且能够达到我的要求啊?Excel单元格没有绿色小三角
格式化一下單元格,讓其為數值列,可以在Excel錄制一個宏,看看宏代碼就知道如何做了。
EXCEL里面也是一块面积,所以也是二维的,也要用双重循环 For i = 1 To 10 For j = 1 To 10 a(i, j) = Format(Val(i), "#0.00") .Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = a(i,j) Next j Next i
Dim XLWorkBook As New Excel.Workbook
Dim XLWorkSheet As New Excel.Worksheet
Set XLApp = CreateObject("Excel.Application")
Set XLWorkBook = XLApp.Workbooks.Add
Set XLWorkSheet = XLWorkBook.Worksheets(1)XLApp.Visible = TrueFor j = 0 To MSFlexGrid1.Cols - 1
If j = 0 Or j = MSFlexGrid1.Cols - 1 Then
MSFlexGrid1.Col = j
For i = 0 To MSFlexGrid1.Rows - 1
MSFlexGrid1.Row = i
If i = 0 Or j = 0 Then
XLWorkSheet.Cells(i + 1, j + 1).NumberFormatLocal = "@"
XLWorkSheet.Cells(i + 1, j + 1).Value = MSFlexGrid1.Text
Else
XLWorkSheet.Cells(i + 1, j + 1).Value = Format(Val(MSFlexGrid1.Text), "#0.00")
End If
Next i
End If
Next j
XLWorkBook.Charts.Add
XLWorkBook.ActiveChart.ChartType = xlColumnClustered
XLWorkBook.ActiveChart.Legend.Clear
XLWorkBook.ActiveChart.SetSourceData Source:=XLWorkSheet.Range("Q2:Q16"), PlotBy:=xlColumns
XLWorkBook.ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R16C1"
XLWorkBook.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
XLWorkBook.ActiveSheet.Shapes("图表 1").IncrementLeft -205.5
XLWorkBook.ActiveSheet.Shapes("图表 1").IncrementTop -125.75
XLWorkBook.ActiveSheet.Shapes("图表 1").ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
XLWorkBook.ActiveSheet.Shapes("图表 1").ScaleHeight 1.97, msoFalse, msoScaleFromTopLeft
XLWorkBook.ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
我重新说一遍:
保存的Excel文件已经保存出来了
文件里面的内容是数据
用户想通过这些数据画个图,但是画不出来
Excel的单元格上有个绿色的三角
如何使得保存的格式没有变,内容也没有变,然后保存出的文件中,没有这个绿色小三角了~!
大概是.NumberFormatLocal = "@" 这样写
那么转化成文本型,应该写什么的?
.NumberFormatLocal = "@" 转化成文本型
上面的瞎猜测
Dim i As Integer
Dim j As IntegerReDim a(10, 10)For i = 1 To 10
For j = 1 To 10
a(i, j) = i
Next j
Next iSet VBExcel = CreateObject("Excel.Application")
Set VBExcelWorkbook = VBExcel.Workbooks.AddWith VBExcelWorkbook.Application
.Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = Format(Val(a), "#0.00")
End With VBExcelWorkbook.SaveAs ("e:\123.xls")
VBExcelWorkbook.Saved = True
VBExcelWorkbook.Close
VBExcel.Quit
Set VBExcel = Nothing
Set VBExcelWorkbook = NothingEnd Sub
这边提示类型不正确,我想问,如果 Format(Val(a), "#0.00") 这个地方是二维数组,该怎么做啊?急~!
i=0 to 数组下标
Format(Val(a(i)), "#0.00")
next i
一个一个地格式化之即可
比如你上面的代码在赋值的时候就可以先格式化:For i = 1 To 10
For j = 1 To 10
a(i, j) = Format(Val(i), "#0.00")
Next j
Next i
.Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = a '给区域赋值
.Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).NumberFormatLocal = "#0.00" '设置区域数字格式
数组格式化之后,该怎么赋值到Excel表啊~!
还是能用 .Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = a
这句话啊?
另外你用从"引用"里面把Excel引用一下
那我 For i = 1 To 10
For j = 1 To 10
a(i, j) = Format(Val(i), "#0.00")
Next j
Next i
这么做了之后,怎么把这个二维数组,保存到Excel表中,并且能够达到我的要求啊?Excel单元格没有绿色小三角
For i = 1 To 10
For j = 1 To 10
a(i, j) = Format(Val(i), "#0.00")
.Range(.Worksheets(1).Cells(1, 1), .Worksheets(1).Cells(11, 11)).Value = a(i,j)
Next j
Next i
是:
.Range(.Worksheets(1).Cells(i, j), .Worksheets(1).Cells(11, 11)).Value = a(i,j)
如果要转置就用
.Range(.Worksheets(1).Cells(j, i), .Worksheets(1).Cells(11, 11)).Value = a(i,j)
是:
.Range(.Worksheets(1).Cells(i, j).Value = a(i,j)
如果要转置就用
.Range(.Worksheets(1).Cells(j, i).Value = a(i,j)