Export to Excel

[Lol Owen]

Hi, as part of my ongoing nightmare dbase I want to output data from a query to Excel. This is straightforward enough, however, I want to plot a graph on my spreadsheet in Excel from the exported data. If I use the Do.Cmd acOutputTo method it any chart I have created and any subsequent sheets I have created in the spreadsheet. I'm currently trying the DoCmd. TransferSpreadSheet method directly from the query. I activate this using a button on a form. The code I have tried is below:

DoCmd.TransferSpreadsheet acExport, 8, "qryCustomerBookingTotals", "c:\Documents and Settings\Lol\Desktop\Chart.Booking Totals"

When I get this I just keep getting a message saying "Cannot update. Database or object is read only"

Any ideas please? I want to be able to have the chart updated every time the data is re-transferred to excel from the query.

Cheers, Lol

 

[Jon K]

"c:\Documents and Settings\Lol\Desktop\Chart.Booking Totals"

The . is causing the problem.

 

[Lol Owen]

I was using the . to designate the sheet name in the workbook. I have one sheet to accept the data and one to plot the chart, linked to that sheet. Any ideas how to designate which sheet to export to in the spreadsheet?

Cheers, Lol

 

[gecko_1]

This is one way to do it, its not the way you where trying to export.

Dim sql As String
Dim xlx As Object, xl As Object, xlw As Object, wb As Object
'--DB
Dim db As Database, rs As Recordset

    Set xlx = CreateObject("Excel.Application")
    Set xlw = xlx.workbooks.Open(reportLoc())
    Set xl = xlw.worksheets(1)
    '  OR   Set xl = xlw.worksheets("SheetName")
    xl.Activate
    
    Set db = CurrentDb
    sql = "SELECT * .....    
      
    Set rs = db.OpenRecordset(sql, dbReadOnly)
        
    xl.Cells(5, 1).copyfromrecordset rs, , 12

 

[Lol Owen]

Thanks mate, but that is totally beyond my comprehension! I'm still banging my head on the DO.Cmd object and associated methods. Any ideas anyone?

Cheers, Lol

 

[samculb]

I relatively new to access, but you can usually find that type of info on the microsoft website in the developer forum.

 

[modest]

To be honest, what gecko pointed out is probably the easiest code that you will find to target a specific sheet name. The reason is because without identifying an object, JET has default procedures to avoid data discrepancies (it can add a sheet, but not update one without the proper calls).

So I'll reuse some of what Gecko provided, but clean it up and add comments so you can understand. And trust me, this is basic stuff, we could have listed some ADO or DAO object calls. As you become more educated on what these do, you'll soon learn how elementary my comments are.

    Dim App  As Object                                           'Excel interface
    Dim ws   As Object                                           'Your worksheet
    Dim File As Object                                           'Your file
    Dim rs   As Recordset                                        'Table created from running your query

    Set App = CreateObject("Excel.Application")                  'This opens excel application windows
    Set File = App.Workbooks.Open("<Full_File_Path\book1.xls>")  'This opens your particular excel file (or workbook)
    Set ws = File.Worksheets("<Your_sheet_name>")                'This opens your sheet that you want to write to
    ws.Activate
    
    Set rs = CurrentDb.OpenRecordset("<Query1>")                 'This runs your query (rs holds the name of the table that is displayed for the query)
    ws.Cells(1, 1).CopyFromRecordset rs, , 12                    'This copies your query to your sheet starting at the first cell and first column
    
    App.Workbooks.Close                                          'This closes the workbook and asks you to save your excel sheet

-hope this helps,
modest

 

[Lol Owen]

Thanks Modest, that's more in my line! I have a VB developers guide to ADO but I don't need to learn that yet, if at all!

Thanks everyone for your help, Lol

 

[modest]

no prob, once you get your sheet open, there are different ways to copy your query over