TransferSpreadsheet - Alternative?

have you tried adding a target range argument :

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " CA Alerter Template.xls", True, "worksheet_name"

I did try that, but it seemed not to accept the value;

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " Alerter Template.xls", True, "CA_Alert!A1"

I was afraid of automation! Haven't programmed that in a long long time, but happy to give it a go;

Set xObj = New Excel.Application
xObj.Open.........


''''do stuff here''''''

xObj.Close
set xObj = Nothing

or something like that?

I guess it will not accept the value because it is looking for a worksheet name instead of a target range name.

have you tried

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " Alerter Template.xls", True, "CA_Alert"

Pretty much.

A good trick is to use GetObject, and if it errors (i.e. it can't find an existing running instance of Excel) then in the error handler use CreateObject and set a flag to indicate that it's been created.  At the end of the routine check the flag, and if it's been set then close the Excel instance.  If not, then leave it be, so you don't accidentally close someone's open Excel work.

While you're testing it, you can set xoObj.Visible = True so you can watch what the heck is going on!

I'd love to dig out some code for you, but I'm at work and have deadlines.  You know how it is.  Check the code on Dev Ashish's site and you can't go wrong.  It'll be a fiddle, but once you've nailed it, it's a technique you'll be able to use forever.

Guys, thanks for all comments, I too have had a pretty chocker day with meetings, I am going to try both solutions from home this evening and feed back tomorrow.

Much appreciated
Dave

Know how that feels!  Good luck.

If you are going to 'append' rows to the same worksheet from those three queries, you have to make an estimate of where the first  and second inserts will end so that you can make a reasonable estimate of where to provide Range values for the second and third SELECT statements.The Range value for the second pass will look like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " OA Alerter Template.xls", True,"WorkSheetName!A30:L55"

Use your actual sheet name in place of WorkSheetName.  If the first pass stopped at row 27, you could start the next pass at row 30 and if 12 fields were exported, and you estimated 25 rows, including column names, the next range would be as shown.  BTW, each export will include the column

Click on Alt+F11 to get to the VBEditor window and in Help type - Transferspreadsheet - have a good read of both Method and Action.  I know the Help says if you include a Range on an export to a spreadsheet, the transfer will fail.  That was not the case in my system (MSOffice Pro) - so long as I included a row/column range after the worksheet name, the transfer occurred. If you only included the worksheet name in the range, then the Excel system created a new worksheet.

If you go the automation route, read my Fast Data Push To Excel article:
https://www.experts-exchange.com/A_2253.html

In addition to the example I used in that article, I also mention the CopyFromRecordset method for ADO recordsets.

aikimark, this was a very interesting read, thank you.

akoster, simply entering the name of a worksheet in the target file didn't pick up the existing sheet, it added another sheet and renamed it, all very odd.

grayl, the target file simply contained blank cells, only formatting, so whilst I appreciate your comments, on this occassion, it hasn't help me.

Andrew, you referral to the Dev's site helped me introduce a much more simplified form of automation.

Thank you all of you for your input, but points awarded to Andrew

for those interested, here is my final piece of code;

'Export the Team Alerter records - if export selected
    If Me.chkAlerterExports Then
        Set objXL = New Excel.Application
        Set qry = CurrentDb.QueryDefs("qryAlerter_Export")
        
        For n = 1 To 5
            Set objWkb = objXL.Workbooks.Open(Me.txtFilePath & "Team " & n & " Alerter Template.xls")
            
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND CA_ALERT<>'No Alert';"
            Set rs = CurrentDb.OpenRecordset("qryAlerter_Export", dbOpenSnapshot)
            Set objSht = objWkb.Worksheets("CA_Alert")
            objSht.Range("A2").CopyFromRecordset rs
            Set rs = Nothing
            
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND OA_ALERT<>'No Alert';"
            Set rs = CurrentDb.OpenRecordset("qryAlerter_Export", dbOpenSnapshot)
            Set objSht = objWkb.Worksheets("OA_Alert")
            objSht.Range("A2").CopyFromRecordset rs
            Set rs = Nothing
            
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND LIMIT_ALERT<>'No Alert';"
            Set rs = CurrentDb.OpenRecordset("qryAlerter_Export", dbOpenSnapshot)
            Set objSht = objWkb.Worksheets("Limit_Alert")
            objSht.Range("A2").CopyFromRecordset rs
            Set rs = Nothing

            Set objSht = Nothing
            objWkb.Save
            objWkb.Close
            Set objWkb = Nothing
        Next n
        objXL.Quit
        Set objXL = Nothing
    End If

Select allOpen in new window