Converting XLS file into CSV file
HI,
I am very new to VBA and i have no clue where i am wrong.
Actually i want my macro to select a .xl file form its folder and convert it into a .csv file. The select should be automatic. It should search for the file in foulder for excample in Test which is at C:\ There will only one file name LTCChanges*.xls. The * is date and it keeps on changing. It have to select this file and convert it into .CSV and save it in same Test folder. I wrote code for selecting and converting but its not working...
here is the code
[vba]
Private Sub xltocsv()
Dim XlFlName As String
Dim CnvFlName As String
Dim fso As FileSystemObject
Dim fso_fol
Dim der As Folder
Dim txt As String
Dim fso_file As File
Dim i As Long
Dim file_name As String
Dim RequiredFileName As String
' Make a new File System object.
Set fso = New FileSystemObject
dir_path = "C:\Test"
' Get the FSO Folder (directory) object.
Set fso_folder = fso.GetFolder(dir_path)
RequiredFileName = ""
For Each fso_file In fso_folder.Files
file_name = fso_file.Name
If (Left$(file_name, 10) = "LTCChanges") Then
RequiredFileName = file_name
Exit For
End If
i = i + 1
Next fso_file
CnvFlName = Mid(GetFileName(RequiredFileName), 1, 18)
ChDir "C:\Test"
MsgBox "Conversion of .xls spreadsheet to .csv tab format"
'FileExists -- Check to see if the file exists or not
If FileExists(RequiredFileName) Then
Workbooks.Open Filename:=RequiredFileName
CsvFlName = CnvFlName + ".csv"
ActiveWorkbook.SaveAs Filename:="C:\Test\" + CsvFlName, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
MsgBox CsvFlName
Else
MsgBox "File is not selected " & RequiredFileName
End If
'Application.Quit
End Sub
[/vba]
There is something wrong in this code everthing works fine till
If FileExists(RequiredFileName) Then
where it goes to the else part and give file not found....
Can anyone say me where i am wrong and what should i do to get it right
Thanks
