Saving XLSB to CSV

FACTOR 21

Board Regular
Joined
Mar 3, 2014
Messages
110
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
You are welcome.

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

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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
Back
Top