The following code produces a .csv that claims "The file you are trying to open is in a different format"

[RockandGrohl]

Hi all, wrote this quickly today so that a user can export a mailing list to a new workbook, copy everything across, then save in a destination.

Sub export_click()

Application.ScreenUpdating = False

Dim camnam As String

Set summ = Worksheets("Summaries")
Set df = Worksheets("Datafeed")

camnam = InputBox("Please enter a temporary campaign name for the purposes of exporting the Mailing List", "Campaign Name")

df.Activate

lastrowdf = Cells(Rows.Count, "A").End(xlUp).Row

Range("a1").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Activate
Loop

endcol = ActiveCell.Column
collet = Split(Cells(1, endcol + 10).Address, "$")(1)



Set newWB = Workbooks.Add
Sheets("Sheet1").Name = "Mailing List"
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete
Application.DisplayAlerts = True

df.Range("A1:" & collet & lastrowdf).Copy
Sheets("Mailing List").Range("A1").PasteSpecial xlPasteColumnWidths
Sheets("Mailing List").Range("A1").PasteSpecial xlPasteValues

Range("BC2:BC" & Cells(Rows.Count, "BC").End(xlUp).Row).NumberFormat = "DDD dd MMMM"

Range("A1").Select
Range("A1").Activate

newWB.SaveAs "\\chw-dc03\company\Sales\Mailing List Creation & Reporting\Campaigns\NH Test\2020 NH Mailing Lists\" & camnam & " - " & Format(Now(), "yyyy-mm-dd") & ".csv"

Application.ScreenUpdating = True

End Sub

What's happening is that the file is produced and saved as normal, but when any user tries to open it, a message is displayed stating that "This file you are trying to open [filename] is in a different format than specified by the file extension... etc"

This only happens with files produced this way, and I am curious as to why this is happening.

If I press "Yes" to open the file, it opens with no problems and no apparent errors.


I'm stuck here and from what I can see online there aren't any people that are producing csv's this way and getting the error.

 

[RockandGrohl]

Fixed, like this:

Sub export_click()

Application.ScreenUpdating = False

Dim SaveDir, camnam As String

Set summ = Worksheets("Summaries")
Set df = Worksheets("Datafeed")

camnam = InputBox("Please enter a temporary campaign name for the purposes of exporting the Mailing List", "Campaign Name")

df.Activate

lastrowdf = Cells(Rows.Count, "A").End(xlUp).Row

Range("a1").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Activate
Loop

endcol = ActiveCell.Column
collet = Split(Cells(1, endcol + 10).Address, "$")(1)



Set newWB = Workbooks.Add
Sheets("Sheet1").Name = "Mailing List"
Application.DisplayAlerts = False
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete
Application.DisplayAlerts = True

df.Range("A1:" & collet & lastrowdf).Copy
Sheets("Mailing List").Range("A1").PasteSpecial xlPasteColumnWidths
Sheets("Mailing List").Range("A1").PasteSpecial xlPasteValues

Range("BC2:BC" & Cells(Rows.Count, "BC").End(xlUp).Row).NumberFormat = "DDD dd MMMM"

Range("A1").Select
Range("A1").Activate

SaveDir = "\\chw-dc03\company\Sales\Mailing List Creation & Reporting\Campaigns\NH Test\2020 NH Mailing Lists\"

newWB.SaveAs SaveDir & camnam & " - " & Format(Now(), "yyyy-mm-dd") & ".csv", xlCSV

Application.ScreenUpdating = True

End Sub

I wasn't using the .SaveAs properly, so I was just naming it .csv which was making it a fake csv.