Results 1 to 8 of 8

Thread: Save Excel 2010 File In CSV Format VBA

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0

    Save Excel 2010 File In CSV Format VBA

    I am using EXCEL 2010 in windows 7.

    I have a simple file

    I need to do these things via VBA.

    Copy Selected Range !A1:Q251
    Ask an Inputbox for the file name
    Save As file in CSV format in the below location
    C:\Users\Mag\Desktop\Revenue 2012
    Needs to create Converted folder, if not exists.

    Appreciate all your help

    Mag

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Please download below file with the solution.

    SaveDataAsCSV.xlsm

    Change code accordingly.

  3. #3
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Thanks For your help .. but i need to do some changes. i need to put only Name .. Select Range option Should be in VBA Code Eg A1:C20 no need to prompt msg Box to Select Range

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Replace previous GetData Procedure with this one

    Code:
    Sub GetData()
    
        Dim rngDataRange                As Range
        
        Set rngDataRange = ActiveSheet.Range("A1:C20")
        
        If Not rngDataRange Is Nothing and rngDataRange.Rows.Count > 0 Then
            DataSaveAs rngDataRange, XL_CSV
        End If
        
        Set rngDataRange = Nothing
         
    End Sub

    Change Range accordingly.

  5. #5
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Thanks Buddy.. You are the Man

  6. #6
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    Hi Can U Please Help me to Change The file Accordingly Please Find the Attached Sample File.

    I need Do Some Changes For Current VBA Which i Using to Change File Path , File Name, File Range if i Change from the Current Sheet Example E1,E2,E3 When I change E1 it should Change file Path When I Change E2 it Should Change File Range When I Change E3 it Should Change File Name

    Also When i Save File to CSV Date Should Be Same As Shown 01/07/2013

    SaveDataAsCSV.xlsm


    Thanks
    Regards
    Mag
    Last edited by mag; 01-08-2013 at 08:10 PM. Reason: if E3 it Should Change File Range changed to File Name

  7. #7
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Please change the old procedure DataSaveAs with this one

    Code:
    Private Sub DataSaveAs(ByVal strFilePath As String, ByVal strFileName As String, ByVal strShtName As String, ByVal strDataRange As String, ByVal SaveAs As FileType)
        
        Dim wbkSrc                      As Workbook
        Dim wksSrcSht                   As Worksheet
        Dim rngData                     As Range
        
        strFileName = vbNullString
        On Error Resume Next
        If Right(strFilePath, 1) <> Application.PathSeparator Then
            strFilePath = strFilePath & Application.PathSeparator
        End If
        strFileName = strFilePath & strFileName
        Set wbkSrc = Nothing
        Set wbkSrc = Workbooks.Open(strFileName, , True)
        On Error GoTo -1: Err.Clear
        
        If wbkSrc Is Nothing Then
            MsgBox "Please check File Name/Path is valid or not.", vbCritical, "Abort..."
            Exit Sub
        Else
            On Error Resume Next
            Set wksSrcSht = Nothing
            Set wksSrcSht = wbkSrc.Worksheets(strShtName)
            On Error GoTo 0: On Error GoTo -1: Err.Clear
        End If
        
        If wksSrcSht Is Nothing Then
            MsgBox "Provided sheet name is not exist.", vbCritical, "Abort..."
            Exit Sub
        End If
        
        If Application.DisplayAlerts Then Application.DisplayAlerts = False
        If Application.ScreenUpdating Then Application.ScreenUpdating = False
        
        With Workbooks.Add(1)
            Set rngData = wksSrcSht.Range(strDataRange)
            .Worksheets(1).Range("A1").Resize(rngData.Rows.Count, rngData.Columns.Count).Value = rngData.Value
            wbkSrc.Close 0
            Call FolderExists
            .SaveAs Filename:=strFullPath & strFileName, FileFormat:=SaveAs, CreateBackup:=False
            .Close
        End With
        
        Set wbkSrc = Nothing
        Set wksSrcSht = Nothing
        Set rngData = Nothing
        
        If Not Application.DisplayAlerts Then Application.DisplayAlerts = True
        If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
        
    End Sub
    Please provide sheet name from which the data you want and file name should always be with extension and call procedure as below

    Code:
    call DataSaveAs("C:\Users\hrasheed\Desktop\Test\halau","Test.xls", "Sheet1","D4:L20",XL_CSV)

  8. #8
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    hi Dear Thanks For the Reply Can u Please send Me a Sample File i have tried that code it seems something going Wrong

Similar Threads

  1. Save Worksheets As New File To Specific Folder
    By k0st4din in forum Excel Help
    Replies: 18
    Last Post: 06-08-2013, 04:24 PM
  2. Importing a csv File to a range
    By SDruley in forum Excel Help
    Replies: 21
    Last Post: 11-20-2012, 04:54 PM
  3. Replies: 12
    Last Post: 08-19-2012, 06:17 PM
  4. Add ribbon programmatically to Excel 2010 using VBA
    By heapifyman in forum Excel Ribbon and Add-Ins
    Replies: 6
    Last Post: 07-18-2011, 09:16 PM
  5. Save File In CSV Format VBA
    By Raj Kumar in forum Excel Help
    Replies: 3
    Last Post: 06-01-2011, 07:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •