[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2010-11-17
19
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34159759
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
ID: 34159870
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34159885
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 44

Expert Comment

by:GRayL
ID: 34159997
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
ID: 34160023
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34160254
looks like a repeat of code in http#a34159759
0
 

Author Comment

by:vsllc
ID: 34160574
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
ID: 34163615
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34163708
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
 

Author Comment

by:vsllc
ID: 34163812
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34163897
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
ID: 34164240
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34164328

* 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
ID: 34164591
Did that, but I still get the error message.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34164679
can you upload the db you are working on..
0
 

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34165071
make it a .mdb file..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34165079
also, include a copy of the excel file..
0
 

Author Comment

by:vsllc
ID: 34191036
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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