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?
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
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, queryname, mypath, True