VBA Code To Export To .CSV File Type

K

kkoruni

New Member
Joined
Jul 18, 2017
Messages
23
Hello,
The goal is to export an excel tab into a .csv fyle type, and then user can save this .csv file type with a name they choose. If this is not possible, then is there an automatic way to same to .csv file type based on file name that exporting tab came from? I have watched many videos but I got my head spinning. Any help is greatly appreciated.

I am in need of your help with below vba. I am able to export tab with ease.
However, I want to be able to export it as .csv file type. Is there a vba code that allows me to export as .csv excel file type? If no, then is there a way to export and automatic save as .csv file type on a desktop folder?

Private Sub CommandButton1_Click()
Sheets("PROF_Data_Capture").Select
ActiveSheet.Unprotect
Sheets("PROF_Data_Capture").Copy
Application.Range("A1:B36").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.Shapes("CommandButton1").Select
Application.CutCopyMode = False
Selection.Delete
Application.CommandBars("Control Toolbox").Visible = False
Application.Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Maybe something like this.
VBA Code: 
Private Sub CommandButton1_Click()
    Dim WB_CSV As Workbook
    Dim MyFileName As String, MyFolder As String

    MyFileName = InputBox("Enter filename for CSV file", "Export to CSV")

    If MyFileName <> "" Then
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select a folder"
            .InitialFileName = vbNullString
            If .Show = -1 Then
                MyFolder = .SelectedItems(1)
            End If
        End With

        If MyFolder <> "" Then
            MyFileName = Split(MyFileName, ".")(0) & ".csv"
            With Sheets("PROF_Data_Capture")
                .Unprotect
                .Copy
                Set WB_CSV = ActiveWorkbook
                .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            End With

            With WB_CSV.Worksheets(1)
                .Range("A1:B36").Value = .Range("A1:B36").Value
            End With

            Application.DisplayAlerts = False
            WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
            Application.DisplayAlerts = True
            WB_CSV.Close False

            MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
                 & MyFolder, vbOKOnly Or vbInformation, "File Export"
        End If
    End If
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above
www.mrexcel.com

How to Post Your VBA Code

Post your VBA code.
www.mrexcel.com www.mrexcel.com
as it makes the code easier to read.)
 
Upvote 0
rlv01 said:
Maybe something like this.
VBA Code: 
Private Sub CommandButton1_Click()
    Dim WB_CSV As Workbook
    Dim MyFileName As String, MyFolder As String

    MyFileName = InputBox("Enter filename for CSV file", "Export to CSV")

    If MyFileName <> "" Then
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select a folder"
            .InitialFileName = vbNullString
            If .Show = -1 Then
                MyFolder = .SelectedItems(1)
            End If
        End With

        If MyFolder <> "" Then
            MyFileName = Split(MyFileName, ".")(0) & ".csv"
            With Sheets("PROF_Data_Capture")
                .Unprotect
                .Copy
                Set WB_CSV = ActiveWorkbook
                .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            End With

            With WB_CSV.Worksheets(1)
                .Range("A1:B36").Value = .Range("A1:B36").Value
            End With

            Application.DisplayAlerts = False
            WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
            Application.DisplayAlerts = True
            WB_CSV.Close False

            MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
                 & MyFolder, vbOKOnly Or vbInformation, "File Export"
        End If
    End If
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above
www.mrexcel.com

How to Post Your VBA Code

Post your VBA code.
www.mrexcel.com www.mrexcel.com
as it makes the code easier to read.)
Click to expand...
Thank you so much. This works very well. I am very impressed.
The only addition or help I would ask is to identify the folder path automatically were the exported tab should be saved.
The intent is that once I enter the filename for CSV File, I want the tool to take me to the folder automatically. Did I miss the folder or file path on your vba? I hope I did not.

Thank you so much.
 
Upvote 0
kkoruni said:
Thank you so much. This works very well. I am very impressed.
The only addition or help I would ask is to identify the folder path automatically were the exported tab should be saved.
Click to expand...
What do you mean by "automatically"? Information like that needs a source and I cannot tell you what that is for your situation.

kkoruni said:
The intent is that once I enter the filename for CSV File, I want the tool to take me to the folder automatically. Did I miss the folder or file path on your vba? I hope I did not.

Thank you so much.
Click to expand...

In the posted example code, "MyFolder" is the variable for the folder. The code I posted asks the user to choose the folder (path). You can modify it to use another source, assuming you have one, to obtain a value for "MyFolder".
 
Upvote 0
rlv01 said:
What do you mean by "automatically"? Information like that needs a source and I cannot tell you what that is for your situation.



In the posted example code, "MyFolder" is the variable for the folder. The code I posted asks the user to choose the folder (path). You can modify it to use another source, assuming you have one, to obtain a value for "MyFolder".
Click to expand...

