Exporting query results to a 'fixed' excelfile.

[Sniper-BoOyA-]

Good morning,

I use the following vba code to export Query results to an Excel file;

Private Sub btn_Excel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query_Controlekaart_Blanco_28d", "P:\17\10_Cluster_17_Algemeen\98_Overige\MSH\Vliegas_Testfase\Excel bladen\Contolekaart Blanco 28 daagse", , "Export"
End Sub

It works but i have noticed that clicking on the export button and excecuting this 'macro' it actually overrites all previous exported data.

Over the past few days i have been checking the internet to see if there is a solution for this, no luck so far.

Do you guys have an idea on how to fix this?

Thanks!

 

[Mihail]

But you don't say how you wish the data to be arranged: in another workbook ? in another sheet ? Bellow the data you already have from previous exports ? ?????

 

[Sniper-BoOyA-]

Right, so ideally the solution could be to copy the querydata below the previous exported data.

But dont i need to use Excel vba commands to look for the first empty Cell in Column A etc..

I can not just use Access VBA can i?

 

[Mihail]

I think that this discussion will help you to do what you intended to do.
Good luck !

 

[Sniper-BoOyA-]

Thanks, ill have a look!

 

[Mihail]

I'm sure

 

[Sniper-BoOyA-]

After checking that link and doing some more research regarding the Excel object within Access ive been trying to rewrite the code, but i still can not figure out how to get it to work.

Private Sub Knop312_Click()
Dim rstName As Recordset
Dim sfile As String
Set rstName = CurrentDb.OpenRecordset("Query_Controlekaart_Blanco_28d")
sfile = "P:\17\10_Cluster_17_Algemeen\98_Overige\MSH\Vliegas_Testfase\Excel bladen\Contolekaart Blanco 28 daagse.xls"
Dim objApp As Object, objMyWB As Object, objMySht As Object, objMyRng As Object
    Set objApp = CreateObject("Excel.Application")
    Set objMyWB = objApp.Workbooks.Open(sfile)
    Set objMySht = objMyWB.Worksheets("Export")
    Set objMyRng = objMySht.Cells(objMySht.UsedRange.Rows.Count + 1, 1)
    objMyRng.CopyFromRecordset rst
    objMyWB.Close SaveChanges:=True
    objApp.Quit

End Sub

At this point the button does not work at all. But i can not find why it isn't working.

Mind you, i have been looking at this code for quite some time so maybe that stops me from finding the reason why this is not working.

Any ideas?

 

[Mihail]

Use Option Explicit !

 

[Sniper-BoOyA-]

Doh!! I feel so stupid hah.

Thanks again.