VBA copy sheet to new Wkb and save as CSV

[Isabella]

Hi

I am trying to use the below code to save a worksheet as CSV, the code runs without any issues. When i open the csv file i get the below error message

"The file format differs from the format that the file name extension specifies"

How can this be rectified?

Sub SaveValues()
    Dim SourceBook As Workbook, DestBook As Workbook, SourceSheet As Worksheet, DestSheet As Worksheet
    
    Dim SavePath As String, i As Integer
    
    Application.ScreenUpdating = False
    
    Set SourceBook = ThisWorkbook
    
    '*********************************************
    'Edit next two lines as necessary
    SavePath = Range("rngpath").Value
    Set SourceSheet = SourceBook.Sheets("Sheet3")
    '*********************************************
    Set DestBook = Workbooks.Add
    Set DestSheet = DestBook.Worksheets.Add
    
    Application.DisplayAlerts = False
    For i = DestBook.Worksheets.Count To 2 Step -1
        DestBook.Worksheets(i).Delete
    Next i
    Application.DisplayAlerts = True
    
    SourceSheet.Cells.Copy
    With DestSheet.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats 'Delete if you don't want formats copied
    End With
    
    DestSheet.Name = SourceSheet.Name
    DestBook.Activate
    With ActiveWindow
        .DisplayGridlines = False
        .DisplayWorkbookTabs = False
    End With
    SourceBook.Activate
    
    Application.DisplayAlerts = False 'Delete if you want overwrite warning
    DestBook.SaveAs Filename:=SavePath
    Application.DisplayAlerts = True 'Delete if you delete other line
    
    SavePath = DestBook.FullName
    DestBook.Close 'Delete if you want to leave copy open
    MsgBox ("A copy has been saved to " & SavePath)
    
End Sub

 

[Joe4]

I believe this command:

DestBook.SaveAs Filename:=SavePath

will save your file as an Excel file. So you are probably saving an Excel file with a CSV extension which is why you are getting those messages.

If you want to save it as a CSV, use:

ActiveWorkbook.SaveAs Filename:=SavePath, FileFormat:=xlCSV, CreateBackup:=False

 

[DocAElstein]

Hi Isabella,
. This may not be a great help as I am a beginner just learning these sort of things now..
. But.

. I tried your code. It worked for me. It produced an Excel CSV File. But again on opening I got the same error as you. However by clicking on OK it opened it and it looked fine, looking exactly as it did before saving (I stepped through the code with F8 and so saw exactly wot was going on.)

. However when I opened that file with the Notepad text editor it looks very strange!!!


. For “fun” I cobbled together a new code based on yours and one that I am currently using. This also produces an Excel CSV File.

. When I opened that file with the Notepad text editor it looks as expected – a simple text csv file
. However when I open that in excel I only get the data and have lost all the formatting

…………

. As I said I am new here, but maybe the error just informs of a special format that excel recognizes in order to keep the formatting. That would explain why it looks very strange to Notepad, having some extra “Formatting code” that Notepad does not recognize. Maybe?

. I guess you want that formatting?

. If you simply want a text .csv file to be produced then maybe my code could help. Here it is:

