Mac – Automatically rename the worksheet created for a CSV file imported into Excel 2010 to Sheet1

macrosmicrosoft excelmicrosoft-excel-2010

I have an application that can import data from an Excel (.xls) spreadsheet. This spreadsheet is in turn imported from a CSV file, which provides a template for data. The user then populates the template, saves it as an .xls file, and imports it into the application.

My problem is that the application that imports the spreadsheet expects a sheet called Sheet1, but when the user opens the CSV template, Excel 'imports' the CSV data into a and automatically created worksheet with the same name as the CSV file. Example:

  1. Application X creates a template file called January.csv
  2. User opens January.csv in Excel, which creates a worksheet called January. They populate this worksheet, and save the workbook as MonthlyData.xls
  3. Application Y attempts to import data from Sheet1 in MonthlyData.xls, and fails.

I would like to find some way of telling Excel that when it opens a CSV file, it must automatically rename the first worksheet it creates to Sheet1, and not give that sheet the same name as the CSV file, as it does by default. What means are at my disposal for doing this?

Best Answer

There is no such thing as a 'worksheet' in a CSV file; therefore, there is nothing to rename. Rely on the behavior you have explained above and pass the file name from the original step to the final step so that the XLS import process in step 3 knows to expect a object called original_filename from step 1.

A CSV is just that - a text file with comma separated values, there is no way to define a worksheet in a CSV file.

Related Question