vba help - save workbook in csv without changing sheet name - issue

[Mallesh23]

Hi Team,

I am trying to save a workbook in csv format.
Macro saves workbook into csv format but also Changes sheet Name to Recon_.

From Sheet1 to Recon_ . I want name should not get changed.


Below is a code

If Dir(ThisWorkbook.Path & "\output", vbDirectory) = "" Then
        MkDir ThisWorkbook.Path & "\output"
End If
    
    Application.DisplayAlerts = False
    On Error Resume Next
        wbSource.SaveAs ThisWorkbook.Path & "\output\Recon_" & Format(date, "Mmmm_YY") & ".csv", 6
    On Error GoTo 0
    Application.DisplayAlerts = True

Thanks
mg

 

[Yongle]

Try this

If Dir(ThisWorkbook.Path & "\output", vbDirectory) = "" Then
        MkDir ThisWorkbook.Path & "\output"
End If

   On Error Resume Next
        wbSource.SaveAs ThisWorkbook.Path & "\output\Recon_" & Format(date, "Mmmm_YY") & ".csv", 6
        wbSource.Sheets(1).Name = "Sheet1"
        wbSource.Save
   On Error GoTo 0
    Application.DisplayAlerts = True

 

[Fluff]

The sheet name of a csv is the same as the file name, you cannot change it.

 

[Yongle]

or this

       If Dir(ThisWorkbook.Path & "\output", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\output"
End If

Application.DisplayAlerts = False
On Error Resume Next
wbSource.SaveAs ThisWorkbook.Path & "\output\Recon_" & Format(date, "Mmmm_YY") & ".csv"
On Error GoTo 0
Application.DisplayAlerts = True

 

[Mallesh23]

Hi Fluff and Yongle,

Yes, there is a issue with csv file,
Unable to change sheet name and also I am losing formatting inside it.


Thanks
mg

 

[Yongle]

Apologies - I did not realise that amending the sheet name with VBA would cause other problems
@Fluff knows more than I do about this and he says " The sheet name of a csv is the same as the file name, you cannot change it. "

If that is so, there is nothing you can do

 

[Mallesh23]

Hi Yongle and Fluff,

Thanks for the information otherwise I would have spent more time on this.


Thanks
mg

 

[Fluff]

I did not realise that amending the sheet name with VBA would cause other problems

It doesn't

also I am losing formatting inside it.

A csv is a simple text file & as such cannot hold formatting, formulae or anything other than straight text.

 

[Yongle]

@Fluff - as it happens I was not referring to that
When I opened one of the files my code created (which did in fact allow the sheet name to be changed) ... Excel told me that it was not happy with the file
Although it looked OK on the surface, I presumed it was because Excel did not like the wrong sheet name
So I amended the sheet name to be the same as the file name, saved it, closed it and reopened it - Excel no longer complains
Which appears to reinforce your comment

 

[Fluff]

That's odd, you should be able to change the sheet name, save, close & re-open without any problem. It's just that the sheet name will revert back to the file name.