Export Query to .ODS file starting at a specific cell

[Baldeagle]

HMRC (the UK Revenue Commissioners) are introducing online claiming of tax refunds for Charitable Donations. Their spreadsheet is in .ODS format and the data must be entered as shown in the attached sample (which is a zipped Excel version of their spreadsheet). While I am proficient at developing Access, I am a rookie when it comes to VB. I have a query (let's call it 'Claim') that will produce the data in the correct format. How can I get this data automatically transferred to the spreadsheet (call it 'Claim1' for convenience) starting at cell C25?

I have seen some examples of something similar but am at a loss to translate it into a format that I need. Can someone help me please? I'm pretty good at cutting & pasting!

[June7]

First, can Access even connect to ODS file? (I doubt it.) If not, must convert to a format Access can work with. You apparently have Excel available.
Here are threads that demonstrate writing data to/from Excel.
http://forums.aspfree.com/microsoft-...el-413629.html
http://forums.aspfree.com/microsoft-...le-413493.html

Need to set VBA Reference to Microsoft Excel x.0 Object Library

In your case you want to open an existing workbook, not create a new one. Another example:

Private Sub ExportExcel()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim intRow As Integer
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("path\filename.xlsx")
intRow = 25
rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
'code to write data to cells of worksheet, see other examples
Wend
oExcel.Visible = True
Set oBook = Nothing
Set oExcel = Nothing
End Sub

[Baldeagle]

Thanks June7 for the guidance. You seem to be correct that I can't copy data direct from Access to a .ods file. However I have used a combination of bits and pieces from where you directed me to and have got a working button! I'm not sure whether I have some unnecessary elements in my coding but it produces the end result that I need! I end up with the HMRC .ods spreadsheet open, covered by a new .xls spreadsheet into which the output of my Access query has been pasted. Because it doesn't hold the date formatting from the query - dd/mm/yy - I have to correct this in the Excel file - (hence the message box which I have shortened below). I then copy and paste this data into the .ods spreadsheet. The following is my code. If you think I can delete any of the elements I would be happy to take your further advice. But don't waste much time on this as I can leave it the way it is. Do you have any idea why the date formatting is not held? It appears as d-mm-yy.

Thanks again for your help.

Dim app As Excel.Application
Dim xl As Excel.Workbook
MsgBox ("If the Donation Date ........")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Gift Aid Claim (Spring) (New)", "C:\Our Folders\BWMC\Blank.xls", True
Set app = CreateObject("Excel.Application")
Set xl = app.Workbooks.Open("C:\Our Folders\BWMC\Blank.xls")
app.Visible = True
Set ws = xl.Worksheets(1)
Set ws = Nothing
Set xl = Nothing
FollowHyperlink "C:\Our Folders\BWMC\gift_aid_schedule.ods", , True, True
FollowHyperlink "C:\Our Folders\BWMC\Blank.xls", , True, True

[June7]

Does this help explain date issue http://allenbrowne.com/ser-36.html

TransferSpreadsheet method does not require Excel objects. Try just:

MsgBox ("If the Donation Date ........")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Gift Aid Claim (Spring) (New)", "C:\Our Folders\BWMC\Blank.xls", True
FollowHyperlink "C:\Our Folders\BWMC\gift_aid_schedule.ods", , True, True
FollowHyperlink "C:\Our Folders\BWMC\Blank.xls", , True, True

[Baldeagle]

Thanks for everything.