CODEWITHSUNDEEP
vbaexcelrenamecorrupt
user2242660
user2242660

Reputation: 35

xls to xlsx conversion using vba name statement corrupts the file

I am trying to rename a file from abcd.xls to xyz.xlsx using the following code-

  NumOfAttachments = Range("RecCount").Value
  DestinationFolderPath = Range("destinationfolder").Value
  NewShtName = "Sheet1"

  If NumOfAttachments <> 0 Then
    For X = 0 To NumOfAttachments - 1
      OrigName = Range("Startcell").Offset(X, 1).Value
      NewName = Range("startcell").Offset(X, 2).Value
      SourceFolderPath = Range("startcell").Offset(X, 3).Value
      NewFile = DestinationFolderPath & NewName
      If Dir(DestinationFolderPath & OrigName) <> "" Then Kill DestinationFolderPath & OrigName
      FileCopy SourceFolderPath & OrigName, DestinationFolderPath & OrigName

      If Dir(NewFile) <> "" Then Kill NewFile
      Name DestinationFolderPath & OrigName As NewFile

After this when I try to open the file(xyz.xlsx) I get the following error-

excel cannot open the file xyz.xlsx because the file format or file extension is not valid

Upvotes: 1

Views: 12985

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The reason is very simple. You are not using the correct file format.

Your .SaveAs code should look like this

.SaveAs "\myserver\test\xyz.xlsx", FileFormat:=51

See the below table for File Formats

50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

Would recommend this link

Upvotes: 10

Related Questions