Saving the contents of a worksheet table to an xml document when theuser closes the workbook.

[R Tanner]

How do you save the contents of a worksheet to an xml document when a
user closes the workbook. My first attempt was the following, but it
didn't work...

If ActiveWorkbook.Close Then
MsgBox "Would you like to close the workbook?", vbOKCancel
ActiveWorkbook.XmlMaps("dataroot_Map").Export URL:="Q:\Workforce
\Test.xml"
Sheets("Daily Workforce Data").Range("K1").Value = "=now()"
End If

 

[Bernie Deitrick]

Robin,

Try using the before-close event, along the lines of

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.XmlMaps("dataroot_Map").Export URL:="Q:\Workforce\Test.xml"
'Choose one of the two following lines
Sheets("Daily Workforce Data").Range("K1").Formula = "=now()" 'Puts in a formula
Sheets("Daily Workforce Data").Range("K1").Value = Now 'Puts in a value

End Sub


Choose one of the two lines setting K1 - your left hand side was putting in a value, but the right
hand side was a formula....

Put the code into the codemodule of the ThisWorkbook object.

HTH,
Bernie
MS Excel MVP