Access Table to Excel not overwriting existing sheet in Excel.

[robertadamharper]

I have below some code that opens excel and imports a sheet from it.

I now need a bit of code inserted into the below code that transfers a
table from access ("Material_Demand_18Mths") to excel, overwriting the
existing sheet in there. Then my code below will take it back out
again.

Existing code:-

Option Compare Database

Function AutomateExcelProvision_Other()
Dim xl As Object
Dim SourceFile As String
Set xl = CreateObject("Excel.Application")

SourceFile = "C:\Documents and Settings\harperro\Desktop
\SAP_Planner_SAVED\x RECAL_MODEL_DEMAND_Linked.xls" '

xl.Workbooks.Open SourceFile
xl.Visible = False
xl.Wait (2000)
xl.Save
xl.DisplayAlerts = False
xl.Application.Quit

Set xl = Nothing

End Function

 

[Ken Snell \(MVP\)]

No need to use Automation. See TransferSpreadsheet in Help file -- can be
run via macro or via VBA programming.

This site gives some additional info about the export feature of
TransferSpreadsheet:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html