Help with exporting to Excel Spreadsheet 1

[unclesvenno]

Gee I'm spending more time looking for help today than actually working! I'm trying to export the results of a query to an excel spreadsheet. Any ideas?

Thanks again,
Uncle Svenno

 

[BSando]

Uncle Svenno,

there are a few options.
create a Macro and use "outputto" This is the easiest way to get you started.

But,

I suggest using Docmd.transferspreadsheet
Below is a sample of what I have used. It takes the query and renames it. That way it saves the worksheet the name that I want.
---------------------------------------------
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String

Set db = CurrentDb
SQL = "Select * from [qry_name]"

db.QueryDef ("new sheet name"), SQL

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
("new sheet name"), "C:\3651H\Monthlysales2.xls", True

db.QueryDefs.Delete ("new sheet name")

Set db = Nothing




Some people make things happen, some watch while things happen, and some wonder 'What happened?'

 

[kjv1611]

And you might want to add at the end of the code:

Set qDef = Nothing
db.Close
qDef.Close

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[unclesvenno]

I should have mentioned my access database is an access 97 database!(I know it's ancient) Unfortunately I'm getting a compile error, pulling up at db.QueryDef. Here's my code:

Private Sub cmdExport_Click()

MsgBox "Paul - Add Creating Excel Spreadsheet Code"

Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String

Set db = CurrentDb
SQL = "Select * from FileRepOrder"

db.QueryDef ("SvennosSample"), SQL

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ("SvennosSample"), "U:\SvennosSample.xls", True

db.QueryDefs.Delete ("SvennosSample")

Set db = Nothing
Set qDef = Nothing
db.Close
qDef.Close

End Sub

Thanks again,
Uncle Svenno

 

[AccessGuruCarl]

Hi unclesvenno,

This should work for you in Access 97
You shouldn't need the DAO reference, but it wont hurt.
If you need to create the query

Private Sub Command10_Click()
On Error GoTo Eval_Error

MsgBox "Paul - Creating Excel Spreadsheet"

Dim db As Database
Dim qDef As QueryDef
Dim SQL As String
Dim fPath As String
'Set a path and file name!
  fPath = "C:\SvennosSample.xls"

  Set db = CurrentDb
    SQL = "Select * from YourTableNameHere"
'Create qry based on SQL
  Set qDef = db.CreateQueryDef("qryTmpExport", SQL)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ("qryTmpExport"), fPath, True
'Delete the object
    DoCmd.SetWarnings False 'Turn-off warning message
    'DoCmd.DeleteObject acQuery, "qryTmpExport"
    DoCmd.SetWarnings True
  
  Set qDef = Nothing
  Set db = Nothing

MsgBox "Paul - Export Complete!"

Exit_Command10_Click:
    Exit Sub

Eval_Error:
    If Err.Number = 3012 Then  'qry already exists, delete and resume
      DoCmd.SetWarnings False 'Turn-off warning message
      DoCmd.DeleteObject acQuery, "qryTmpExport"
      DoCmd.SetWarnings True
      Resume
    Else    'Critical Error, display message.
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
    End If
    Resume Exit_Command10_Click
    
End Sub

If the query already exists, this single line will work.

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ("Your Query/Table Name"), fPath, True

The 'acSpreadsheetTypeExcel7' type is optional, you can remove it if you get an error!




AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[steveg0702]

AccessGuruCarl,

I added that code to one of my tables and it worked great for one of my needs, but it exported the whole database ("TRIAGE_LOG") that I specified. Can you please help me:
1) Only export the query I am running ("Data Integrity - No Reas From Level")
2) Export to an open Excel document rather than saving one

Here is my code:

Private Sub Data2_Click()
On Error GoTo Eval_Error

Dim db As Database
Dim qDef As QueryDef
Dim SQL As String
Dim fPath As String
'Set a path and file name!
    fPath = "C:\Documents and Settings\SG85174\Desktop\Temp.xls"

    Set db = CurrentDb
        SQL = "Select * from TRIAGE_LOG"
'Create qry based on SQL
    Set qDef = db.CreateQueryDef("Data Integrity - No Reas From Level", SQL)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ("Data Integrity - No Reas From Level"), fPath, True
'Delete the object
        DoCmd.SetWarnings False 'Turn-off warning message
        'DoCmd.DeleteObject acQuery, "qryTmpExport"
        DoCmd.SetWarnings True
  
  Set qDef = Nothing
  Set db = Nothing

Exit_Data2_Click:
    Exit Sub

Eval_Error:
    If Err.Number = 3012 Then  'qry already exists, delete and resume
      DoCmd.SetWarnings False 'Turn-off warning message
      DoCmd.DeleteObject acQuery, "Data Integrity - No Reas From Level"
      DoCmd.SetWarnings True
      Resume
    Else    'Critical Error, display message.
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
    End If
    Resume Exit_Data2_Click

    
End Sub

Thanks!
Steve

 

[AccessGuruCarl]

Hi steveg0702

Not sure I understand the question(s).

What do mean by it exported the whole database?

Is there a table called TRIAGE_LOG or is this the db name?

Create a query that pulls the data you want...
Run it to make sure ot works, and displays the correct info.

Next click, View - SQL View
Copy and paste this into your form.
You will need to make minor adjustments for it to run in VBA.

You can export to any excel file, I don't think the excel file can be open though. You'll need to test this!
It will overwrite the orginal excel file, unless you specify a new worksheet. This requires a reference to excel.

Hope this helps, let me know if you need further help.

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.