SaveAS .csv

[AMAS]

Hi,

I have this code that is supposed to open excel file, manipulate them, then save the end result as a csv file. It doesn't...

   Const pathPattern As String = "C:\*.xls*"
   
   fileName = Dir(pathPattern)
   Do While fileName <> ""
      Set wb = Workbooks.Open(fileName)
        With wb.Worksheets(1)
'Do something
        End With
      wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False
     wb.Close True
    fileName = Dir
   Loop

It always tries to replace the original excel sheet (exact name as original file). When I changed:

      wb.SaveAs fileName:=wb.Path & "\" & fileName, FileFormat:=xlCSV, CreateBackup:=False

to

      wb.SaveAs fileName:=wb.Path & "\" & fileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False

then it kept the original file name (including the .xlsx) and added a .csv to the end.

Where am I going wrong???

AMAS

 

[AMAS]

Bump

 

[AMAS]

Re: SaveAS .csv not working

Any thoughts?

 

[AMAS]

Bump

 

[Asator]

Re: SaveAS .csv not working

your filename variable will include the .xlsx extension. You're probably going to want to trim that off.

newfile = left(filename,len(filename)-5) & ".csv"

 

[AMAS]

Thanks Asator.

I had a feeling that this was the problem there must be a more efficient way because when I use a similar approach for .xls, .doc, .docx etc. files. No that microsoft has made the extension 3 or 4 letters after the period, it has become more complicated.

Am I the only one with this problem? I have not seen a universal solution anywhere on the internet

AMAS

 

[xenou]

Not tested but the Scripting FileSystemObject has many useful features for dealing with file objects:

Const pathPattern As String = "C:\*.xls*"
[COLOR="Navy"]Dim [COLOR="RoyalBlue"]CSVSavePath [/COLOR]As String
Dim FSO As Object[/COLOR]
Filename = Dir(pathPattern)
[COLOR="Navy"]If FSO Is Nothing Then
    Set FSO = CreateObject("Scripting.FileSystemObject")
End If[/COLOR]
Do While Filename <> ""
    Set wb = Workbooks.Open(Filename)
    With wb.Worksheets(1)
        'Do something
    End With
[COLOR="Navy"]    [COLOR="RoyalBlue"]CSVSavePath [/COLOR]= FSO.GetFolder(Filename).Name & "\" & FSO.GetBaseName(Filename) & ".csv"[/COLOR]
    wb.SaveAs Filename:=[COLOR="RoyalBlue"]CSVSavePath[/COLOR], FileFormat:=xlCSV, CreateBackup:=False
    wb.Close True
    Filename = Dir
Loop

 

[AMAS]

Hi Xenou,

Excellent tip. I have modified the code to use the FileSystemObject but had trouble using Dir so I had to use a slightly different method. I know that most people use Dir to loop through all the files in a folder, but I have had mixed results with Office 2007 on Windows Vista and Office 2010 on Windows 7. It seems to work consistently on Office 2007 on Windows XP, but gave errors frequently on OS after XP. Here is my modified code which seems to work so far.

Sub Convert_Excel_CSV()
Dim screenUpdateState       As Variant
Dim statusBarState          As Variant
Dim eventsState             As Variant
Dim fso                     As Object
Dim fPath                   As String
Dim myFolder, myFile
Dim wb                      As Workbook
Dim SavePath                As String
Dim I, x                    As Integer

' Turn off some Excel functionality so your code runs faster
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    eventsState = Application.EnableEvents
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
' Use File System Object to choose folder with files
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With
' Open each file consequently
        Set myFolder = fso.GetFolder(fPath).Files
            For Each myFile In myFolder
                If LCase(myFile) Like "*.xls*" Then
                    x = FreeFile
                    For I = 1 To x
' Perform tasks with each file
                        Set wb = Workbooks.Open(myFile)
                        With wb.Worksheets(1)
                            'Add code to do something with each file
                        End With
                        
                        ' Save file in original folder, but as csv file format
                        SavePath = fso.GetFolder(fPath).Name & "\" & fso.GetBaseName(myFile) & ".csv"
                        wb.SaveAs fileName:=SavePath, FileFormat:=xlCSV, CreateBackup:=False
                        
                        ' Close file
                        wb.Close True
' Loop through all files in folder
                    Next I
                End If
            Next myFile
'clean up
    myFile = vbNullString
    I = 1
' Turn Excel functionality back on
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState
End Sub

Thanks again.

AMAS

 

[xenou]

Good. Glad it helped. I've had no problems with any FSO routines so far but I'm always on XP at work and at home.

This seems like overkill (and a potential cause of error, since we aren't sure what FreeFile will be, even if it's usually 1). I'd remove the line that assigns x a value, and remove this loop (but not what's inside it):

x = FreeFile
For I = 1 To x
    'code
Next x

 

[AMAS]

Hi Xenou,

OK. Will do. Thanks for the heads up on this potential problem.

As for XP vs. subsequent Windows OS. At work, every computer runs XP, and almost all run Office 2007. I found that Dir worked on the office computers just fine, but when I tried it on several computers running Office 2007 under Vista then I ususally (not always) got an error that the file could not be found. When I just upgraded both the OS to Windows 7 and Office to 2010, I thought I might give Dir another try, but am still having problems. Since I'm not a programmer, and don't have the adequate knowledge to trouble-shoot this problem, I just moved on to something else that works.

AMAS