Excel conversion from CSV to XLS?

Question

I am working on developing a user interface for a machine that collects environmental monitoring data (at a pharmaceutical company) from a serial device and dumps it in to a CSV file. I have been asked that after the dump, a mechanism be put in place that would hide certain columns automatically and prevent others from being edited. I basically need to be able to take the pre-existing csv file and convert it to xls with the specified restrictions. Does anyone have any ideas?

Answer 1

If the original mechanism is written in VB then you can simply use the Excel library for VB and convert the CSV file to an XLS file type and protect it to the extent needed. If the original mechanism isn't written in VB, then you'd likely need to write a separate program to do the file conversion. Another alternative would be to take an Excel file and use Data Import feature to create a link to the CSV file - and simply use the data source to update the content of the new Excel file any time the CSV file is regenerated.

Answer 2

Based on what you've said here it seems to me that the optimal solution would be to import the .csv data into a database, then use whatever reporting methodology you prefer to query. If the client has explicitly stated that using a DB is out of the question, or that final output is to be in excel format, you may consider using some excel VBA to massage the data into the required shape, handle what is and isn't viewable, etc.

Answer 3

It can be done. You will have to write a macro or a program in VB for excel to do that.



You can prevent columns from being edited by protecting the cells. Only people with the password can edit the cells.

Answer 4

Can you do it with a Macro? If your imported data always has the same format, you should be able to record your keystrokes to hide the columns. Not sure how to lock others from being edited. Different files, perhaps?

Answer 5

Just open it in Excel and save as .xls.