Convert every xls file in a folder to csv

[JI]

I need some vb code that I could perhaps run from access that will look
in a particular folder I could specify in the code, and convert all the
files (just excel are saved there) to .csv. Is this possible? This
would be a huge help for me and any help would be greatly appreciated.


Also, there are only 5 files in the folder (the folder path never
changes) but they are updated monthly so if we cant evaluate the entire
population that is in the folder and have to write seperate code to
convert each file that is cool too.

Here is my process:

Excel Files Updated and saved to directory ---> Saved as csv in same
directory---> Imported in Access ---> Access Reports Runs --->Done

I would love to have all this done in a click of a button, im so close
I just need to get this conversion to csv piece.

Thanks.

 

[mark.driscol]

If you put an Excel file containing this macro in the same directory as
your other Excel files, you should be able to execute a macro like this
macro on the other files.

Option Explicit

Sub ConvertToCSV()
'
' Uses code from John Walkenbach's Power Programming book
'
Dim i As Long
Dim NumFiles As Long
Dim FileName As String
Dim FileNames() As String

' Get name of first file in backlog directory
FileName = Dir(ThisWorkbook.Path & "/*.xls")

NumFiles = 1
ReDim Preserve FileNames(1 To NumFiles)
FileNames(NumFiles) = FileName

' Get other file names, if any
Do While FileName <> ""
FileName = Dir()
If FileName <> "" Then
NumFiles = NumFiles + 1
ReDim Preserve FileNames(1 To NumFiles)
FileNames(NumFiles) = FileName
End If
Loop

' Save each file as a .csv file, overwriting any existing .csv
files
Application.DisplayAlerts = False
For i = 1 To UBound(FileNames)
If FileNames(i) <> ThisWorkbook.Name Then
Workbooks.Open FileName:=FileNames(i)
ActiveWorkbook.SaveAs _
FileName:=Left(FileNames(i), Len(FileNames(i)) - 4) &
".csv", _
FileFormat:=xlCSV
ActiveWorkbook.Close
End If
Next i
Application.DisplayAlerts = True

End Sub


Mark

 

[JI]

I am getting an error at this part:

Workbooks.Open FileName:=FileNames(i)

the message is that it cant find the file and to make sure the
directory or name hasn't changed, it has the file name included in the
message. I think the code works to collect and store all the files, it
just is having problem going back to the list to open the files.

 

[Andrew Taylor]

This is probably because it's looking in the wrong directory: the
FIleNames() array doesn't contain the full path, so VBA is trying
to find it in whatever it thinks is the current directory. Try

Workbooks.Open FileName:=ThisWorkbook.Path & "\" & FileNames(i)

 

[JI]

Andrew and MArk thanks everything works great now.

 

[NickHK]

Jon,
If the ultimate aim is to get the data in Access, can you not just link the
Excel files in Access ?

NickHK