Saving XLSB to CSV

FACTOR 21

Board Regular
Joined
Mar 3, 2014
Messages
95
Hi
I currently use the code below to save a XLSB worksheet to same file location and add the date of save to the File Name, I now require the file to be converted to a CSV and saved in same file path with Date added, is this possible please

VBA Code:
Sub Save_Workbook()
Dim FullPath As String
Dim temp As String
Dim extn As String

Dim i As Integer
FullPath = ActiveWorkbook.FullName
If InStr(1, FullPath, Format(Now(), "dd-mm-yyyy")) >= 1 Then GoTo skip

i = InStr(1, FullPath, ".")
temp = Left(FullPath, i - 1)
extn = Mid(FullPath, i, (Len(FullPath) - i) + 1)
FullPath = temp & " " & Format(Now(), "dd-mm-yyyy") & extn
skip:
ActiveWorkbook.SaveAs Filename:= _
        FullPath _
        , FileFormat:=xlExcel12, CreateBackup:=False


End Sub

Regards
Jeremy
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
Turn your Macro Recorder on, and record yourself saving the file as a CSV.
Then stop the Macro Recorder and review your code. You should be able to pull the "Save" command from that, and drop it into your code, and then change the Filename to use the variable you already created.
 

FACTOR 21

Board Regular
Joined
Mar 3, 2014
Messages
95
Turn your Macro Recorder on, and record yourself saving the file as a CSV.
Then stop the Macro Recorder and review your code. You should be able to pull the "Save" command from that, and drop it into your code, and then change the Filename to use the variable you already created.
Hi Joe

I've tried changing FileFormat:=xlExcel12, to FileFormat:=xlCSV, after checking the macro but the save does not convert to a CSV

Thanks
Jeremy
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
What is your value for "extn"?
Are you changing that to "CSV"?
If not, you are saving as a CSV but with an Excel extension.
 

FACTOR 21

Board Regular
Joined
Mar 3, 2014
Messages
95

ADVERTISEMENT

What is your value for "extn"?
Are you changing that to "CSV"?
If not, you are saving as a CSV but with an Excel extension.

Hi Joe

The code changed as follows;

VBA Code:
Sub Save_Workbook()
Dim FullPath As String
Dim temp As String
Dim extn As String

Dim i As Integer
FullPath = ActiveWorkbook.FullName
If InStr(1, FullPath, Format(Now(), "dd-mm-yyyy")) >= 1 Then GoTo skip

i = InStr(1, FullPath, ".")
temp = Left(FullPath, i - 1)
extn = Mid(FullPath, i, (Len(FullPath) - i) + 1)
FullPath = temp & "\" & Format(Now(), "dd-mm-yyyy") & extn
skip:
ActiveWorkbook.SaveAs Filename:= _
        FullPath _
        , FileFormat:=xlCSV, CreateBackup:=False


End Sub

Regards
Jeremy
 
Last edited by a moderator:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
The code changed as follows;
You are trying to pull the file extension from the existing file, which is "XLSB". So that won't work.
You need to change the file extension to ".CSV".

So change this line:
VBA Code:
extn = Mid(FullPath, i, (Len(FullPath) - i) + 1)

to:
VBA Code:
extn = ".csv"
 

FACTOR 21

Board Regular
Joined
Mar 3, 2014
Messages
95
You are trying to pull the file extension from the existing file, which is "XLSB". So that won't work.
You need to change the file extension to ".CSV".

So change this line:
VBA Code:
extn = Mid(FullPath, i, (Len(FullPath) - i) + 1)

to:
VBA Code:
extn = ".csv"

Hi Joe

Many thanks for your help, this works for me now

Best
Jeremy
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

I hope it makes sense to you, why we had to change what we did.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,345
Messages
5,624,127
Members
416,012
Latest member
rockermom59

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