[color=green]'[/color]
[color=darkblue]Sub[/color] SaveValuesAlan()
    [color=darkblue]Dim[/color] SourceBook [color=darkblue]As[/color] Workbook, DestBook [color=darkblue]As[/color] Workbook, SourceSheet [color=darkblue]As[/color] Worksheet, DestSheet [color=darkblue]As[/color] Worksheet
 
    [color=darkblue]Dim[/color] SavePath [color=darkblue]As[/color] [color=darkblue]String[/color], i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
 
    Application.ScreenUpdating = [color=darkblue]False[/color]
 
    [color=darkblue]Set[/color] SourceBook = ThisWorkbook
 
    [color=green]'*********************************************[/color]
    [color=green]'Edit next two lines as necessary[/color]
    [color=green]'SavePath = Range("rngpath").Value[/color]
    SavePath = ThisWorkbook.Path & "\TestIsabella.csv"
    [color=darkblue]Set[/color] SourceSheet = SourceBook.Sheets("Sheet3")
    [color=green]'*********************************************[/color]
    [color=darkblue]Set[/color] DestBook = Workbooks.Add
    [color=darkblue]Set[/color] DestSheet = DestBook.Worksheets.Add
 
    Application.DisplayAlerts = [color=darkblue]False[/color]
    [color=darkblue]For[/color] i = DestBook.Worksheets.Count [color=darkblue]To[/color] 2 [color=darkblue]Step[/color] -1
        DestBook.Worksheets(i).Delete
    [color=darkblue]Next[/color] i
    Application.DisplayAlerts = [color=darkblue]True[/color]
 
    SourceSheet.Cells.Copy
    [color=darkblue]With[/color] DestSheet.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats [color=green]'Delete if you don't want formats copied[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
 
    DestSheet.Name = SourceSheet.Name
    DestBook.Activate
    [color=darkblue]With[/color] ActiveWindow
        .DisplayGridlines = [color=darkblue]False[/color]
        .DisplayWorkbookTabs = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    SourceBook.Activate
 
[color=green]'    Application.DisplayAlerts = False 'Delete if you want overwrite warning[/color]
[color=green]'    DestBook.SaveAs Filename:=SavePath[/color]
[color=green]'    Application.DisplayAlerts = True 'Delete if you delete other line[/color]
[color=green]'[/color]
'    SavePath = DestBook.FullName
[color=green]'    DestBook.Close 'Delete if you want to leave copy open[/color]
[color=green]'    MsgBox ("A copy has been saved to " & SavePath)[/color]
[color=green]'[/color]
 
'
''   Write a CSV File: Note: the file will be overwritten if it already exists.------
     [color=darkblue]Dim[/color] Temp() [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=darkblue]Dim[/color] ColumnAfter [color=darkblue]As[/color] [color=darkblue]Long[/color], LastAfterColumn [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Variables for heading column numbers in After Sheet[/color]
    [color=darkblue]Let[/color] LastAfterColumn = DestSheet.Cells.Find(What:="*", After:=Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column [color=green]' work backwards from first cell(effectively start at last cell) to find end of data and pick out last Column[/color]
    [color=darkblue]Dim[/color] RowNumberAfter [color=darkblue]As[/color] [color=darkblue]Long[/color], LastAfterRowNumber [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Row count and final last Row in After sheet[/color]
    [color=darkblue]Let[/color] LastAfterRowNumber = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row [color=green]'Go to last cell in last column then go back up to range with an entry and return its Row Number[/color]
    [color=darkblue]ReDim[/color] Temp(1 [color=darkblue]To[/color] LastAfterColumn) [color=green]'Set its actual size with rteDim as Dim takes only nimbersm not variables[/color]
 
    [color=darkblue]Open[/color] SavePath [color=darkblue]For[/color] [color=darkblue]Output[/color] [color=darkblue]As[/color] 1 [color=green]'Open a Data "Highway to take data sent, give it name 1"[/color]
 
    [color=darkblue]For[/color] RowNumberAfter = 1 [color=darkblue]To[/color] LastAfterRowNumber [color=green]'For each row in final After Sheet[/color]
        [color=darkblue]For[/color] ColumnAfter = 1 [color=darkblue]To[/color] LastAfterColumn  [color=green]'Go through every Column in Final After sheet.[/color]
            Temp(ColumnAfter) = DestSheet.Cells(RowNumberAfter, ColumnAfter).Value
        [color=darkblue]Next[/color] ColumnAfter
        [color=darkblue]Print[/color] #1, Join(Temp, ",") [color=green]'A Row of columns in excel are jopined with a separator , to make one line which is then effectively writtedn in the CSV data File[/color]
 
    [color=darkblue]Next[/color] RowNumberAfter
 
    [color=darkblue]Close[/color] 1 [color=green]'Must allways shut "Highway" or problems may arise[/color]
   
    SavePath = DestBook.FullName
    DestBook.Close [color=green]'Delete if you want to leave copy open[/color]
    MsgBox ("A copy has been saved to " & SavePath)
    [color=darkblue]End[/color] [color=darkblue]Sub[/color]

Alan.

EDIT: I just caught Joe4’s response, which probably ties up with my findings and explains the problem.

 

[DocAElstein]

OK.

. I tried Joe4's code (or rather this to tie up with your code...........

    DestBook.SaveAs Filename:=SavePath, FileFormat:=xlCSV, CreateBackup:=[color=darkblue]False[/color]

...........)

. It produces then the same as my program, that is to say a "normal looking" csv file.

. So it all ties up......

.... I guess you need to simply disable or ignor that error warning.

 

[Joe4]

. It produces then the same as my program, that is to say a "normal looking" csv file.

. So it all ties up......

.... I guess you need to simply disable or ignor that error warning.

Are you sure?

Try the original way, then try opening the CSV file in a Text Editor like NotePad or WordPad. Is it readable?

Now try my way, then try opening the CSV file in a Text Editor like NotePad or WordPad. Is that readable?

I am guessing that the first one creates an Excel file with a CSV extension, while the second one creates a valid CSV file.
So, if that is true, both would open from Excel just fine (since Excel can open CSV or Excel files), but only the second one would open it a Text Editor (as if you open an Excel file in a Text Editor, you will get gibberish).

 

[DocAElstein]

Are you sure?

...........

Hi Joe
? I think we are in agreement arn't we?. The original produces gibberish in a text editor.
. My code and your way both look OK in a text editor, as they produce "normal .csv Text files. (And your and my way give no error warning but just upload normal text data). That the first produces a "type" of Excel file is maybe wot I meant by "the error just informs of a special format that excel recognizes in order to keep the formatting."

. As I am new and do not really know wot I am talking about maybe I explained myself badly.

. Incidentally in my Directory all 3 versions are described as "Microsoft Office Excel-CSV (.csv)" - But as we are both I think in agreement the second 2 are in fact "normal" .csv text files. Probably just because excel “produced them” so Microsoft like to add their name in the directory?
. For me I thought your explanation tied up with my findings and cleared it up nicely? I am very grateful for any advances on that, because I am keen to learn!
Thanks for the replies
Alan