Saving one sheet as CSV and naming sheet based on cell value

[DanaS85]

Hi

I'm very new to VBA and very stuck on a VBA code and I hope you can help.

I have a workbook, with multiple sheets, where one sheet named "Label" needs to be exported as a UTF8 csv file to a specific folder. At the same the sheet is to be named based on a cell value. The csv is to be used in Illustrator.

I've tried the following VBA that does all of the above, but won't load in Ilustrator:

Sub sb_Copy_Save_Worksheet_As_Workbook()
Application.DisplayAlerts = False
Dim strSourceSheet As String
Dim strFullname As String

strSourceSheet = "Label"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=("C:\test\") & Range("B2").Text, _
FileFormat:=xlCSVUTF8, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

But when I use the following VBA, it will load in Illustrator, but then the sheet can only be named test.

Sub sb_Copy_Save_Worksheet_As_Workbook()
Application.DisplayAlerts = False
Dim strSourceSheet As String
Dim strFullname As String

strSourceSheet = "Label"
strFullname = "C:\test\test.csv"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, _
FileFormat:=xlCSVUTF8, _
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

I can't figure out why one VBA works and the other does not, when they are both saved as a UTF8csv. Is it because, when I use the second VBA i name the file test.csv?

Hope it makes sense.

Kind regards, Dana

 

[DanaS85]

I think I found the problem, but have no idea how to fix it.

With the second VBA code, the file is saved with .CSV instead of .csv. When I use excel 2010, it saves with lowercase exstension, however using excel 2013, saves with uppercase. Is there any way to fix that, since it is only possible to save as csvUTF8 is in 2013?

 

[DanaS85]

Well I think I solved it. I changed the extension to lowercase (.csv) manually on the saved file in the specific folder, and when I ran the code again, it replaced the previous file, but retained the lowercase extension.