Divan
11-22-2011, 02:13 AM
Hi all I am having a problem I am creating a csv from a single sheet in excel but i only want to save the sheet and not the whole workbook with a savefiledialog box at the moment the path is hard coded but I want the user to be able to select the destination where he wants to save the csv copy of the selected sheet and not the entire workbook
here is my code so far
Sub createcsv()
'
' createcsv Macro
' creates a csv from xl
'
Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String, filename As String
Set fs = CreateObject("Scripting.FileSystemObject")
Destfile = "C:\Tum"
If Len(Dir(Destfile, vbDirectory)) = 0 Then ' CHECK IF PATH there
MkDir (Destfile) ' If NOT - Make IT
End If ' End Check Folder Name
If Len(Dir(Destfile, vbDirectory)) = 0 Then ' CHECK IF PATH there
Kill Destfile
MkDir (Destfile) ' If NOT - Make IT
End If ' End Check Folder Name
Set a = fs.CreateTextFile("c:\TUM\tum.csv", True) 'filesavedailog box
For r = 1 To Range("A65536").End(xlUp).Row
s = ""
c = 1
While Not IsEmpty(Cells(r, c))
s = s & Cells(r, c) & ","
c = c + 1
Wend
a.writeline s 'write line
Next r
MsgBox "The job is done !", vbInformation
End Sub
here is my code so far
Sub createcsv()
'
' createcsv Macro
' creates a csv from xl
'
Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String, filename As String
Set fs = CreateObject("Scripting.FileSystemObject")
Destfile = "C:\Tum"
If Len(Dir(Destfile, vbDirectory)) = 0 Then ' CHECK IF PATH there
MkDir (Destfile) ' If NOT - Make IT
End If ' End Check Folder Name
If Len(Dir(Destfile, vbDirectory)) = 0 Then ' CHECK IF PATH there
Kill Destfile
MkDir (Destfile) ' If NOT - Make IT
End If ' End Check Folder Name
Set a = fs.CreateTextFile("c:\TUM\tum.csv", True) 'filesavedailog box
For r = 1 To Range("A65536").End(xlUp).Row
s = ""
c = 1
While Not IsEmpty(Cells(r, c))
s = s & Cells(r, c) & ","
c = c + 1
Wend
a.writeline s 'write line
Next r
MsgBox "The job is done !", vbInformation
End Sub