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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
497
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
497
Office Version
  1. 2010
Platform
  1. Windows
Fixed, like this:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,319
Messages
5,595,465
Members
413,992
Latest member
CSEGUIN1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top