Solved

Exporting Access Query to an Excel Spreadsheet

Posted on 2007-12-04
5
1,859 Views
Last Modified: 2009-08-20
I have a query called "usp_Anomalies" that I would like to export to an Excel sheet. I have the following code that works and that I have attached as a code snippet. The only thing left is the export process. Please note that the data of the query must be exported including the COLUMN NAMES of the query.


Dim sSQL As String
  Dim objXL As Object
  Dim objWB As Object
  Dim sFile As String
  Dim sShtName As String
  Dim i As Integer
 
  'Set name of file to save to
  sFile = "C:\ANOMALIES.XLS"
 
  'Set name of new sheet
  sShtName = "ANOMALIES"
 
 
  'Create a new excel document
  Set objXL = CreateObject("Excel.Application")
 
  'create new workbook
  Set objWB = objXL.Workbooks.Add
  
  'Save as a new file, delete existing file if it exists
  If Dir$(sFile) <> "" Then Kill sFile
  objWB.SaveAs sFile
  
  objWB.Close
  objXL.Quit
 
  'CLOSE DOWN
  Set objWB = Nothing
  Set objXL = Nothing

Open in new window

0
Comment
Question by:pancho_alvarez
5 Comments
 
LVL 11

Accepted Solution

by:
TWBit earned 250 total points
ID: 20405323
How about this:

    DoCmd.OutputTo acOutputQuery, [YourQuery], acFormatXLS, sFile
0
 

Expert Comment

by:Vittles
ID: 20405330
I have the following code attached to a button on one form that 'dumps' the behind the scenes query into excel (nothing fancy):  
DoCmd.OutputTo acOutputForm, "Query Name", acFormatXLS, , True

I also have a more complex code set up for another that updates, exports, formats & saves a table based on a query (converted the query contents to a table in order to avoid memo fields being cut off at 250 characters) that looks like the following:  

Sub export_to_excel click()    

DoCmd.SetWarnings False
DoCmd.OpenQuery "Current Table Contents-Delete query", acViewNormal, acEdit
DoCmd.Close acQuery, "Current Table Contents-Delete query"
DoCmd.OpenQuery "Update with new data to be Exported-Append query", acViewNormal, acEdit
DoCmd.Close acQuery, "Update with new data to be Exported-Append query"
DoCmd.SetWarnings True
    Dim objXL As Object
    Dim objWB As Object
    Dim objSheet As Object
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim lRow As Long
    Dim Pth
    Pth = CurrentProject.Path & "\File name.xls"
    On Error Resume Next
    Kill Pth
   
    Set rs = CurrentDb.OpenRecordset("End Table name")
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Add
    Set objSheet = objXL.ActiveSheet
   
    'Create Header - set to hold 5 fields (increase 0 to 4 range if there are more)
    For i = 0 To 4
        objSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
       
    'Fill in with data (4 columns)
    lRow = 2
    Do While Not rs.EOF
        For i = 0 To 4
            objSheet.Cells(lRow, i + 1).Value = rs.Fields(i).Value
        Next i
        rs.MoveNext
        lRow = lRow + 1
    Loop
   
    objSheet.Range("A1:E1").Font.Bold = True
    objSheet.Rows("1:1").RowHeight = 21
    objSheet.Columns("A:B").autofit
    objSheet.Columns("C").ColumnWidth = 40
    objSheet.Columns("E").ColumnWidth = 40
    objSheet.Columns("D").ColumnWidth = 20
    objSheet.Columns("A:E").WrapText = True
    objSheet.Range("A2:E2000").VerticalAlignment = xlCenter
   
    With objSheet.Rows("1:1").Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
   
   objSheet.Rows(2).Select
   objXL.ActiveWindow.FreezePanes = True

    objWB.SaveAs Pth

   rs.Close
   Set rs = Nothing

   objXL.Visible = True

End Sub


0
 
LVL 39

Expert Comment

by:stevbe
ID: 20405752
This is stock code from the Excel help file for the CopyFromRecordset function that sets the field names and copies all the data in 1 shot which is much faster than looping.

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1),  ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs

Steve
0
 

Expert Comment

by:Vittles
ID: 20405920
Good to know - I replaced that section in my own code.  Thanks!
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20407644
Well try this...

Define the name in the sheet where u want to paste the data from access to excel

Sub ImportAccess()
Dim MyAccess As Database
Dim MyTemp As DAO.Recordset

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Importing Data From Access"

Sheets("Your Sheet Name").Select
Range("Your Range Name").Select
Selection.clearcontents

Set MyAccess = OpenDatabase("Your database path")

Set MyTemp = MyAccess.OpenRecordset("usp_Anomalies")

'**********Importing From Access**********
Sheets("Your Sheet Name").Select
Range("Your Range Name").Select
Selection.CopyFromRecordset MyTemp


Range("a1").Select
End Sub
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question