Macro to Rename One of Two Identical Column Names

[paulkem]

I have a data extract that is in the format of an Excel workbook. The file is exported from a web interface by a person. I am importing this extract into SQL Server via SSIS. The problem is that there are two columns on the worksheet that have the same name. I need a way to find the the second instance of the column name and tack a "1" or something to the end of the field name. I would also like to programatically change the tab name.

I was thinking of a template file that the user could paste the exported data into, then run a macro to rename that second field and the tab, then save the file as normal.

Any ideas?

Thanks

PK

[tehneXus]

Hi,

try this, works with an unspecified amount of duplicate column headers, the first duplicate remains while each following is renamed by adding "_1" and so on
I assumed the headers to be in row 1:

Please Login or Register  to view this content.

Regards

[paulkem]

This appears to work quite nicely.

Now, is there a way to rename the tab within this same macro?

[tehneXus]

Yes it is,

put

Please Login or Register  to view this content.

Regards

[paulkem]

Thank you! I almost have the functionality that i need. I figured out how to "save as" through the macro, but the file that i am trying to work with is actually XML data with an XLS extension. When i use .SaveAs Filename:="filename.xls" it still just saves it with an XLS extension. Do you know how to code a "Save as type:" to force it to save as Excel formatted XLS?

[tehneXus]

Hi,

please find a detailed documentation of the SaveAs function here: http://msdn.microsoft.com/en-us/libr.../ff841185.aspx
and the values for the "FileFormat" parameter here: http://msdn.microsoft.com/en-us/libr.../ff198017.aspx

Actually xls is an excel format extension, if you want to save as xml use:

Please Login or Register  to view this content.

Regards

[paulkem]

Yep. XLS is the file format that i need it to save as. I think i found my answer. Thank you so much for your help!