title: VBA - force excel to save in custom format
body: <p>For a project at work i would like to allow the user to create an export of data to be saved for a single project, since it will not be possible to save my main workbook for each project.</p>
  
  <p>I have created a function that exports the data to another workbook, but now i want to make sure that the user won't change the exported data directly (By setting another file format, i think the users would think more about opening the workbook, and therefore not do it by "accident")</p>
  
  <p>So my question is:
  Can i force the user to save the file as a .clg file?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I solved the problem by saving the file with <code>.SaveAs</code> and specifying the format as "51" (The same as .xlsx"). I used <code>GetSaveAsFilename</code> to allow the user to decide the location (i.e. the project folder), and only browse for .clg files. </p>
  
  <pre><code>Dim sFullName As String
  sFullName = exportFile.Application.GetSaveAsFilename(thisFile.Sheets("Project information").Cells(1, 2).Value, "Export files (*.clg),*.clg", , "Choose export location", "Export")
  If Not Len(sFullName) = 0 Or sFullName = "False" Then
  
      exportFile.SaveAs Filename:=sFullName, FileFormat:=51
  
  End If
  
  exportFile.Close
  </code></pre>
  
  <p>If someone wants to use Mr. Monshaw's approach, one way to go is to create a class module, which has a variable withevents - which means that any event that fires in the variable, fires in your module.</p>
  
  <pre><code>Public WithEvents wbk As Workbook
  </code></pre>
  
  <p>Just remember to keep the instance of the class in memory, for example by adding it to a collection.</p>
  
  <p>Another way to use the <code>BeforeSave</code> is to dynamically write the code, see <a href="http://www.ozgrid.com/forum/showthread.php?t=163903" rel="nofollow">http://www.ozgrid.com/forum/showthread.php?t=163903</a></p>