Adodc1.connectionstring = ......
Adodc1.RecordSource = "select * from 表1 where ......
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
已经通过上面的语句把Access中"表1"的数据传到"DataGrid1"控件显示.
现在想把"DataGrid1"中选出的数据转到Excel文本中???
Dim i, j
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open("D:\test\工作薄1.xlt") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
For i = 1 To DataGrid1.Columns.count
xlsheet.Cells(1, i) = DataGrid1.Columns(i - 1).Caption
For j = 0 To DataGrid1.VisibleRows - 1
xlsheet.Cells(j + 2, i) = DataGrid1.Columns(i - 1).CellText(DataGrid1.RowBook(j))
Next j
Next i
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏
现在我采用了上面"红色"的代码,实现数据从DataGrid1导到Excel。但是问题是:
只能把DataGrid1框中当前页显示的数据导入到Excel,而未显示的数据(数据很多,右侧滑块下拉才能显示的数据)则不能导入到Excel中。估计和我用的循环界定的“DataGrid1.VisibleRows”有关! 请问各位大侠如何改进,使得DataGrid1中所有的数据可以显示???
谢谢!!!
Adodc1.RecordSource = "select * from 表1 where ......
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
已经通过上面的语句把Access中"表1"的数据传到"DataGrid1"控件显示.
现在想把"DataGrid1"中选出的数据转到Excel文本中???
Dim i, j
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open("D:\test\工作薄1.xlt") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
For i = 1 To DataGrid1.Columns.count
xlsheet.Cells(1, i) = DataGrid1.Columns(i - 1).Caption
For j = 0 To DataGrid1.VisibleRows - 1
xlsheet.Cells(j + 2, i) = DataGrid1.Columns(i - 1).CellText(DataGrid1.RowBook(j))
Next j
Next i
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏
现在我采用了上面"红色"的代码,实现数据从DataGrid1导到Excel。但是问题是:
只能把DataGrid1框中当前页显示的数据导入到Excel,而未显示的数据(数据很多,右侧滑块下拉才能显示的数据)则不能导入到Excel中。估计和我用的循环界定的“DataGrid1.VisibleRows”有关! 请问各位大侠如何改进,使得DataGrid1中所有的数据可以显示???
谢谢!!!
Dim j As Integer
Dim k As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Columns.AutoFit
Me.MousePointer = 11
For k = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, k + 1) = DataGrid1.Columns(k).Caption
Next
If DataGrid1.ApproxCount = 0 Then
MsgBox "没有记录,请使用查询功能"
Me.MousePointer = 0
Exit Sub
Else
DataGrid1.Scroll 0, -DataGrid1.FirstRow
DataGrid1.Row = 0
For i = 0 To DataGrid1.ApproxCount - 1
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
Next
If i < DataGrid1.ApproxCount - 1 Then
DataGrid1.Row = DataGrid1.Row + 1
End If
Next
Me.MousePointer = 0
MsgBox "导出成功!", vbOKOnly + vbInformation, "提示"
xlApp.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End If
呵呵~~~~~