Simple code to create .csv file

[bellawala]

I've been looking through the board here and have found a couple of lengthy possible solutions to this problem, but none seems to be exactly what I'm looking for.

All I want to do is have a macro that will, for example, create a csv file on my desktop from Range("A1:B10") in the worksheet named "testsheet".

Thanks!

 

[John_w]

This should do it:

Sub SaveRangeCSV()

    Dim saveRange As Range
    Dim newWorkbook As Workbook
       
    Set saveRange = Sheets("testsheet").Range("A1:B10")
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)
    saveRange.Copy newWorkbook.Sheets(1).Range("A1")
    newWorkbook.SaveAs DesktopPath & "data.csv", FileFormat:=xlCSV
    newWorkbook.Close False
    
End Sub

Private Function DesktopPath() As String
    DesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
End Function

 

[kyrgan]

I've been looking through the board here and have found a couple of lengthy possible solutions to this problem, but none seems to be exactly what I'm looking for.

All I want to do is have a macro that will, for example, create a csv file on my desktop from Range("A1:B10") in the worksheet named "testsheet".

Thanks!

I have this snippet...I hope it helps

Sub SaveGrossItemSalesCSV()

    Dim FirstPart As String, SecondPart As String
    Dim PathName As String, FullName As String
    Dim i As Integer
    Dim SourceBook As Workbook, ReportBook As Workbook
           

Application.ScreenUpdating = False

    WSName = ActiveSheet.Name 'This is the name on the worksheet tab, make sure it's dos/win compliant
    
    Set SourceBook = ThisWorkbook
    Set ReportBook = Workbooks.Add
       
    'Delete all daily files in the upload dir
    'If Dir("c:\DIRNAME\upload\" & "*.csv") <> "" Then
    '    Kill "c:\DIRNAME\upload\" & "*.csv"
    'End If
    
    'Copy appropriate sheet to new workbook
    SourceBook.Sheets(WSName).Copy Before:=ReportBook.Sheets(1)
    Call DeleteImportButton

    
    'Delete Extra Sheets in new workbook
    Application.DisplayAlerts = False
    For i = Sheets.Count To 2 Step -1
    Sheets(i).Delete
    Next i
    Application.DisplayAlerts = True
    
    'Delete any columns/Rows I do not want in the saved file.
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:I").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.NumberFormat = "General"
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    'Build Path and File Name for new workbook
    PathName = "c:\DIRNAME\import"
    FirstPart = WSName
    SecondPart = Format(SourceBook.Sheets(WSName).Range("D1").Value, "[$-409]d-mmm-yyyy;@") 'ddmmyyyy
    CSVFilename = "Gross Sales Export for " & FirstPart & " " & SecondPart
    FullName = PathName & "\" & CSVFilename
    
    'Save and close new workbook
    Application.DisplayAlerts = False
    ReportBook.SaveAs Filename:=FullName, FileFormat:=xlCSV
    ReportBook.Close
    Application.DisplayAlerts = True
    
    
    Application.ScreenUpdating = True
    
End Sub

 

[bellawala]

Worked like a charm!

Thanks!