一。新建一个工程,然后引用 microsoft excel 9.0 object library 二。 Dim app As Excel.Application Dim eworkbook As Workbook Dim eworksheet As Worksheet dim one,sec,thr as single Set app = New Excel.Application Set eworkbook = app.Workbooks.Open("c:\a.xls") '打开文件 Set eworksheet = eworkbook.Sheets(1) '激活sheets1,以下操作都是针对sheets1 With eworksheet one=val(trim(.Cells(6,2))) ‘读了第一个数据,修改文件名继续读第二个。 然后相加将数据写入对应表格。如:.cells(6,2)=12 end with
哦,最后别忘记加上 set app=nothing ......释放所有的对象变量。
' '********************************************************** '将 mshflexgrid 表格导出到EXCEL '函数:ToExcel '参数:ClipRow :每次传送的块大小(以行为单位,默认值 100 ) '返回值:T 成功,F 失败 '********************************************************** Public Function ToExcel(Optional ExcFileName As String = "", Optional ClipRow As Long = 100) As Boolean Dim K As Long, I As Long Dim TmpStr As String Dim PutStr As String Dim RowMax As Long Dim ColMax As Long Dim xlApp As Object Dim xlBook As Object Dim xlSheet As Object Dim AddFlag As Boolean
Dim RowID As Integer Dim ColID As Integer
On Error Resume Next
Set xlApp = CreateObject("Excel.Application") Err.Clear
If xlApp Is Nothing Then '//系统没有安装EXCEL ToExcel = False Else
With Ev_GridObj Set xlApp = Nothing Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.workbooks.Add Set xlSheet = xlBook.Worksheets(1)
RowMax = .Rows - 1 ColMax = .Cols - 1
For I = 0 To ColMax Call xlApp.Range(xlApp.Cells(1, I + 1), xlApp.Cells(RowMax + 2, I + 1)).Select With xlApp.Selection.Font .Name = Me.Font.Name .Size = Me.Font.Size End With xlApp.Selection.NumberFormatLocal = "@" xlSheet.Columns(I + 1).ColumnWidth = 15 Next Call xlApp.Cells(1, 1).Select
K = 1: PutStr = "": AddFlag = True For RowID = 0 To RowMax' For ColID = 0 To ColMax ' xlApp.CELLS(RowID + 1, ColID + 1) = .TextMatrix(RowID, ColID) ' Next For ColID = 0 To ColMax If ColID = 0 Then TmpStr = .TextMatrix(RowID, ColID) Else TmpStr = TmpStr & vbTab & .TextMatrix(RowID, ColID) End If Next If RowID Mod ClipRow = 0 And RowID > 0 Then PutStr = PutStr & TmpStr & vbCr Clipboard.Clear Clipboard.SetText PutStr Call xlApp.Cells(K, 1).Select Call xlBook.ActiveSheet.Paste K = RowID + 2 PutStr = "" AddFlag = True Else PutStr = PutStr & TmpStr & vbCr End If Next
'======================================================================= If Len(PutStr) > 0 Then PutStr = Left(PutStr, Len(PutStr) - 1) Clipboard.Clear Clipboard.SetText PutStr Call xlApp.Cells(K, 1).Select Call xlBook.ActiveSheet.Paste End If
Call xlApp.Cells(1, 1).Select If Len(ExcFileName) > 0 Then xlBook.SaveAs FileName:=ExcFileName End If '//======================================== Set xlSheet = Nothing DoEvents xlBook.Close Set xlBook = Nothing DoEvents Set xlApp = Nothing DoEvents
End With
Set xlSheet = Nothing If Not xlBook Is Nothing Then xlBook.Close Set xlBook = Nothing Set xlApp = Nothing
ToExcel = (Err.Number = 0)
End IfEnd Function
'先引用Microsoft Excel9.0对象库 Private Sub Command1_Click() Dim xlsapp As New Excel.Application Dim xlsbook1 As Excel.Workbook Dim xlssheet1 As Excel.WorksheetDim xlsbook2 As Excel.Workbook Dim xlssheet2 As Excel.WorksheetDim xlsbook3 As Excel.Workbook Dim xlssheet3 As Excel.WorksheetSet xlsbook1 = xlsapp.Workbooks.Open(App.Path & "\111.xls") Set xlssheet1 = xlsbook1.Worksheets("sheet1")Set xlsbook2 = xlsapp.Workbooks.Open(App.Path & "\222.xls") Set xlssheet2 = xlsbook2.Worksheets("sheet1")Set xlsbook3 = xlsapp.Workbooks.Open(App.Path & "\333.xls") Set xlssheet3 = xlsbook3.Worksheets("sheet1")x = xlssheet1.Cells(6, 2) 'B6 y = xlssheet2.Cells(6, 2) 'B6 xlssheet3.Cells(6, 2) = x + y 'B6xlsapp.Quit Set xlssheet1 = Nothing Set xlssheet2 = Nothing Set xlssheet3 = Nothing Set xlsbook1 = Nothing Set xlsbook2 = Nothing Set xlsbook3 = Nothing Set xlsapp = Nothing End Sub
jxgzay(jxgzay): 按照你的方法, 运行到x = xlssheet1.Cells(6, 2) 'B6 这一行时报错:变量无法定义! Microsoft Excel9.0对象库已经引用了,请问各位大哥:这是怎么回事?
二。
Dim app As Excel.Application
Dim eworkbook As Workbook
Dim eworksheet As Worksheet
dim one,sec,thr as single
Set app = New Excel.Application
Set eworkbook = app.Workbooks.Open("c:\a.xls") '打开文件
Set eworksheet = eworkbook.Sheets(1) '激活sheets1,以下操作都是针对sheets1
With eworksheet
one=val(trim(.Cells(6,2))) ‘读了第一个数据,修改文件名继续读第二个。
然后相加将数据写入对应表格。如:.cells(6,2)=12
end with
set app=nothing
......释放所有的对象变量。
'**********************************************************
'将 mshflexgrid 表格导出到EXCEL
'函数:ToExcel
'参数:ClipRow :每次传送的块大小(以行为单位,默认值 100 )
'返回值:T 成功,F 失败
'**********************************************************
Public Function ToExcel(Optional ExcFileName As String = "", Optional ClipRow As Long = 100) As Boolean
Dim K As Long, I As Long
Dim TmpStr As String
Dim PutStr As String
Dim RowMax As Long
Dim ColMax As Long
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim AddFlag As Boolean
Dim RowID As Integer
Dim ColID As Integer
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Err.Clear
If xlApp Is Nothing Then '//系统没有安装EXCEL
ToExcel = False
Else
With Ev_GridObj Set xlApp = Nothing
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
RowMax = .Rows - 1
ColMax = .Cols - 1
For I = 0 To ColMax
Call xlApp.Range(xlApp.Cells(1, I + 1), xlApp.Cells(RowMax + 2, I + 1)).Select
With xlApp.Selection.Font
.Name = Me.Font.Name
.Size = Me.Font.Size
End With
xlApp.Selection.NumberFormatLocal = "@"
xlSheet.Columns(I + 1).ColumnWidth = 15
Next
Call xlApp.Cells(1, 1).Select
K = 1: PutStr = "": AddFlag = True For RowID = 0 To RowMax' For ColID = 0 To ColMax
' xlApp.CELLS(RowID + 1, ColID + 1) = .TextMatrix(RowID, ColID)
' Next For ColID = 0 To ColMax
If ColID = 0 Then
TmpStr = .TextMatrix(RowID, ColID)
Else
TmpStr = TmpStr & vbTab & .TextMatrix(RowID, ColID)
End If
Next If RowID Mod ClipRow = 0 And RowID > 0 Then
PutStr = PutStr & TmpStr & vbCr
Clipboard.Clear
Clipboard.SetText PutStr
Call xlApp.Cells(K, 1).Select
Call xlBook.ActiveSheet.Paste
K = RowID + 2
PutStr = ""
AddFlag = True
Else
PutStr = PutStr & TmpStr & vbCr
End If Next
'======================================================================= If Len(PutStr) > 0 Then
PutStr = Left(PutStr, Len(PutStr) - 1)
Clipboard.Clear
Clipboard.SetText PutStr
Call xlApp.Cells(K, 1).Select
Call xlBook.ActiveSheet.Paste
End If
Call xlApp.Cells(1, 1).Select
If Len(ExcFileName) > 0 Then
xlBook.SaveAs FileName:=ExcFileName
End If
'//========================================
Set xlSheet = Nothing
DoEvents
xlBook.Close
Set xlBook = Nothing
DoEvents
Set xlApp = Nothing
DoEvents
End With
Set xlSheet = Nothing
If Not xlBook Is Nothing Then xlBook.Close
Set xlBook = Nothing
Set xlApp = Nothing
ToExcel = (Err.Number = 0)
End IfEnd Function
Private Sub Command1_Click()
Dim xlsapp As New Excel.Application
Dim xlsbook1 As Excel.Workbook
Dim xlssheet1 As Excel.WorksheetDim xlsbook2 As Excel.Workbook
Dim xlssheet2 As Excel.WorksheetDim xlsbook3 As Excel.Workbook
Dim xlssheet3 As Excel.WorksheetSet xlsbook1 = xlsapp.Workbooks.Open(App.Path & "\111.xls")
Set xlssheet1 = xlsbook1.Worksheets("sheet1")Set xlsbook2 = xlsapp.Workbooks.Open(App.Path & "\222.xls")
Set xlssheet2 = xlsbook2.Worksheets("sheet1")Set xlsbook3 = xlsapp.Workbooks.Open(App.Path & "\333.xls")
Set xlssheet3 = xlsbook3.Worksheets("sheet1")x = xlssheet1.Cells(6, 2) 'B6
y = xlssheet2.Cells(6, 2) 'B6
xlssheet3.Cells(6, 2) = x + y 'B6xlsapp.Quit
Set xlssheet1 = Nothing
Set xlssheet2 = Nothing
Set xlssheet3 = Nothing
Set xlsbook1 = Nothing
Set xlsbook2 = Nothing
Set xlsbook3 = Nothing
Set xlsapp = Nothing
End Sub
按照你的方法,
运行到x = xlssheet1.Cells(6, 2) 'B6
这一行时报错:变量无法定义!
Microsoft Excel9.0对象库已经引用了,请问各位大哥:这是怎么回事?