VBA Code To Export To .CSV File Type

[kkoruni]

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

 

[rlv01]

Maybe something like this.

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

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

 

[kkoruni]

Maybe something like this.

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

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.)

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.

 

[rlv01]

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.

What do you mean by "automatically"? Information like that needs a source and I cannot tell you what that is for your situation.

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.

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".

 

[kkoruni]

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".

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

 

[rlv01]

Below is the vba code I used.

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

It should be something more like this:

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

So:

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

 

[kkoruni]

It should be something more like this:

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

So:

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

RLV01 I cannot thank you enough.
Truly appreciate it.

Thank you,
kkoruni

 

[rlv01]

Glad I could help.

 

[kkoruni]

Glad I could help.

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

 

[rlv01]

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.