CODEWITHSUNDEEP
vbaexcel
Kentora
Kentora

Reputation: 106

VBA - force excel to save in custom format

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.

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")

So my question is: Can i force the user to save the file as a .clg file?

Edit:

I solved the problem by saving the file with .SaveAs and specifying the format as "51" (The same as .xlsx"). I used GetSaveAsFilename to allow the user to decide the location (i.e. the project folder), and only browse for .clg files. 

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

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.

Public WithEvents wbk As Workbook

Just remember to keep the instance of the class in memory, for example by adding it to a collection.

Another way to use the BeforeSave is to dynamically write the code, see http://www.ozgrid.com/forum/showthread.php?t=163903

Upvotes: 1

Views: 4578

Answers (2)

NickSlash
NickSlash

Reputation: 5077

Is 'clg' a format you use? or just a random not xls/x extension?

If the file does need to be viewed and viewed in excel (guessing your not exporting windows catalogue files?) then changing the extension might not be the best move.

If you create your export using VBA, then you can automate some things to make it more apparent to the users that they should not be editing the data.

The Workbook.SaveAs method has the option for "ReadOnlyRecommended" which will inform the user that the workbook should be opened read-only when opening it. (its optional though, so they could ignore it)

Assuming the data is always read-only you could also lock the workbook so that no changes can be made.

Update

If you want to save an Excel Workbook with a custom extension (and not as a custom format) then using the Workbook.SaveAs method will work. ex: ThisWorkbook.SaveAs "Report.clg"

You'll either need to have your export code do this for you, or if its a more manual process you can just create a macro with a button to invoke the save-as for you instead of using the standard save-as dialogue.

If you want to save as a custom format, and it is not one of the ones supported by Workbook.SaveAs (supported formats) you will have to do as @Mr.Monshaw suggested and watch for the onbeforesave and generate the file manually. (unlikely someone has done an excel to windows catalogue converter)

Upvotes: 1

Mr.Monshaw
Mr.Monshaw

Reputation: 450

im not sure what the constant for clg would be but the fileformat property of SaveAs in vba would allow you to decide what format to save in

i saw a clever solution to something similar to this if you can find the fileformat, just add a macro to your book with a workbook event like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Call SaveAsCLG(SaveAsUI)          'set save type
    Cancel = True                                 'cancel user save

End Sub

that will override a user save-as request

Upvotes: 0

Related Questions