convert csv to xls

[datacontrol]

I need to convert a csv file to xls via the push of a button. Can anyone help?

Thanks

 

[pdx_man]

Why? Just open the csv file in Excel. It will convert it. By default the computer should tell you that you have an "Excel Comman Seperated Values" file type ...

 

[datacontrol]

because I am trying to create an interface that gets rid of these steps. "magic button" lol

There is a way to do it, and I will figure it out, somehow!!

 

[pdx_man]

You can always read the csv file into a temporary table, then export the table to an Excel Workbook ...

Check out the TransferText / TransferSpreadsheet Actions in Access Help.

 

[Fornatian]

Seeing as the file info is excel interchangeable would create a duplicate file with an xls extension work?

Dim csvPath as string
csvPath = "C:\etc..."

FileCopy csvPath, Left(csvPath,Len(csvPath)-3) & "xls"

 

[pdx_man]

I tried to do that, Fornation, but when I opened it up in Excel, it did not seperate the values. Have you tried changing the extension and opening it up? It didn't work for me ...

 

[Pat Hartman]

You can't just change the extension of a file. Each file type has a certain format. When a program opens a file with a specific file type, it expects a specific format. Many programs, such as Excel and the other Office components, may be able to read files of different formats but that doesn't mean that you can just change extensions willy-nilly. I did try this to see what happens and found that:
1. Excel opens an .xls format file as a workbook regardless of what the extension is. This is as you would expect and also works with Access and other office products. It doesn't matter what the extension is provided you open the file with the correct product.
2. If you open a .csv file that has an .xls extension with Excel, Excel goes through an import wizard and imports the data correctly. This does not work with other applications. You can't give a Word document an .mdb extension and expect Access to open it as a database. I never tried any other possibilities.

Don't make a practice of renaming file extensions even though in this particular case it seems to work. It is "cute" solutions like this that come back to bite you later.

 

[Fornatian]

No-one ever called me cute before

Thanks Pat, I always like to hear your corrections of my attempts to help, I understand what you are saying re formats but CSV and XLS are very closely related, I would never attempt to open a word doc as an access db or vica-versa. I may be ill-informed but I'm not daft.

Given Pat's comments then PDX's suggestion of using a temptable and an outputto command will be the desired solution.

 

[Fornatian]

Actually I've been having a play and have worked out a straight conversion method. Include a reference to MS Excel in your VBA library (Tools-References in VBA Window) then add this public sub

Public Sub ConvertCSVtoXL(strCSVPath As String)

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\Office\" _
        & "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:=strCSVPath
    ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xls" _
        , FileFormat:=xlNormal
    End With
    
    appExcel.Quit
Set appExcel = Nothing
    
    MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
    "filename with an XLS extension"

End Sub

and call it with...

ConvertCSVtoXL "C:\YourDocs\YourFile.csv"

This code has no error trapping so will fail if an XL file of the same name exists in the same dir as the CSV file

 

[Pat Hartman]

CSV and XLS are very closely related

- really they're not. Open a .csv file with notepad and you'll see that it is a text format. Open an .xls file with notepad and see the difference. I know that you wouldn't intentionally open a Word document with Excel but you would be very confused if you opened what you thought was an .xls file and got an import dialog.

 

[pdx_man]

Just to echo what Pat said, the only thing similar between CSV files and XLS files is that they both have an 'S' in their extensions, and that's about it. Think about it. Excel is a Microsoft product and they have proprietary formats. Try reading an .xls file in WordPad and you'll see what we mean.

 

[Fornatian]

Points taken.

 

[datacontrol]

making progress

Fornation, I was able to get your code to work. Now I have a macro set up that imports the contents of the xls file to a table. The problem now is that my first column contains long numbers and some of them are being imported as scientific notation. So, I need to first have them correctly formatted I would imagine when the files is converted from csv to xls. Can you give me an example of how to correctly format this first field?

There are other fields that will need to be formatted as well. Unfortunately, I can't set up an import spec for xls files.....or can I?

Thanks...looking good!

 

[Fornatian]

Instead of

With appExcel
    
    .Workbooks.Open FileName:=strCSVPath
    ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xls" _
        , FileFormat:=xlNormal
    End With

use

With appExcel
    
.Workbooks.Open FileName:=strCSVPath
.Columns("A:A").NumberFormat = "0"    
.ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xls" _
        , FileFormat:=xlNormal
    End With

Note this will format column A to show only whole numbers, if you need decimals use "0.00" instead of "0". This can be changed at a later date as it is only a a format.

 

[datacontrol]

no luck

I tried adding your code, column one still looks the same, with scientific notation. Maybe I could import as text, if so....what would be the code?

 

[Fornatian]

Post me one of your files and i'll take a look at it tomorrow, also I know it's a daft question but is the column too narrow to display the entire contents?

 

[datacontrol]

access import

I looked at the excel file that is dynamically generated. All the information is intact there, it seems to happen when my macro imports the excel file to access.

 

[datacontrol]

file

There needs to be other formatting as well. There are some occurances of text in the first column, as well as dates throughout. Please find my attached spreadsheet (outputted by your code)

 

[datacontrol]

any word on this?

Were you able to find a way to accomplish this?

 

[Fornatian]

can u please post the original csv file and your formatting requirements, thanks. sorry I didn't have enough time to lookat it yesterday but I do night work and was knackered when I got in.