PDA

View Full Version : Converting XLS file into CSV file



bhagya2340
01-12-2009, 10:44 AM
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


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



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

Artik
01-12-2009, 04:53 PM
If fso.FileExists("C:\Test\" & RequiredFileName) Then
Artik