连Excel和连ACCESS的方法差不多,我现在用的是ADO的方法,你可以参考一下。 Dim exconn as ADODB.Connection Dim exconn as ADODB.Recordset Set exConn = New ADODB.Connection Set exRs = New ADODB.Recordset
exConn.Open "Driver={Microsoft Excel Driver (*.xls)};UID=;PWD=;DBQ=" & exPath exRs.AddNew For i = 0 To ListFieldPrint.ListCount - 1 If ListFieldPrint.List(i) = "Ê¡±àºÅ" Then '''»ñµÃÊ¡Ãû
Dim Prs As ADODB.Recordset Dim Psql As String Set Prs = New ADODB.Recordset Psql = "select * from Province where Ê¡±àºÅ='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'" Prs.Open Psql, conn, 1, 1
If Not (Prs.BOF And Prs.EOF) Then exRs.Fields(i).Value = Prs.Fields("Ê¡Ãû").Value Else exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value End If ElseIf ListFieldPrint.List(i) = "ÊбàºÅ" Then '''»ñµÃ³ÇÊÐÃû
Dim Crs As ADODB.Recordset Dim Csql As String Set Crs = New ADODB.Recordset Csql = "select * from City where ÊбàºÅ='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'" Crs.Open Csql, conn, 1, 1
If Not (Crs.BOF And Crs.EOF) Then exRs.Fields(i).Value = Crs.Fields("ÊÐÃû").Value Else exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value End If
ElseIf ListFieldPrint.List(i) = "µ¥Î»±àºÅ" Then '''»ñµÃµ¥Î»Ãû
Dim Srs As ADODB.Recordset Dim Ssql As String Set Srs = New ADODB.Recordset Ssql = "select * from School where µ¥Î»´úÂë='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'" Srs.Open Ssql, conn, 1, 1
If Not (Srs.BOF And Srs.EOF) Then exRs.Fields(i).Value = Srs.Fields("µ¥Î»").Value Else exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value End If
Else exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value End If
Next exRs.Update其中,conn及rs的数据源连接你自己写好了。
当然你得先引用一下Excel9.olb (Mirosoft Excel 9.0 Object Library) Public Sub ProCopyMSFlexToExcel(MhFlex As MSHFlexGrid, SheetName As String) Dim appExcel As Excel.Application '通用EXCEL对象 Dim wbExcel As Excel.Workbook '指定EXCEL对象 Dim TempSheet As Excel.Worksheet '工作单对象 Dim TempRange As Excel.Range '限制行 Dim LongRow As Long, LongCol As Long '循环变量 Set appExcel = CreateObject("excel.application") Set wbExcel = appExcel.Workbooks.Open("d:\tj.xls") '打开文件 Set TempSheet = appExcel.Worksheets(SheetName) TempSheet.Cells.Clear '清空现有数据 LongRow = 0 Set TempRange = TempSheet.Rows(LongRow + 1) Do While LongRow < MsFlex.Rows LongCol = 0 Do While LongCol < MsFlex.Cols TempRange.Cells(LongRow + 1, LongCol + 1) = CStr(MsFlex.TextMatrix(LongRow, LongCol)) LongCol = LongCol + 1 Loop LongRow = LongRow + 1 Loop' With TempRange.Range(.Cells(1, 1), .Cells(EndRow, EndCol)) ' ' With .Borders ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' .Borders(xlInsideVertical).Weight = xlHairline ' .Borders(xlInsideHorizontal).Weight = xlHairline ' .EntireColumn.AutoFit ' End With Set TempSheet = Nothing '关闭对象 wbExcel.Save wbExcel.Close Set wbExcel = Nothing Set appExcel = NothingEnd Sub
没有sheet你要把什么写入到另外一个excel文件中去呢?
你没说清楚问题吧!
如果写内容的话、读出来源文件数据再写到新文件你想要的位置就行了。
声明 elxApp
elxbook
elxsheetelxsheet.cell()=""
Dim exconn as ADODB.Connection
Dim exconn as ADODB.Recordset
Set exConn = New ADODB.Connection
Set exRs = New ADODB.Recordset
exConn.Open "Driver={Microsoft Excel Driver (*.xls)};UID=;PWD=;DBQ=" & exPath exRs.AddNew
For i = 0 To ListFieldPrint.ListCount - 1
If ListFieldPrint.List(i) = "Ê¡±àºÅ" Then '''»ñµÃÊ¡Ãû
Dim Prs As ADODB.Recordset
Dim Psql As String
Set Prs = New ADODB.Recordset
Psql = "select * from Province where Ê¡±àºÅ='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'"
Prs.Open Psql, conn, 1, 1
If Not (Prs.BOF And Prs.EOF) Then
exRs.Fields(i).Value = Prs.Fields("Ê¡Ãû").Value
Else
exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value
End If
ElseIf ListFieldPrint.List(i) = "ÊбàºÅ" Then '''»ñµÃ³ÇÊÐÃû
Dim Crs As ADODB.Recordset
Dim Csql As String
Set Crs = New ADODB.Recordset
Csql = "select * from City where ÊбàºÅ='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'"
Crs.Open Csql, conn, 1, 1
If Not (Crs.BOF And Crs.EOF) Then
exRs.Fields(i).Value = Crs.Fields("ÊÐÃû").Value
Else
exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value
End If
ElseIf ListFieldPrint.List(i) = "µ¥Î»±àºÅ" Then '''»ñµÃµ¥Î»Ãû
Dim Srs As ADODB.Recordset
Dim Ssql As String
Set Srs = New ADODB.Recordset
Ssql = "select * from School where µ¥Î»´úÂë='" & rs.Fields("" & ListFieldPrint.List(i) & "").Value & "'"
Srs.Open Ssql, conn, 1, 1
If Not (Srs.BOF And Srs.EOF) Then
exRs.Fields(i).Value = Srs.Fields("µ¥Î»").Value
Else
exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value
End If
Else
exRs.Fields(i).Value = rs.Fields("" & ListFieldPrint.List(i) & "").Value
End If
Next
exRs.Update其中,conn及rs的数据源连接你自己写好了。
Public Sub ProCopyMSFlexToExcel(MhFlex As MSHFlexGrid, SheetName As String) Dim appExcel As Excel.Application '通用EXCEL对象
Dim wbExcel As Excel.Workbook '指定EXCEL对象
Dim TempSheet As Excel.Worksheet '工作单对象
Dim TempRange As Excel.Range '限制行 Dim LongRow As Long, LongCol As Long '循环变量 Set appExcel = CreateObject("excel.application")
Set wbExcel = appExcel.Workbooks.Open("d:\tj.xls") '打开文件 Set TempSheet = appExcel.Worksheets(SheetName) TempSheet.Cells.Clear '清空现有数据
LongRow = 0 Set TempRange = TempSheet.Rows(LongRow + 1) Do While LongRow < MsFlex.Rows LongCol = 0 Do While LongCol < MsFlex.Cols TempRange.Cells(LongRow + 1, LongCol + 1) = CStr(MsFlex.TextMatrix(LongRow, LongCol))
LongCol = LongCol + 1 Loop LongRow = LongRow + 1 Loop' With TempRange.Range(.Cells(1, 1), .Cells(EndRow, EndCol))
'
' With .Borders
' .LineStyle = xlContinuous
' .Weight = xlThin
' .ColorIndex = xlAutomatic
' End With
' .Borders(xlInsideVertical).Weight = xlHairline
' .Borders(xlInsideHorizontal).Weight = xlHairline
' .EntireColumn.AutoFit
' End With Set TempSheet = Nothing '关闭对象
wbExcel.Save
wbExcel.Close
Set wbExcel = Nothing
Set appExcel = NothingEnd Sub