By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

Transfer to Excel from a form

P: n/a
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?
Nov 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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?

Nov 17 '06 #2

P: n/a
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?


Nov 18 '06 #3

P: n/a
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?



Nov 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.