Button macro - create CSV file

[da1]

Macros for Button in workbook.worksheet2 that saves data from workbook.worksheet2 starting from row number 4 as c:\file2.CSV. Does anyone know the code I can use?

Thanks,
Dana

 

[VoG]

Have you tried recording a macro whilst doing this manually?

 

[DragonWood]

Dana,

Personally, I don't agree with putting something directly on the C drive, but I'm sure you have your reasons.

Either way, this code will export every row from 4 down and columns A through J. You can make adjustments as needed.

Also, I have included a commented out line that will put the new file in the same place the original is (just in case).

I also believe in password protecting my worksheets so I have included the code that unprotects and reprotects them so the code can execute.

Public Sub ExportFile2()
'Created by Dragon Wood (September, 2011)
'Exports the data to a .csv file and puts directly on the C drive

    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
    .Unprotect Password:="Password"
    
    Dim intExport As Integer
    Dim LR As Long
    Dim i As Long
    Dim strFile As String
    
    strFile = "C:\file2.csv"
    'strFile = ThisWorkbook.Path & "\" & "file2.csv"
    LR = Range("A" & Rows.Count).End(xlUp).Row
    intExport = FreeFile

    Open strFile For Output As #intExport
    
    For i = 4 To LR
        Print #intExport, .Range("A" & i).Value; & ","; .Range("B" & i).Value;  & ","; .Range("C" & i).Value;  & ","; .Range("D" & i).Value;  & ","; .Range("E" & i).Value;  & ","; .Range("F" & i).Value;  & ","; .Range("G" & i).Value;  & ","; .Range("H" & i).Value;  & ","; .Range("I" & i).Value;  & ","; .Range("J" & i).Value
    
    Next i
    
    Close #intExport
    
    .Protect Password:="Password"
    
    End With
    
    Application.ScreenUpdating = True

End Sub

I did not test this yet.

 

[DragonWood]

Ok, this one is tested and works.

Public Sub ExportFile2()
'Created by Dragon Wood (September, 2011)
'Exports the data to a .csv file and puts directly on the C drive

    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
    '.Unprotect Password:="Password"
    
    Dim intExport As Integer
    Dim LR As Long
    Dim i As Long
    Dim strFile As String
    
    strFile = "C:\file2.csv"
    'strFile = ThisWorkbook.Path & "\" & "file2.csv"
    LR = Range("A" & Rows.Count).End(xlUp).Row
    intExport = FreeFile

    Open strFile For Output As #intExport
    
    For i = 4 To LR
        Print #intExport, .Range("A" & i).Value; ","; .Range("B" & i).Value; ","; .Range("C" & i).Value; ","; .Range("D" & i).Value; ","; .Range("E" & i).Value; ","; .Range("F" & i).Value; ","; .Range("G" & i).Value; ","; .Range("H" & i).Value; ","; .Range("I" & i).Value; ","; .Range("J" & i).Value
    Next i
    
    Close #intExport
    
    '.Protect Password:="Password"
    
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[da1]

Thank you very much Dragon! It works perfect

Ok, this one is tested and works.

Public Sub ExportFile2()
'Created by Dragon Wood (September, 2011)
'Exports the data to a .csv file and puts directly on the C drive

    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
    '.Unprotect Password:="Password"
    
    Dim intExport As Integer
    Dim LR As Long
    Dim i As Long
    Dim strFile As String
    
    strFile = "C:\file2.csv"
    'strFile = ThisWorkbook.Path & "\" & "file2.csv"
    LR = Range("A" & Rows.Count).End(xlUp).Row
    intExport = FreeFile

    Open strFile For Output As #intExport
    
    For i = 4 To LR
        Print #intExport, .Range("A" & i).Value; ","; .Range("B" & i).Value; ","; .Range("C" & i).Value; ","; .Range("D" & i).Value; ","; .Range("E" & i).Value; ","; .Range("F" & i).Value; ","; .Range("G" & i).Value; ","; .Range("H" & i).Value; ","; .Range("I" & i).Value; ","; .Range("J" & i).Value
    Next i
    
    Close #intExport
    
    '.Protect Password:="Password"
    
    End With
    
    Application.ScreenUpdating = True

End Sub