Re: export form filtered data to excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 -

.

Quantcast