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

 

[xenou]

Your original code had C:\ as the directory. Some systems limit access to the root directory for non-admin users. Might be a cause of some obscure errors.

Anyway, FSO always works well for me too, so you're good there.

Regards,
ξ

 

[AMAS]

Hi Xenou,

A quick follow-up on my previous posts. I had to change:

For I = 1 To x
    'code
Next x

to

For I = 1 To x
    'code
Next I

Also I tried removing the line:

x = FreeFile

but then nothing happened in my code. It just stood idely looking me in the face (probably laughing at me). So I had this line back and it works fine now. Did I misinterpret your suggestion of not assigning x a value?

AMAS

 

[xenou]

Well, as far as I can see there is no need for the For-Next loop at all.

' Open each file consequently
        Set myFolder = fso.GetFolder(fPath).Files
            For Each myFile In myFolder
                If LCase(myFile) Like "*.xls*" Then
[COLOR="Blue"]                    x = FreeFile
                    For I = 1 To x[/COLOR]
' 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
[COLOR="Blue"]                    Next I[/COLOR]
                End If
            Next myFile

Can be changed to (removing the blue lines):

' Open each file consequently
        Set myFolder = fso.GetFolder(fPath).Files
            For Each myFile In myFolder
                If LCase(myFile) Like "*.xls*" Then
' 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
                End If
            Next myFile

I assume x gets a value of 1 on each run but I don't know if FreeFile might someday return 2, or 50 ... so it's seems a possible situation where you might start repeating the loop an unknown number of times.

 

[AMAS]

Hi Xenou,

You are correct. The microsoft website says this about FreeFile: "Returns an Integer value representing the next file number available for use by the FileOpen function" (http://msdn.microsoft.com/en-us/library/c3zyf7d8(v=VS.90).aspx). It seems that the only problem is that you can't have more than 255 files open with it at the same time. When I open each and file, modify it and then close it, then it doesn't seem to have this problem.

I have tested your adjustments also and they work fine. I'm not sure if anyone has had any problems with FreeFile before? I am not experienced enough to know the possible scenarios where one method works better than the other, but so far both options seem viable.

AMAS

 

[xenou]

It's a red herring. Using a file handle is only needed with the VB Open function, which is a VB I/O function that reads/writes to a text file (or binary files). You're using the Excel VBA Open method of the workbooks collection - something entirely different. So you don't need a file handle at all.

ξ

 

[AMAS]

Hi Xenou,

Thanks for the explanation. I will write this down in my notes for future reference because I do on and off deal with text files. I guess I just got in the habit of using FreeFile for all loop procedures without completely understanding its specific purpose.

AMAS

 

[xenou]

No worries.

I use the FileSystemObject for opening text files for reading and writing, and you don't need a file number with the FSO methods either (you will set a textstream object instead, which keeps track of "where" the file is).

It's all good.

ξ

 

[AMAS]

Thanks for heads-up on the textstream object. Never used it before but will look into incorporating it into my projects.

AMAS