Solved

Can I export query results to a spreadsheet in an Excel file?

Posted on 2010-11-17
19
290 Views
Last Modified: 2012-05-10
I would like to export the results if a select query to a spreadsheet in an existing .xls.  I would like to overwrite the results on the spreadsheet without overwriting the .xls.

Is this possible?
0
Comment
Question by:vsllc
  • 9
  • 8
  • 2
19 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
can you explain this

 I would like to overwrite the results on the spreadsheet without overwriting the .xls.

docmd.transferspreadsheet acexport,8,"queryName","c:\myexcel", true ,"sheetName"
0
 

Author Comment

by:vsllc
Comment Utility
Yes, there are other sheets in the .xls workbook that I'd like to leave alone.  Just replace all the data on the one spreadsheet.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
use this codes


Sub exportExcel()
Dim rs As dao.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\MyExcel.xls"

    Set rs = CurrentDb.OpenRecordset("query1")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.Cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A2").CopyFromRecordset rs  'copy the data
   
    'save the excel file
    xlObj.ActiveWorkbook.Save
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
End Sub

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
You can transfer the recordset from a table or a query using:

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"queryName","ExcelPathWkbk",true,"SheetName"

You do not need any code.


0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I just tested and was able to transfer a table or a query to an existing workbook where Sheet1 contained data, and sheets 2 & 3 were blank.  I specified "Sheet2", in the docmd line above as well as the path and file name of the workbook and the transfer occurred without any changes to Sheet1.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
looks like a repeat of code in http#a34159759
0
 

Author Comment

by:vsllc
Comment Utility
When I do just the docmd. the spreadsheet gets added to my workbook.  It doesn't overwrite the existing one.  Just adds it with a 1 after it.

I'll test the other code.
0
 

Author Comment

by:vsllc
Comment Utility
OK, I figured out my issue with the docmd.  I had the spreadsheet already created in the file so when added, it created a new one.  I removed the spreadsheet and ran the docmd. several times.

I have two issues with it though.

1-I get an error message after event is run that says..."Unable to open )....Cannot open the specified file."
2-Existing spreadsheet data is not completely overwritten.  If the existing sheet has more records than the query results, the overage will be left.  For example, if sheet has 200 records and qry results 175, only 175 will be overwritten and the remaining 25 left.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
using the codes, you can delete existing data from the sheet

Sub exportExcel()
Dim rs As dao.Recordset
Dim xlObj As Object, Sheet As Object, iCol As Integer

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\MyExcel.xls"

    Set rs = CurrentDb.OpenRecordset("query1")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")

'clear contents of sheet1
         Sheet.cells.clearcontents

        For iCol = 0 To rs.Fields.Count - 1
            Sheet.Cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A2").CopyFromRecordset rs  'copy the data
   
    'save the excel file
    xlObj.ActiveWorkbook.Save
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
End Sub

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:vsllc
Comment Utility
capricorn1-When I try to run this, I get a compile error "User-defined type not defined" on the following line:  rs As dao.Recordset
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
you have to add to your references, Microsoft DAO x.x Object library  (x.x) is number

Tools >references  


Sub exportExcel()

Dim rs As dao.Recordset

Dim xlObj As Object, Sheet As Object, iCol As Integer



    Set xlObj = CreateObject("Excel.Application")

    xlObj.Workbooks.Open "C:\MyExcel.xls"



    Set rs = CurrentDb.OpenRecordset("query1")

    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")



'clear contents of sheet1

         Sheet.cells.clearcontents



        For iCol = 0 To rs.Fields.Count - 1

            Sheet.Cells(1, iCol + 1).Value = rs.Fields(iCol).Name

        Next

    Sheet.Range("A2").CopyFromRecordset rs  'copy the data

   

    'save the excel file

    xlObj.ActiveWorkbook.Save

   

    Set Sheet = Nothing

    xlObj.Quit

    Set xlObj = Nothing



rs.close

End Sub

Open in new window

0
 

Author Comment

by:vsllc
Comment Utility
Got it.  Checked DAO 3.6 in References.  This works but I'm still getting the error message "Unable to open)...Cannot open the specified file.".  How can I prevent that?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

* close all opened excel
* open task manager
* click on the Processes tab
* locate all Excel.Exe
* select one at a time, then click End Process

close task manager

run the code again

open the excel file
0
 

Author Comment

by:vsllc
Comment Utility
Did that, but I still get the error message.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
can you upload the db you are working on..
0
 

Author Comment

by:vsllc
Comment Utility
no I can't.  But let me try to recreate in a dummy db and load that.  Will take a little time.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
make it a .mdb file..
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
also, include a copy of the excel file..
0
 

Author Comment

by:vsllc
Comment Utility
Before I could replicate, I had to reinstall MS Access.  Now when I run the code, I get another message that says:

Run-time error '9'

Subscript out if range.

It's highliting the line if code that designates my spreadsheet.

I've googled but not sure what thus error is for.  Perhaps there's a setting I need to check/unchecked after the reinstall if Access?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now