将ACESS表中的数据，用一条语句导入EXCEL中，如何实现？

[皮皮堵儿]

我记得应该实现，但我现在用的OFFICE没有完全安装，所以通过宏记录导入外部数据的过程就实现不了！所以就跑到这里来问各位大侠，这条语句该这么写？？！！

[acptvb]

感谢您使用微软产品。

您可以使用Excel Object Library中QueryTables的add方法，把从Access查询到的数据作为一个整体添加到excel的区域中，保存为Excel文件。如下例：

Private Sub Command1_Click()

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

End Sub

详情请参阅：
Range Collection
http://msdn.microsoft.com/library/en-us/vbaxl10/html/xlobjRange.asp

Excel的VBA相关信息可供参阅：
QueryTables Collection Object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlobjQueryTables.asp

在以下链接点击Example，选择As it applies to the QueryTables object，能够获得相关样例程序。
Add Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlmthadd.asp


关于把数据导入EXCEL还可以参考下面链接中的方法中的例子。
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic http://support.microsoft.com/support/kb/articles/q247/4/12.asp

Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO http://support.microsoft.com/support/kb/articles/q295/6/46.asp


- 微软全球技术中心 VB技术支持

本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利。具体事项可参见使用条款
(http://support.microsoft.com/directory/worldwide/zh-cn/community/terms_chs.asp)。
为了为您创建更好的讨论环境，请参加我们的用户满意度调查
(http://support.microsoft.com/directory/worldwide/zh-cn/community/survey.asp?key=(S,49854782))。
======================

[acptvb]

感谢您使用微软产品。

您可以使用Excel Object Library中QueryTables的add方法，把从Access查询到的数据作为一个整体添加到excel的区域中，保存为Excel文件。如下例：

Private Sub Command1_Click()

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

End Sub

详情请参阅：
Range Collection
http://msdn.microsoft.com/library/en-us/vbaxl10/html/xlobjRange.asp

Excel的VBA相关信息可供参阅：
QueryTables Collection Object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlobjQueryTables.asp

在以下链接点击Example，选择As it applies to the QueryTables object，能够获得相关样例程序。
Add Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlmthadd.asp


关于把数据导入EXCEL还可以参考下面链接中的方法中的例子。
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic http://support.microsoft.com/support/kb/articles/q247/4/12.asp

Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO http://support.microsoft.com/support/kb/articles/q295/6/46.asp


- 微软全球技术中心 VB技术支持

本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利。具体事项可参见使用条款
(http://support.microsoft.com/directory/worldwide/zh-cn/community/terms_chs.asp)。
为了为您创建更好的讨论环境，请参加我们的用户满意度调查
(http://support.microsoft.com/directory/worldwide/zh-cn/community/survey.asp?key=(S,49854782))。
======================

[playyuer]

HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q295646

HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246335

[cnwd]

如果你那个没有完全安装的OFFICE有 EXCEL.EXE 的话：


Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Private Sub Command1_Click()

Dim dbs As Database
Set dbs = OpenDatabase(App.Path & "\db1.mdb")
If Dir(App.Path & "\MyExcel.xls") <> "" Then Kill App.Path & "\MyExcel.xls"
dbs.Execute "SELECT * INTO [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] FROM 在校学生"
dbs.Close
Set dbs = Nothing
Shell "C:\Program Files\Microsoft Office\Office\EXCEL.EXE " & App.Path & "\MyExcel.xls", vbMaximizedFocus

End Sub