0

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.

Improve this question
2
  • 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, 2014 at 18:19
  • Since you didn't accept the answer, I'm not sure if it answered your question or not. But I thought I should at least link this question and answer, Importing csv files into Excel using a macro, for anyone else looking to get started on this type of task.
    – GlennFromIowa
    Oct 5, 2016 at 17:25

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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