Copy / paste to Excel

[CedarTree]

Hello - anyone have simple coding I can steal that runs a query or two, copies and pastes into Excel and does it without the query opening visibly? (Or turn off screen updating I guess). I'm avoiding transferspreadsheet b/c I don't want to transfer the whole table AND I want to potentially copy two queries on to one Excel tab.
Thanks!

 

[Dreamweaver]

You could limit the output when you use transfer method you always have option for limiting the data set.
Not sure what you mean about 2 queries but I haven't used excel for a while

Mick

 

[theDBguy]

Hi. Check out the CopyFromRecordset method. I'll see if I can find a link for you.

Here's one link I found on a quick search, hope it helps...

Modules: Transferring Records to Excel with Automation

theaccessweb.com

 

[Pat Hartman]

Instead of avoiding the easiest to use tool, perhaps it would be better to learn how to use it. In Access, Tables and Select queries are interchangeable for most purposes. So if you can use a table for something, you can use a saved querydef as a substitute. You may have noticed that Access is OK with you giving a form, report, and a table all the same name such as "Client". However, it will not allow you to have a table and a query with the same name so you can't have both a query and a table named "Client". That is so Access can always tell whether you have used a table or a query to do something.

If you want two queries on the same tab, create a union query and use TransferText to export the union. However, if the queries are not the same layout, you would need to use OLE automation to place query A here and query B there.

Access is a Rapid Application Development tool. You get the most bang for your buck when you use the built in tools. Code should be your last choice. Do things the "Access" way whenever possible.

 

[CedarTree]

Yes the issue is the queries are very different formats, so I want to copy/paste each one separately to the same Excel tab.

 

[bastanu]

Here is what I have in an older application using copy/paste but I strongly recommend if you can use the CopyFromRecordset instead

Private Sub cmdExportRawDataToExcel_Click()
On Error Resume Next
'DoCmd.OutputTo acOutputQuery, "YourQueryHere", acFormatXLS, , True

Dim oApp As Excel.Application
Dim oWT As Excel.Workbook
Dim oWS As Excel.Worksheet

Dim lastRow As Long
Dim lStartOfDataList As Long
Dim lEndOfDatList As Long
DoCmd.RunCommand acCmdSaveRecord


Application.Echo False
Set oApp = GetObject("Excel.Application")
          If Err.Number <> 0 Then Set oApp = CreateObject("Excel.Application")
    With oApp
        .Visible = True
        .Workbooks.Close
        On Error GoTo 0
        .Workbooks.Add
        .Workbooks(1).Activate

        Set oWT = .ActiveWorkbook
        Set oWS = oWT.ActiveSheet

        .ScreenUpdating = False
        .DisplayAlerts = False
        'set orientation to landscape
        oWS.PageSetup.Orientation = xlLandscape
        'lets do the header
        oWS.PageSetup.CenterHeader = "&""Arial,Bold""&10" & "YOUR TITLE HERE"
        'now the footer
'        oWS.PageSetup.CenterFooter = " = Page " & "[Page]" & " of " & "[Pages]"
'         oWS.PageSetup.RightFooter = " = " & "[Date]" & ""
        
        oWS.PageSetup.CenterFooter = "Page &P of &N"
        oWS.PageSetup.RightFooter = "Printed &D &T"
        oWS.PageSetup.LeftFooter = "EXCEL FOOTER"
        
        
        oWS.PageSetup.LeftMargin = oApp.InchesToPoints(0.75)
        oWS.PageSetup.RightMargin = oApp.InchesToPoints(0.75)
        oWS.PageSetup.TopMargin = oApp.InchesToPoints(1)
        oWS.PageSetup.BottomMargin = oApp.InchesToPoints(1)
        oWS.PageSetup.PaperSize = xlPaperLegal
        'force one fit one page wide
        oWS.PageSetup.Zoom = False
        oWS.PageSetup.FitToPagesWide = 1
        oWS.PageSetup.FitToPagesTall = False
        
        oWS.PageSetup.PrintTitleRows = oWS.Range("A1", oWS.Range("A1").End(xlUp)).EntireRow.Address
        'now lets paste the exception list
        DoCmd.OpenQuery "MedicalWriteOffs"
        DoCmd.RunCommand acCmdSelectAllRecords
        DoCmd.RunCommand acCmdCopy
        Set oWS = oWT.ActiveSheet
        oWS.Range("A1").Activate
        oWS.Paste
        DoCmd.OpenQuery "YourQueryHere"
        DoCmd.RunCommand acCmdSelectRecord          'Minimize the risk of message: "You have copied a large amount of data..."
        DoCmd.RunCommand acCmdCopy
        DoCmd.Close acQuery, "YourQueryHere", acSaveNo
        'add the totals
        'we need to add a total now
        lastRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row
        oWS.Range("A" & lastRow + 1).Activate
        oWS.Range("A" & lastRow + 1) = "TOTAL:"
        oWS.Range("A" & lastRow + 1).Font.Bold = True
        oWS.Range("D" & lastRow + 1) = .WorksheetFunction.Sum(oWS.Range("D2:D" & lastRow))
        oWS.Range("D" & lastRow + 1).Font.Bold = True
          
        
        
        oWS.Range("E" & lastRow + 1) = .WorksheetFunction.Sum(oWS.Range("E2:E" & lastRow))
        oWS.Range("E" & lastRow + 1).Font.Bold = True
        
        oWS.Range("D2:E" & lastRow + 1).NumberFormat = "$#,##0.00"
        
        oWS.Range("A" & lastRow & ":K" & lastRow).Borders(xlEdgeBottom).LineStyle = xlContinuous
        oWS.Range("A" & lastRow & ":K" & lastRow).Borders(xlEdgeBottom).Weight = xlThick
        oWS.Range("A" & lastRow & ":K" & lastRow).Borders(xlEdgeBottom).ColorIndex = xlAutomatic
         'now to autofit columns
        lastRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row
        lEndOfDatList = lastRow
        oWS.Range("A1:J" & lastRow).Select
        .Selection.Font.Size = 10
        .Selection.WrapText = False
        oWS.Range("A1:K" & lastRow).Columns.AutoFit
        
        oWT.Worksheets("Sheet1").Name = "SHEET_NAME"
          
        
        .ScreenUpdating = True
        .DisplayAlerts = True
     End With


Application.Echo True

oApp.Visible = True
AppActivate "Microsoft Excel"
Set oApp = Nothing
End Sub

I have an example on how to use copyfromrecordset in my free utility :http://forestbyte.com/ms-access-utilities/fba-pivot-table-designer/

Cheers,
Vlad

 

[CedarTree]

Thanks - I think the recordset approach earlier above is likely more efficient. Thanks everyone!

 

[MajP]

One thing that you seemed confused on

I'm avoiding transferspreadsheet b/c I don't want to transfer the whole table

You can use a select query and not the whole table.

A string expression that is the name of the Office Access table that you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query whose results you want to export to a spreadsheet