Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a bunch of CSV files in a folder. I'd like to import each file into Excel using the 'Import from Text' tool by typing the name of the file into a cell. I then need to format all of the data into a specific layout.

All of the files are in the same folder and are identical in terms of headings used, number of entries, size etc. The final layout will also be identical each time, it's just a matter of getting the data from the CSV file into the correct places and applying the correct formatting.

I have a template layout in a separate Excel workbook, so could either use this if possible or could apply the formatting from scratch within the macro.

I've never written a VBA code before and am just starting out recording macros (the absolute basics) because I know that's the way to get automating processes like this. A suggestion on how to record this macro myself would be absolutely ideal, as a bunch of code with no explanation will be difficult for me to edit later if needs be, however any advice is much appreciated.

share|improve this question
    
You want to type a file's name into a cell and have excel import the data from that file name (if it exists in a specific location) and format it? Where do you want the data to go? Into a new workbook? A sheet of the current book? An exact example would be helpful in guiding you. –  Raystafarian Jan 31 at 18:19

1 Answer 1

In Excel, it is possible to save text import settings (as a "template") and reuse them with any text file later. A blogpost by Excel MVP Jan Karel Pieterse describes this in detail: http://blogs.office.com/2010/06/10/importing-text-files-in-an-excel-sheet/

The "text import template" is automatically created when you are done with Text Import Wizard. Make sure that you save your workbook to keep the "template".

To reuse it with another (or the same) text file:

  1. Click any cell within your sheet that holds (used to hold) data of a previous import.
    • Excel 2007/2010: Click Data -> Refresh All dropdown -> Refresh
    • Excel 2003 and older: Click Data -> Refresh Data

If you use this "template" to always import a file of the same name and location, you can customize the "template" so that Excel does not prompt you for a file name each time you hit the Refresh button.

During the text import, click Properties... in the Import Data dialog and untick Prompt for file name on refresh in the External Data Range Properties dialog. You can also edit that later in the Connection Properties dialog.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.