vb实现excel 问题：如何把一行数据copy到指定行

[bdgyz]

rt，期待

[tripofdream]

关注

[tripofdream]

这是我最近写的，用来把数据导入excel并在excel中生成图表的
Dim intCols As Integer
Dim xlapp As New Excel.Application
Dim xlbook As New Excel.Workbook
Dim xlbooktmp As New Excel.Workbook
Dim xlchart As New Excel.Chart
Dim xlsheet As New Excel.Worksheet
Dim strDestination As String
Dim strSource As String
On Error Resume Next
CommonDialog1.ShowSave '设置保存文件路径
strDestination = CommonDialog1.FileName
CommonDialog1.FileName = ""
If strDestination = "" Then Exit Sub

'===========================================
strSource = App.Path & "\{--临时模板--}.xls"
'源文件，即临时模板
'因为直接用saveas方法在文件已经存在的情况下会出错
'所以使用一个临时模板，此模板的文件名应该保证不会重名
'===========================================

Set xlbooktmp = xlapp.Workbooks.Add '打开一个workbook
xlbooktmp.SaveAs strSource '保存作为临时模板
xlbooktmp.Close '源文件打开时复制会出错，因此先关闭
FileCopy strSource, strDestination '复制
Kill strSource '删除临时模板
xlapp.Visible = False '为False时Excel在后台运行
Set xlbook = xlapp.Workbooks.Open(strDestination) '打开工作簿
Set xlsheet = xlbook.Worksheets(1) '选定活页簿
If rsStat.RecordCount < 1 Then '无记录退出
MsgBox "没有记录!"
xlapp.Quit
Exit Sub
Else '往活页簿写数据
rsStat.MoveFirst
For intCols = 1 To rsStat.RecordCount
xlsheet.Cells(1, intCols).Value = rsStat.Fields(0)
xlsheet.Cells(2, intCols).Value = rsStat.Fields(1)
rsStat.MoveNext
Next

Charts.Add , xlsheet '插入图表，位置在xlsheet之后
ActiveChart.ChartType = xlColumnClustered '设置图表类型
ActiveChart.SetSourceData Source:=xlsheet.Range(xlsheet.Cells(1, 1), _
xlsheet.Cells(2, rsStat.RecordCount)), PlotBy _
:=xlRows '图表数据来源
ActiveChart.Location Where:=xlLocationAsNewSheet

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = strTitle '图表标题
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXTitle 'X轴标题由frmStat传回
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYTitle 'Y轴标题由frmStat传回
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False

End If
xlbook.Save
xlapp.Quit
xlapp.Visible
MsgBox "文件保存在:" & strDestination, vbOKOnly, "导出成功!"
End Sub

[tripofdream]

这是我最近写的，用来把数据导入excel并在excel中生成图表的
Dim intCols As Integer
Dim xlapp As New Excel.Application
Dim xlbook As New Excel.Workbook
Dim xlbooktmp As New Excel.Workbook
Dim xlchart As New Excel.Chart
Dim xlsheet As New Excel.Worksheet
Dim strDestination As String
Dim strSource As String
On Error Resume Next
CommonDialog1.ShowSave '设置保存文件路径
strDestination = CommonDialog1.FileName
CommonDialog1.FileName = ""
If strDestination = "" Then Exit Sub

'===========================================
strSource = App.Path & "\{--临时模板--}.xls"
'源文件，即临时模板
'因为直接用saveas方法在文件已经存在的情况下会出错
'所以使用一个临时模板，此模板的文件名应该保证不会重名
'===========================================

Set xlbooktmp = xlapp.Workbooks.Add '打开一个workbook
xlbooktmp.SaveAs strSource '保存作为临时模板
xlbooktmp.Close '源文件打开时复制会出错，因此先关闭
FileCopy strSource, strDestination '复制
Kill strSource '删除临时模板
xlapp.Visible = False '为False时Excel在后台运行
Set xlbook = xlapp.Workbooks.Open(strDestination) '打开工作簿
Set xlsheet = xlbook.Worksheets(1) '选定活页簿
If rsStat.RecordCount < 1 Then '无记录退出
MsgBox "没有记录!"
xlapp.Quit
Exit Sub
Else '往活页簿写数据
rsStat.MoveFirst
For intCols = 1 To rsStat.RecordCount
xlsheet.Cells(1, intCols).Value = rsStat.Fields(0)
xlsheet.Cells(2, intCols).Value = rsStat.Fields(1)
rsStat.MoveNext
Next

Charts.Add , xlsheet '插入图表，位置在xlsheet之后
ActiveChart.ChartType = xlColumnClustered '设置图表类型
ActiveChart.SetSourceData Source:=xlsheet.Range(xlsheet.Cells(1, 1), _
xlsheet.Cells(2, rsStat.RecordCount)), PlotBy _
:=xlRows '图表数据来源
ActiveChart.Location Where:=xlLocationAsNewSheet

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = strTitle '图表标题
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXTitle 'X轴标题由frmStat传回
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYTitle 'Y轴标题由frmStat传回
End With
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False

End If
xlbook.Save
xlapp.Quit
xlapp.Visible
MsgBox "文件保存在:" & strDestination, vbOKOnly, "导出成功!"