title: excel macro beforeclose event
body: <p>I am using the following macro to convert text to numbers for all the values.
  Since linked server is loading the xls, the format got messed up.
  Columns are already formatted e.g, accounting, percentage, number....etc, but even though
  everything are saved as text.</p>
  
  <p>So I decided to run the macro before closiong the workbook.
  The macros goes like this:</p>
  
  <pre><code>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cells.Select
    Range("D1").Activate
    Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
    With Selection
      .VerticalAlignment = xlTop
      .WrapText = False
    End With
    ActiveWorkbook.SaveAs "test.xls"
    ActiveWorkbook.Close 
  End Sub
  </code></pre>
  
  <p>But the thing is, it pops up window to ask if I wanna save the changes,
  How can I fix that.</p>
  
  <p>Above macro takes little longer to execute!!!</p>
  
  <p>I have fixed columns(till D1) but valiable rows.</p>
  
  <p>Can somebody help me to figure this out.</p>