Auto convert CSVs to Excel 2003 1

[madelca100]

I need code that will read thru a directory and convert all the .csv files to .xls (Excel2003) right back into the same directory.

I don't need any interaction with the user except to provide the path to the csv files. (If the xls file exists it should be overwritten.)

Appreciate any help with sample code (or point to some code that works).

I often find code that doesn't quite work on my machine (see history below). Perhaps I have a different version of Excel or OpSys or something.

Some history:
I'm running some TEST code from Access - no particular reason except that is what I found on the net.

I would just as soon use vba in Excel directly.

And it isn't working because it fails on the SaveAs method. The code I'm testing doesn't loop either - it was used to test the base functionality.

 

[PHV]

The code I'm testing
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

You do not have to CONVERT anything.

Any Excel application can open a .csv text file.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[madelca100]

PHV, It doesn't matter since it doesn't do what Iwant it to do and I don't want to spend time trying to get it to work.

Skip, I don't have any trouble 'reading' the CSV. I'm in need of an automated way to convert them to XLS without intervention. Please read my entire post.

 

[bubba100]

Both PHV and Skip read your post. Each would help but neither will hand you a complete answer.

You could turn on the Macro recorder in Excel and Import one of the files. That would be a start.

 

[SkipVought]

Check out the FileSystemObject objects. There are examples in VBA Help. Since you're doing Excel stuff, I'd code it in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NWBeaver]

This is really pretty easy, if you would just post the code you are working with, we would be glad to help you.
I could also just post an answer, but you learn more by trying. You have to meet us half way.

 

[madelca100]

Bubba,

Thanks. I get that. It might have been nice to state something like that from the beginning.


I'll try the macro route.


What I'm trying to avoid (and I did start to post this code) is someone trying to get this to work by forcing it 'right'. Many times code like this should be blown up and started over. Unfortunately I don't have the background to start from scratch. So I found some code that looked like it would work, but doesn't. And I started fiddling around with it so it kinda/sorta works, but again- bottom line - it doesn't do what I want it to do.

Here 'tis:
Please remember:
I couldn't get it to read a directory so I hardcoded a filename.
It doesn't loop.
It stops constantly to ask for permission to proceed (e.g., Save/ Overwrite/ Quit).
I don't know if it works from Excel since I tried this from MS Access.
It abends.

In other words, not elegant. :-(


Public Sub ConvertCSVtoXLx()

'Dim appExcel As Excel.Application

'Switch to Microsoft Excel so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Excel"

'If Excel isn't running, start and activate it
If Err Then
Shell "c:\Program Files\Microsoft Office\Office11\" _
& "Excel /Automation", vbHide
AppActivate "Microsoft Excel"
End If
On Error GoTo 0

'Get an Application object so you can automate Excel.
Set appExcel = GetObject(, "Excel.Application")


With appExcel
.Workbooks.Open Filename:= _
"Z:\temp\test\cva.csv"

.ActiveWorkbook.SaveAs Filename:="xxx.xls"
', FileFormat:=xlExcel97 <----- it abends here so I commented it out
End With

appExcel.Quit
Set appExcel = Nothing

MsgBox "File xxx has been converted to excel under the same " & _
"filename with an XLS extension"


End Sub

 

[NWBeaver]

Okay...
If you really want to run it from inside Excel, consider starting with something like this:

Sub CSV_to_XLS()

'Turning off Interactive prevents keyboard or mouse input while the macro is running.
Application.Interactive = False
'Turning off DisplayAlerts suppresses warnings about overwriting existing files.
Application.DisplayAlerts = False
'Turning off ScreenUpdating allows the macro to run faster.
Application.ScreenUpdating = False


Dim StrFilePath As String
StrFilePath = "C:\FolderA\" 'This is the name of the folder to look in

'Create a list of all the files in the source folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set colFiles = objFSO.GetFolder(StrFilePath).Files

For Each objFile In colFiles
strFileName = objFile.Name

'Open the .csv file.
Workbooks.Open Filename:=StrFilePath & strFileName

'Build an ActiveWorkbook.SaveAs filename string
'to use to save the .csv file as an Excel workbook.

strName = ActiveSheet.Name
strSaveFileAs = StrFilePath & strName & ".xls"

ActiveWorkbook.SaveAs Filename:=strSaveFileAs
ActiveWorkbook.Close

Next

Set objFSO = Nothing

'Turn things back ON before quitting.
Application.Interactive = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[madelca100]

NWBeaver,

This almost works.
It does everything I requested.
But what I notice is that when I open one of the csv files in Excel and do a 'save as' it puts everything in the correct column. This process bunches everything in the first column.

Is there a way around this behavior?


Also one other challange - the procedure truncates the names of the files to 31 characters. (All are over 30 with some 35 character names.)

The system assigns the filenames so I would like to avoid having to add a step to rename them - since people are familiar with them.

btw - It isn't the folder names, only the filenames. (I moved them to a different, short name, folder and it did the same thing.)

For example: All, except my last 'Mxxx' file was wiped out.

......M123.xls and ......M345.xls were wiped. Only .....M456.xls was saved - as .....M.xls

I don't see a DIM to set. Is there another way to set this up so that any file regardless of name length could be used?

much appreciated,
mike

 

[NWBeaver]

Try changing this line:

ActiveWorkbook.SaveAs Filename:=strSaveFileAs

to look like this:

ActiveWorkbook.SaveAs Filename:=strSaveFileAs, FileFormat:=xlExcel9795

That should fix the first problem.

 

[NWBeaver]

The problem with the truncated file names lies in the fact that the code as written is using the ActiveSheet.Name property to generate the name to save the file as.
The ActiveSheet.Name property is the Tab name that you see at the bottom of each worksheet, and is limited to 31 characters.

So, in this case you can't use this:

strName = ActiveSheet.Name
strSaveFileAs = StrFilePath & strName & ".xls"

The exercise then is to find a different method to use to retain the original file name while just changing the extension from .csv to .xls

 

[SkipVought]

I would in most cases IMPORT the text, rather than OPENING the file, using Data > Import External Data > IMPORT. You will have much more precise control over the parsing of your data into the proper columns AND the proper data types.

Once you have inserted a querytable, all you need do is replace the Connection string inside a loop, and Refresh the QT.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NWBeaver]

