• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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

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
vsllc
Asked:
vsllc
  • 9
  • 8
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
vsllcAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
GRayLCommented:
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
 
GRayLCommented:
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
 
Rey Obrero (Capricorn1)Commented:
looks like a repeat of code in http#a34159759
0
 
vsllcAuthor Commented:
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
 
vsllcAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
vsllcAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
vsllcAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

* 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
 
vsllcAuthor Commented:
Did that, but I still get the error message.
0
 
Rey Obrero (Capricorn1)Commented:
can you upload the db you are working on..
0
 
vsllcAuthor Commented:
no I can't.  But let me try to recreate in a dummy db and load that.  Will take a little time.
0
 
Rey Obrero (Capricorn1)Commented:
make it a .mdb file..
0
 
Rey Obrero (Capricorn1)Commented:
also, include a copy of the excel file..
0
 
vsllcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now