Export from Access Creates new Worksheet instead of Updating

[proballin]

I have a macro in Access that should update my worksheet called "Sum". However instead of updating the sheet it creates a new worksheet called "Sum1".

In Access I am using the TransferSpreadsheet operation for the Macro.

Any clue as to why it will not update the existing "Sum" worksheet but instead creates one called "Sum1"?

 

[boblarson]

TransferSpreadsheet does that. It creates a new sheet, so if there is one already with the name it would use, it creates one with an appended number on.

If you want to update an existing sheet, you should use Excel Object Programming like:

Dim appXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set appXL = CreateObject("Excel.Application")
Set xlWB = appXL.Workbooks.Open("yourFileNameAndPathHere")

Set xlWS = xlWB.Worksheets("Sum")

...etc. and then you can either use a recordset to write out the values by iterating through, or use the xlWS.CopyFromRecordst rst code to do so.

See if that gets you on your way. If you need some extra help, take a look at this code of mine which exports using this method.

 

[MailMan]

I haven't tried this from a macro, but in VB if you let access create the worksheet the first time, it will overwrite the worksheet each time after that.

 

[proballin]

You are right MailMan...I did that and it all works out now. Thanks!