Code execution is correct but generates Error :The file you aretrying to open is in a different form

[JeanPierre Charron]

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

 

[Don Guillett]

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

 

[Martin Brown]

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.

 

[bobflanagan1]

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 beforeopening 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

Most likely the file is a different format. Try opening with a text editorand see if it looks like a text file. Which means that when saved, someone specified a file type when typing the file name and in the type of file specified a different type. Or, you have display file types turned off and the real type is something.xls.csv for example. If you have lots of these to process, then put Application.DisplayAlerts = False immediately before opening, and then set to true immediately after opening.

Robert Flanagan
Add-ins.com LLC
Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel