Re: export form filtered data to excel

Try changing

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, Len(strSQL) - 1) & _
" WHERE " & Me.Filter
End If

to

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If

Or, better yet, look at the actual SQL for your query and see what's
required.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<ryan.fitzpatrick3@xxxxxxxxxxx> wrote in message
news:ae265da0-9ae9-4d46-81c5-4d866045244d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I got an error saying "characters found at end of SQL statement".



On Oct 9, 4:25 am, "Douglas J. Steele"
<NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote:
One option would be to generate the appropriate SQL, save it to a
temporary
query and export the temporary query.

Since you're currently using a query, you could try something like:

Private Sub Command84_Click()

On Error GoTo Err_Command84_Click

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("QryAdageVolumeSpend").SQL

' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)
Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, Len(strSQL) - 1) & _
" WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
DoCmd.TransferSpread*** transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True

' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName

Else

DoCmd.TransferSpread*** transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName::="QryAdageVolumeSpend", FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True

End If

Exit_Command84_Click:
Set dbCurr = Nothing
Exit Sub

Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click

End Sub

Note that I've only handled simple queries there: a SELECT with no WHERE
clause but (possibly) an ORDER BY clause. You know what
QryAdageVolumeSpend
looks like: you may have to modify the code. Of course, you also have the
option of simply putting the SQL there, as opposed to retrieving the SQL
from QryAdageVolumeSpend and working with it.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:1fffdf1a-96a0-4a2c-a37c-04018d2eaf07@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
OK I got the export vba I think. this code exports the query but the
whole thing and not the filtered selection on the form.

Private Sub Command84_Click()

On Error GoTo Err_Command84_Click

DoCmd.TransferSpread*** transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:="QryAdageVolumeSpend", FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls", _
hasfieldnames:=True

Exit_Command84_Click:

Exit Sub

Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click

End Sub

I changed the form source from table to query. I made a query of the
table and made the form pull that instead. How do I link the form to
the query? When I linked up the combo boxes to the query and tried to
select an item it gave me an error. Error: You cannot add new clients
to this search form. Permission denied. What?s this mean? How do I get
the query to have the same results as the form, so when I export with
the code above it?ll give me the filtered list and not all 70k
records?

On Oct 8, 11:49 am, "Douglas J. Steele"



<NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote:
You don't dump data into queries: queries are how you extract data from
tables.

Create a query with the appropriate Where clause and export the query to
Excel. You should never be working directly with tables anyhow.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:4db88462-80e0-493b-a6cd-d5a1f5b2a591@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Is there away to export a filtered selection of a table (which is on a
form) to excel? I'm reading some of the posts, and it appears I would
need to have the filtered selection dump into a query then export it.
Is that correct? What would be the best approach?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


.

Loading