tobybrid
06-28-2016, 01:10 PM
I get .csv report everyday through email so I coded VBA to save a copy to the folder. And I actually want it in .xlsx format and I'm trying to code the script able to change the format too.
But the file can't be opened and error message pop up.
"Excel cannot open the file 'filename' because the file format or file extension is not valid. Verify that file has not been corrupted and that the file extension matches the format of the file"
I actually hard coded to save as .xlsx but didn't change the format and that's why I'm having this error. I can think of a solution but that seems like there is a lot of redundancy.
First save the .csv file and open that file with excel application. then saveAs .xlsx format. finally delete the .csv file.
Please advise if there is any alternative.
Appreciate that.
Public Sub SaveAttachementAsXSLX(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim FileName As String
saveFolder = "filePath"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName & ".xlsx"
Set objAtt = Nothing
Next
End Sub
But the file can't be opened and error message pop up.
"Excel cannot open the file 'filename' because the file format or file extension is not valid. Verify that file has not been corrupted and that the file extension matches the format of the file"
I actually hard coded to save as .xlsx but didn't change the format and that's why I'm having this error. I can think of a solution but that seems like there is a lot of redundancy.
First save the .csv file and open that file with excel application. then saveAs .xlsx format. finally delete the .csv file.
Please advise if there is any alternative.
Appreciate that.
Public Sub SaveAttachementAsXSLX(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim FileName As String
saveFolder = "filePath"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName & ".xlsx"
Set objAtt = Nothing
Next
End Sub