My Excel VBA code parse a worksheet to 3 workbooks and works without error.
However, when I try the generated workbooks I get the following message :
..
The file you are trying to open, 'C.xls' is in a different format than specified by the file extension.
Verify than the file is not corrupted and is from a trusted source before opening the file.
Do you want to open the file now ?
If I click 'Yes' the workbook opens normally with correct content.
How do I avoid the preceding annoying message ?
Help appreciated.
..
The corresponding VBA code follows :
..
Sub x()
Dim r As Long, rng As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With Sheets("Data")
Sheets.Add().Name = "Test"
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Test").Range("A1"), Unique:=True
For Each rng In Sheets("Test").Range("A2", Sheets("Test").Range("A2").End(xlDown))
.AutoFilterMode = False
.Range("A1").AutoFilter field:=1, Criteria1:=rng
Set ws = Sheets.Add
.AutoFilter.Range.Copy ws.Range("A1")
ws.Name = rng
ws.Move
ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\2013\PM4\" & rng & ".xls"
Next rng
.AutoFilterMode = False
Sheets("Test").Delete
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

On Tuesday, February 18, 2014 9:02:44 PM UTC-6, JeanPierre Charron wrote:
My Excel VBA code parse a worksheet to 3 workbooks and works without error.

However, when I try the generated workbooks I get the following message :

.

The file you are trying to open, 'C.xls' is in a different format than specified by the file extension.

Verify than the file is not corrupted and is from a trusted source before opening the file.

Do you want to open the file now ?

If I click 'Yes' the workbook opens normally with correct content.

How do I avoid the preceding annoying message ?

Help appreciated.

.

The corresponding VBA code follows :

.

Sub x()

Dim r As Long, rng As Range, ws As Worksheet

Application.DisplayAlerts = False

Application.ScreenUpdating = False

With Sheets("Data")

Sheets.Add().Name = "Test"

.Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Test").Range("A1"), Unique:=True

For Each rng In Sheets("Test").Range("A2", Sheets("Test").Range("A2").End(xlDown))

.AutoFilterMode = False

.Range("A1").AutoFilter field:=1, Criteria1:=rng

Set ws = Sheets.Add

.AutoFilter.Range.Copy ws.Range("A1")

ws.Name = rng

ws.Move

ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\2013\PM4\" & rng & ".xls"

Next rng

.AutoFilterMode = False

Sheets("Test").Delete

End With

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub
This may help to either save each sheet after making all or to see how to modify your code

Set wsbSource = ActiveWorkbook
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
Next
or one from Ozgrid
Sub ExportToWorkbooks()
Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set OldBook = ThisWorkbook

For Each sh In OldBook.Worksheets
If sh.Visible = True Then
sh.Copy
ActiveWorkbook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & "VALUES", FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

On 19/02/2014 03:02, JeanPierre Charron wrote:
My Excel VBA code parse a worksheet to 3 workbooks and works without error.
However, when I try the generated workbooks I get the following message :
.
The file you are trying to open, 'C.xls' is in a different format than specified by the file extension.
Verify than the file is not corrupted and is from a trusted source before opening the file.
Do you want to open the file now ?
If I click 'Yes' the workbook opens normally with correct content.
How do I avoid the preceding annoying message ?
Help appreciated.
.
The corresponding VBA code follows :
.
Sub x()
Dim r As Long, rng As Range, ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
With Sheets("Data")
Sheets.Add().Name = "Test"
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Test").Range("A1"), Unique:=True
For Each rng In Sheets("Test").Range("A2", Sheets("Test").Range("A2").End(xlDown))
.AutoFilterMode = False
.Range("A1").AutoFilter field:=1, Criteria1:=rng
Set ws = Sheets.Add
.AutoFilter.Range.Copy ws.Range("A1")
ws.Name = rng
ws.Move
ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\2013\PM4\" & rng & ".xls"
Next rng
.AutoFilterMode = False
Sheets("Test").Delete
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I'll hazard a guess that you are using XL2007 or later and the workbook
is being saved in the new ZIP encapsulated XLSX or XLSM format but with
the old XL97 type file extension ".XLS" that you have forced on it.

--
Regards,
Martin Brown

On Tuesday, February 18, 2014 10:02:44 PM UTC-5, JeanPierre Charron wrote:
My Excel VBA code parse a worksheet to 3 workbooks and works without error.