asked on

Export of query result to Excel with TransferSpreadsheet into existing sheet fails, but creates unwanted new sheet

I am exporting a number of query results to an existing Excel workbook (see code Example below). The results should be placed in sheets which are named exactly like the queries which works in most cases. However, for a recently added query (say "myQuery") (and corresponding Sheet "myQuery") this does not work. Rather, a new sheet "myQuery1" is created and the query result of the query "myQuery" is placed there.

Since I have other sheets and formulas referencing to the sheet "myQuery", this is not acceptable for me. What could be the reason for this behaviour and how can I fix it?

Code Example:

Dim queryname
For Each seg In getSegments
queryname = "query_" & seg

Dim startMonth As String
startMonth = getStartMonth(seg)

'
' all other cases
'
CurrentDb.QueryDefs(queryname).sql = "SELECT DateSerial([year],[month],1) AS [Date], MEMBER_RANKING.Segment, " & _
"MEMBER_RANKING.A_MEMBER_ID as Member, MEMBER_RANKING.volume as [Member volume],MEMBER_RANKING.rank as Rank, MEMBER_RANKING.Marketvolume as [Market volume] " & _
"FROM MEMBER_RANKING WHERE (MEMBER_RANKING.A_MEMBER_ID='" & membi & "' and MEMBER_RANKING.segment = '" & seg & _
"') ORDER BY MEMBER_RANKING.year, MEMBER_RANKING.month;"

'
' TRANSFER QUERY TO MEMBER-SPECIFIC EXCEL SHEET
'
Debug.Print mypath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, queryname, mypath, True
Next seg

SOLUTION

ipendlebury

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Like it or not you cannot do it. Here is what help has to say:

Range The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax does not work in Microsoft Access 97 or Microsoft Access 2000). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, Excel 8.0, or Excel 2000 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.
Note When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

jefftwilley

You could probably swing it this way using Excel Automation

Dim xlApp As Object
Dim xlWb1 As Object
Dim xlWs1 As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWb1 = xlApp.Workbooks.Open("C:\Test1.xls", True) <--------your spreadsheet name
Set xlWs1 = xlWb1.Worksheets("Sheet1") <--------Replacing sheet1 of course, with your query name
xlWb1.Sheets("Sheet1").Select

Now that you have it opened and to your sheet...you can paste records from your query.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

The second queryname is the sheet name in the workbook myQueryWorkBook.xls - of course you want them to be the same.

GRayL

Thanks, glad I could help. Sorry MS Help was not the same;-)

fritzke

ASKER

Thanks to all of you for your effort!