You could use code to transfer a recordset (see below - nice code, not mine)
or you could pull the data from the excel side with MS Query.
'put this code in a form event
Private Sub button_Click()
Dim sql as String
Dim rs as ADODB.Recordset
'change the query to select from your database
sql = "SELECT Drivers.* FROM Drivers WHERE Drivers.DriverId 0;"
set rs = KeySet_Rs(sql)
ExportExcel "c:\excelfiles", "filename.xls", rs
rs.close
set rs = nothing
End Sub
'************************************************* *****************
'put this code below in a module
'If your using only access use CurrentProject.Connection as the connection
string
Public Function Connect() As String
Connect = Application.CurrentProject.Connection
End Function
' Returns Disconnected Keyset Recordset
Public Function KeySet_Rs(sql As String) As ADODB.recordset
Dim rs As ADODB.recordset
Set rs = New ADODB.recordset
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset
rs.Open sql, Connect()
rs.ActiveConnection = Nothing
Set KeySet_Rs = rs
End Function
'Exports a recordset to Excel
Public Sub ExportExcel(path As String, filename As String, rs As
ADODB.recordset)
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False 'Set to true if you want to see it
objXL.Workbooks.Add 'creates new empty worksheet in excel
Dim row As Long, column As Long
column = 1
row = 1
With objXL.Application
Do While Not (rs.EOF Or rs.BOF)
If row = 1 Then
For column = 0 To rs.Fields.count - 1 'Get Header Row with
Field Names
.ActiveSheet.cells(row, column + 1).value =
rs.Fields(column).Name
.ActiveSheet.cells(row, column + 1).Borders.Weight =
xlMedium
.ActiveSheet.cells(row, column + 1).Interior.ColorIndex
= 15
.ActiveSheet.cells(row, column + 1).Interior.Pattern = 1
.ActiveSheet.cells(row, column + 1).Interior.PatternColorIndex =
xlAutomatic
Next
row = row + 1
End If
For column = 0 To rs.Fields.count - 1 'Loop through
recordset and insert all the records into excel
Dim str As String
str = IIf(IsNull(rs.Fields(column)), "", rs.Fields(column))
If Len(str) 0 Then
If IsDate(str) Then
.ActiveSheet.cells(row, column + 1).NumberFormat =
"mm/dd/yyyy hh:mm:ss AM/PM"
End If
End If
.ActiveSheet.cells(row, column + 1).value =
rs.Fields(column)
.ActiveSheet.cells(row, column + 1).Borders.Weight = xlThin
Next
row = row + 1
rs.MoveNext
Loop
.ActiveSheet.Columns("A:ZZ").AutoFit
End With
If Not PathExists(path) Then
MkDir path
End If
objXL.ActiveWorkbook.SaveAs path & "\" & filename
objXL.Quit 'Remove this if you want to leave Excel Open
Set objXL = Nothing
End Sub
"JohnM" <jo**@jmawer.demon.co.ukwrote in message
news:ej*******************@news.demon.co.uk...
Thanks ... it does make sense, but a bit of a pity, because I'm working on
a network where the users don't have save rights, one of the reasons I'm
doing this is so that they can take out chunks of data and analyse it in
excel in their own area, but aren't able to alter the database. It all
works a treat up to this selection bit.
Any ideas would be gratefully received.
"Kc-Mass" <co********@comcast.netwrote in message
news:6P******************************@comcast.com. ..
>>I assume you are using the form to let users specify some of the
parameters
of a query, which your code then builds the SQL string for.
You can either save the SQL string as a new queryDef and then hand that
querydef
name to the DoCmd.TransferSpreadsheet - or - Change the SQL string to
create a
new table and hand the table name to the DoCmd.TransferSpreadsheet
method.
TransferSpreadsheet works only with saved queries and tables.
Kevin C
"JohnM" <jo**@jmawer.demon.co.ukwrote in message
news:ej*******************@news.demon.co.uk...
>>>I can transfer from a query with
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Filenam",
CPath, True
I would like to use a form for the user to select and order data then
export th result. How do I set about this?