Here is my latest revision. It now also ignores any non-.csv files.


Sub CSV_to_XLS()

'Turning off Interactive prevents keyboard or mouse input while the macro is running.
Application.Interactive = False
'Turning off DisplayAlerts suppresses warnings about overwriting existing files.
Application.DisplayAlerts = False
'Turning off ScreenUpdating allows the macro to run faster.
Application.ScreenUpdating = False


Dim StrFilePath As String
Dim StrFilename As String
Dim strName As String


StrFilePath = "C:\FolderA\" 'This is the name of the folder to look in

'Create a list of all the files in the source folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set colFiles = objFSO.GetFolder(StrFilePath).Files

For Each objFile In colFiles
StrFilename = objFile.Name
intPosition = InStr(1, objFile.Name, ".csv", vbTextCompare)

If intPosition > 0 Then
'Build an ActiveWorkbook.SaveAs filename string
'to use to save the .csv file as an Excel workbook.

intLength = intPosition - 1
strName = Left(objFile.Name, intLength)
strSaveFileAs = StrFilePath & strName & ".xls"

'Open the .csv file and save as .xls
Workbooks.Open Filename:=StrFilePath & StrFilename
ActiveWorkbook.SaveAs Filename:=strSaveFileAs, FileFormat:=xlNormal, CreateBackup:=False
ActiveWorkbook.Close

Else
MsgBox "File is not a .csv file"

End If

Next

Set objFSO = Nothing

'Turn things back ON before quitting.
Application.Interactive = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[HughLerwill]

Pleased to hear you have it working. If it is a one-off conversion job and it has worked that is fine. Otherwise please listen to Skip with reference to speed and format control. Import of csvs via QueryTables can be considerably faster than Opening them.

 

[HughLerwill]

ref my post in thread707-955487

 

[NWBeaver]

Hugh,

The .TextFileColumnDataTypes property specifies the format for each column, does it cause a problem if the number of columns changes?

How would you go about making the query table dynamic so that it works on .csv files with varying numbers of columns?

 

[SkipVought]

I would assume that all the .csv are identical in structure, or at least, there would be very few distinct groups by structure.

I'd run each "group" in its own QueryTable, with its own pre-defined parsing/format structure.

However, if you have a free-for-all, grab-bag, no-holds-barred, loosey-goosey, anything-goes, whatever-turns-you-on environment, then baby, yer on yer own!!! Have at it, opening each one with Excel, and suffer the expected consequences.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HughLerwill]

>The .TextFileColumnDataTypes property

Could be an issue to workaround if included and you need to take advantage of it, if not included however Loading/ processing of the csv maintains considerable speed advantage over Opening it.
You may be able to determine the format for each column from its column title; typically in the first line in the csv although such code may become very specific.

>How... dynamic... varying... cols

Not an issue all columns are imported

 

[madelca100]

Thanks for all the assistance especially NWBeaver.


Here is what worked - almost.

(It converted all the files, but died on a
Run-time error 1004
Application-defined or object-defined error.)


This code:
- finds all .csv files
- converts them to .xls
- puts all the data in separate columns
- does not have a 31 character limit on filename
- does not prompt the user for any input (might add a prompt for location of the files)
- writes over any existing files with same filename



Sub SaveCSV_to_XLS()
Dim str1 As Variant, str2 As String

Application.DisplayAlerts = False

str1 = Dir("Z:\1b\*.csv")
Do While str1 <> ""
Workbooks.Open Filename:="Z:\1B\" & str1
str2 = Left(str1, Len(str1) - 3)
ActiveWorkbook.SaveAs Filename:="Z:\1B\" & str2 & "xls", FileFormat:=xlExcel9795
ActiveWindow.Close
str1 = Dir
Loop
Application.DisplayAlerts = True
End Sub