I'm stuck with this procedure:

Today = Format(Date, "dd-mm-yyyy")
Application.DisplayAlerts = False
Set DestinationWb = Workbooks.Add
ThisWorkbook.Sheets("MySheet").Copy Before:=DestinationWb.Sheets(1)
DestinationWb.Sheets("Foglio1").Delete
Application.DisplayAlerts = True
DestinationWb.SaveAs Application.GetSaveAsFilename(InitialFileName:=LCase(Environ("Path")) & "\" & Today & "_MyName.dif", FileFilter:="Formato interscambio dati (*.dif), *.dif", Title:="My Data Export")
DestinationWb.Close

It works and it creates a file with .dif extension but for some reasons the destination file content gets encripted and I can't read or reuse it.

Anyone has ever experienceed something similar? how to solve?

CodePudding user response：

The file doesn't get ruined - it's simply not stored in the file format you expect. You don't tell Excel to store it in a specific format (in your case likely in Data Interchange format). Just by giving a file extension doesn't advice the SaveAs to change the format, so Excel will store the file as regular Excel file.

You can check this by renaming your saved file to .xlsx and try to open it with Excel - it should work.

You will need to tell the SaveAs the format you want using the FileFormat-parameter. File formats and the named constants are described here: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat. In your case, you need probably xlDIF.

Dim fileName
fileName = Application.GetSaveAsFilename(InitialFileName:=LCase(Environ("Path")) & "\" & Today & "_MyName.dif", FileFilter:="Formato interscambio dati (*.dif), *.dif", Title:="My Data Export")
DestinationWb.SaveAs filename, FileFormat:=xlDIF