Convert a CSV file into a formatted Excel file

**douglasvrs** · 01-06-2009, 04:09 PM

Hello everyone!

I tried many ways to convert a CSV file into a formatted Excel (.xls) file via VBA. I have a file with 5 lines (header included) and about 10 columns (delimited by commas). How can I format it via vba on button click action? Does anyone happen to know?

Thanks everyone!

**Richard Schollar** · 01-06-2009, 05:27 PM

Hi

Can't you just open it up in Excel, apply any formatting as required and then resave as an xls?

Richard

**bhofsetz** · 01-06-2009, 06:33 PM

Have you tried recording a macro when you open the file manually, format as desired and then save.

Give that a try to get all the syntax, etc. correct for the formatting you want to perform.

HTH

**douglasvrs** · 01-07-2009, 09:42 AM

I have these two examples of codes to try to do it via VBA:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I tried to use them changing only the paths and the fields, but I get some errors. Mainly on the first code above, I get an error on the variable "rs = New ADODB.Recordset".

I need to automate it because the users that will use this file don't know how to format it manually.

Could you help me with that?

Thanks

**Richard Schollar** · 01-07-2009, 11:03 AM

How many records are held in the csv file? Since you are not filtering the data in any way, I think it unnecessary to use ADO to import the file. Did you try what bhofsetz suggested by recording a macro of you going File>Open in Excel and opening the file up and applying formatting as required?

**Kenneth Hobson** · 01-07-2009, 12:28 PM

Welcome to the forum!

I don't understand why you would want to append to a file that you query. It would be better to just post 1 ado code example.

If you can attach a short example csv file, it would be easier to help you.

As stated earlier, if you record a macro, you will have what you need for a QueryTable. e.g.
Data > Import External Data > Import Data... > Select your csv file, and finish the wizard. For my csv file with 2 fields, First and Last:

Please Login or Register  to view this content.

The array part is where it set the data types.

We can do the ado if you like but a QueryTable will probably meet your needs.

**douglasvrs** · 01-07-2009, 01:36 PM

Perfect, it worked!!! Thanks!

Just one more question: How can I save it again on a CSV File? That's because I need to make some changes on it and save it back on a CSV format delimited by commas.

Thanks to all of you!!!

**Kenneth Hobson** · 01-07-2009, 01:59 PM

Here again, the recorder is your friend. Save it as a csv file.

**bhofsetz** · 01-08-2009, 04:40 PM

Recorded while saving the active file to a csv

Please Login or Register  to view this content.

HTH

**VBA Noob** · 01-08-2009, 05:09 PM

douglasvrs,

I wrapped you're code this time but please read the below before posting again

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

VBA Noob

Convert a CSV file into a formatted Excel file

LinkBack

Thread Tools

Rate This Thread

Display

Convert a CSV file into a formatted Excel file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions