嗨，我有一个计算查询表名称DataQuery，例如

日期/ RJournal / AMount

其中Rjournal是计算字段

Rjournal：DLookUp（“ REFjournal”，“ DV”，“ ChckID> 0并且Payee ='”＆[Payee]＆“'和Dvnumber =”＆[Dvnumber]＆“”）

它工作正常。

但是由于MS access是我的数据库，而Excel是我的前端，并且我的大多数用户都是excel用户。我创建了一个导出按钮，以使用excel中的ADO将此查询导出到excel。出于某种原因，Field RJournal不会捕获其数据，只是将其留空

但是，如果我使用访问菜单“外部数据”，则将显示“导出到Excel”中的所有数据。

我想知道ADO是否支持导出计算表查询。

Private Sub Export_Click()

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String

'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
Sheets("Data").Range("A2:C500000").ClearContents

'get the path to the database
dbPath = Sheets("Update Version").Range("b1").Value

Set cnn = New ADODB.Connection ' Initialise the collection class variable

'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString,UserID,Password,Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

SQL = "SELECT * FROM DATAQUERY"

'Create the ADODB recordset object.

Set rs = New ADODB.Recordset 'assign memory to the recordset

'ConnectionString Open '—-5 aguments—-
'Source,activeConnection,CursorType,LockType,Options
rs.Open SQL,cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!",vbCritical,"No Records"

Exit Sub
End If


'Write the reocrdset values in the sheet.
Sheets("DATA").Range("A2").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing

'Enable the screen.
Application.ScreenUpdating = True



'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported",vbInformation,"Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

END SUB

我希望这样的结果

日期/ RJournal / AMount 01 / CRJ / 1000 02 / CDJ / 1000 03 / CRJ / 1000 04 / CRJ / 1500

像这样结束

日期/ RJournal / AMount 01 / / 1000 02 / / 1000 03 / / 1000 04 / / 1500