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

TransferSpreadsheet - Alternative?

My fellow experts, I have a bit of a problem insofar that I am using a loop to change a qry for exporting data from Access to Excel!

Basically, I loop through numbers 1 to 5, producing 3 different Excel files.

The files have already been created and have formatting in place, my problem is that the first of the 3 exports in the loop exports successfully to the formatted sheet, on 2 and 3 however, the export ignores the existence of the only sheet (formatted) in the file and adds instead, another sheet, totally unformatted!

Its driving me crazy!

Below is my code - The CA Alert export successfully copies the records to a formatted sheet in the target file (the file already exists)

Does anyone one of you know how I can successfully export the 15 datasets with the correct formatting of the resulting 15 Excel files intact?


For n = 1 To 5
            Set qry = CurrentDb.QueryDefs("qryAlerter_Export")
            'Export the CA Alert records
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND CA_ALERT<>'No Alert';"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " CA Alerter Template.xls", True
            'Export the OA Alert records
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND OA_ALERT<>'No Alert';"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " OA Alerter Template.xls", True
            'Export the Limit Alert records
            qry.SQL = "SELECT * FROM qry_ExecPack_Data_All_Output WHERE TEAM='Team " & n & "' AND LIMIT_ALERT<>'No Alert';"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryAlerter_Export", Me.txtFilePath & "Team " & n & " Limit Alerter Template.xls", True
        Next n

Open in new window

0
Runrigger
Asked:
Runrigger
  • 5
  • 3
  • 2
  • +2
1 Solution
 
Arno KosterCommented:
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"
0
 
Andrew_WebsterCommented:
What you'll need to do is use automation.

Create an instance of an Excel object, have it create your sheet based on an Excel template, then use CopyFromRecordset to populate the sheet.  Use this basic approach within your for loop, and make sure that you close the Excel object properly otherwise you can end up with a bunch of hidden Excel instances eating your memory!

There's a great post here AccessWeb CopyFromRecordset example that handles most of the detail.
0
 
RunriggerAuthor Commented:
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"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RunriggerAuthor Commented:
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?
0
 
Arno KosterCommented:
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"
0
 
Andrew_WebsterCommented:
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.
0
 
RunriggerAuthor Commented:
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
0
 
Andrew_WebsterCommented:
Know how that feels!  Good luck.
0
 
GRayLCommented:
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.
0
 
aikimarkCommented:
If you go the automation route, read my Fast Data Push To Excel article:
http://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.
0
 
RunriggerAuthor Commented:
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
0
 
RunriggerAuthor Commented:
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

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now