use names of excel

[#1]

Suupose i have an excel sheets named FY11, FY12,FY13 ...In these excels i have columns named Jan Feb MAr Apr .. Now i want to rename these columns as JAn 11, Feb 11 .... based on the excel sheet names .. so how to do this stuff ..Can someone suggest me some idea ?

[SunilChauhan]

filebasename() will give you name without extention of excel

hope this helps

[#3]

Hi Sunil,

I tried this :- Jan as Jan&'-'&filebasename();

but it is not working

[SunilChauhan]

Jan as 'Jan'&'-'&filebasename();

or can you share sample file

[Sokkorn]

Hi,

Do you load data for specific sheet name or loop for all sheet in excel in one time?

If you load for specific sheet name; let say load only sheet FY11 then you can do this

     Load

          Months & 11,

     ......................

Regards,

Sokkorn

[SunilChauhan]

or you can try beloww code

Jan as Jan&'-'&right(filebasename(),2);

hope you required this

[m_woolf]

Once you have the workbook open (objWorkbook):

for i = 1 to objWorkbook.sheets.count

     strSheetname = objWorkbook.sheets(i).name   ' will give you the sheet name

     strYear = right(strSheetname,2)     ' will give you the year

' assuming your Jan column heading is in A1 and you have all 12 months

     for j = 1 to 12

          objWorkbook.sheets(i).cells(1,j).value = objWorkbook.sheets(i).cells(1,j).value & " " & strYear

     next

next