Hello,
I tried to do more automation work so that when user clicks export button, it will not ask to choose file name. As far as folder yes I do have a file source but we have 3 entries of My Folder. Where do I enter the folder path? Folder path is: C:\My Data\Policies\Employees. Can you please let me know where to enter it? Sorry but struggled trying to figure it out. I tried to make the changes but did not work. Any help is appreciated.

For the file name I modified it to to look up Cell B2 as it contains the file name. Once vba is modified it takes me to choose a folder. Once I choose a folder, the file name is not correct. The file name is "True". So I am thinking I am missing a True and False statement but I do not know where to enter it. Can you please help me with this as well? Below is the vba code I used.

MyFileName = Sheets("PROF_Data_Capture").Range("B2").Select


Thank you,
kkoruni
 
Upvote 0
kkoruni said:
Below is the vba code I used.

MyFileName = Sheets("PROF_Data_Capture").Range("B2").Select
Click to expand...

It should be something more like this:

VBA Code: 
MyFileName = Sheets("PROF_Data_Capture").Range("B2").Value

So:
VBA Code: 
Private Sub CommandButton1_Click()
    Dim WB_CSV As Workbook
    Dim MyFileName As String, MyFolder As String

    'MyFileName = InputBox("Enter filename for CSV file", "Export to CSV")
    MyFileName = Trim(Sheets("PROF_Data_Capture").Range("B2").Value)
    MyFolder = "C:\My Data\Policies\Employees"

    If MyFileName <> "" Then
        With CreateObject("Scripting.FileSystemObject")
            If Not .FolderExists(MyFolder) Then
                MsgBox "Folder not found:" & vbCr & MyFolder, vbOKOnly Or vbExclamation, "File Folder Error"
                Exit Sub
            End If
        End With

        MyFileName = Split(MyFileName, ".")(0) & ".csv"
        With Sheets("PROF_Data_Capture")
            .Unprotect
            .Copy
            Set WB_CSV = ActiveWorkbook
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With

        With WB_CSV.Worksheets(1)
            .Range("A1:B36").Value = .Range("A1:B36").Value
        End With

        Application.DisplayAlerts = False
        WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
        Application.DisplayAlerts = True
        WB_CSV.Close False

        MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
             & MyFolder, vbOKOnly Or vbInformation, "File Export"
    End If
End Sub
 
Upvote 0
Solution
rlv01 said:
It should be something more like this:

VBA Code: 
MyFileName = Sheets("PROF_Data_Capture").Range("B2").Value

So:
VBA Code: 
Private Sub CommandButton1_Click()
    Dim WB_CSV As Workbook
    Dim MyFileName As String, MyFolder As String

    'MyFileName = InputBox("Enter filename for CSV file", "Export to CSV")
    MyFileName = Trim(Sheets("PROF_Data_Capture").Range("B2").Value)
    MyFolder = "C:\My Data\Policies\Employees"

    If MyFileName <> "" Then
        With CreateObject("Scripting.FileSystemObject")
            If Not .FolderExists(MyFolder) Then
                MsgBox "Folder not found:" & vbCr & MyFolder, vbOKOnly Or vbExclamation, "File Folder Error"
                Exit Sub
            End If
        End With

        MyFileName = Split(MyFileName, ".")(0) & ".csv"
        With Sheets("PROF_Data_Capture")
            .Unprotect
            .Copy
            Set WB_CSV = ActiveWorkbook
            .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With

        With WB_CSV.Worksheets(1)
            .Range("A1:B36").Value = .Range("A1:B36").Value
        End With

        Application.DisplayAlerts = False
        WB_CSV.SaveAs Filename:=MyFolder & "\" & MyFileName, FileFormat:=xlCSV
        Application.DisplayAlerts = True
        WB_CSV.Close False

        MsgBox "CSV file " & MyFileName & " created in folder:" & vbCrLf & vbCrLf _
             & MyFolder, vbOKOnly Or vbInformation, "File Export"
    End If
End Sub
Click to expand...
RLV01 I cannot thank you enough.
Truly appreciate it.

Thank you,
kkoruni
 
Upvote 0
rlv01 said:
Glad I could help.
Click to expand...
Hello RLV01.
On the vba you helped me with I need additional assistance to incorporate below vba code. This code will copy and paste values in the format they are.
Do you know where I can add it or if you have a better vba code to incorporate?
Selection.PasteSpecial Pas
te:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
Upvote 0
That is a new problem that is not related to the problem in this thread (exporting to CSV file), so you should create a new thread for this new problem. That way, other people who may have the same problem will be able to locate it more easily.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
CSV export to remove column
Replies
1
Views
650
StellarRumble59
S
C
  • Question Question
Excel VBA CSV export query
Replies
1
Views
573
cerberus1845
C
T
  • Question Question
Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"
Replies
1
Views
391
Tanquen
T
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
128
brentcole
B
W
  • Solved
Modify Column format before saving as csv file
2
Replies
12
Views
1K
WascoWarrior
W

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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