CHANGE HEADINGS FROM TOOLBAR BUTTON

[netrusher]

I have a report that is exported from another application into an Excel Spreadsheet. My problem is that the column headings do not mean anything to the viewer of the Spreadsheet. Example:
CDEFFM
CDEFMS
CDEFSM

These three column headings represent:
PROCESS
SOECODE
BUILD ID

I am seeking help that whenever exports open in Excel that the column headings change names:
CDEFFM become PROCESS etc. etc. I am looking for advice on how to accomplish this. Macro, VBA, etc.

Also, in one Spreadsheet CDEFFM might be in Cell A1 and in another in could be in Cell F1, etc. My point is that the column headings might be in different cells.

Thanks for your help.

 

[StuckInTheMiddle]

Hi again netrusher

To answer your questions about my code solution in the
Office forum post you need to put the where the graphics shows below. I noticed you've changed the question, so please see the amended code below which will replace your column headings no matter where they begin (A1 or F1 or Z1).

Then close XL file and re-open it and it will replace your column headings with the chosen ones, simply repeat this for any number of columns making sure to increment the cells(1,4) for the next one etc.

Simply paste your new data into this XL book each time (or paste the code into a new book) and it will magically transform your names for you when it re-opens

Private Sub Workbook_Open()
On Error Resume Next

Dim ws As Worksheet
Dim sAddress As String

For Each ws In ActiveWorkbook.Sheets

    sAddress = ws.Range("A1:AZ1").Find("CDEFFM", , , xlWhole).Address
    If Err.Number = 0 Then
        ws.Range(sAddress) = "PROCESS"
        Err.Clear
    End If
    
    sAddress = ws.Range("A1:AZ1").Find("CDEFMS", , , xlWhole).Address
    If Err.Number = 0 Then
        ws.Range(sAddress) = "SOECODE"
        Err.Clear
    End If
    
    sAddress = ws.Range("A1:AZ1").Find("CDEFSM", , , xlWhole).Address
    If Err.Number = 0 Then
        ws.Range(sAddress) = "BUILD ID"
        Err.Clear
    End If

Next ws

End Sub

 

[netrusher]

I have not tried the code that just got posted but I have this code (see below). I have the macro attached to a customized button. How can I ensure that I can run it anytime any excel spreadsheet is opened.

Below is the code that I now have for changing column header names. I have this macro attached to a customized button on the tool bar. How do I attach the code to ensure that it is available to run for any spreadsheet that is opened?

Sub ColumnNames()

With ActiveSheet.Range("1:1")
.Replace What:="CD1FID", Replacement:="Record ID", LookAt:=xlWhole
.Replace What:="CD2FFM", Replacement:="Parent", LookAt:=xlWhole
.Replace What:="CD8FFM", Replacement:="Dwg Nbr", LookAt:=xlWhole
.Replace What:="CD9FFM", Replacement:="Soecode", LookAt:=xlWhole
.Replace What:="CDEFFM", Replacement:="Child", LookAt:=xlWhole
.Replace What:="CDEFMP", Replacement:="Process", LookAt:=xlWhole
.Replace What:="CDEFMS", Replacement:="SoeCode", LookAt:=xlWhole
.Replace What:="CDEFSM", Replacement:="Child", LookAt:=xlWhole
.Replace What:="CDEFTM", Replacement:="Tool Nbr", LookAt:=xlWhole
.Replace What:="CDEFTR", Replacement:="Text Ref Nbr", LookAt:=xlWhole
.Replace What:="NARFTX", Replacement:="Event Ext", LookAt:=xlWhole
.Replace What:="NB1FDS", Replacement:="Dwg Rev", LookAt:=xlWhole
.Replace What:="NB3FME", Replacement:="Event Nbr", LookAt:=xlWhole
.Replace What:="NBRFFN", Replacement:="Map Qty", LookAt:=xlWhole
.Replace What:="NBRFXM", Replacement:="Build DC Qty", LookAt:=xlWhole
.Replace What:="NMSWRU", Replacement:="Work Unit", LookAt:=xlWhole
.Replace What:="PSREQ", Replacement:="Mfg Qty Batch", LookAt:=xlWhole
.Replace What:="ST1FME", Replacement:="Tqc/Ver", LookAt:=xlWhole
.Replace What:="ST1REG", Replacement:="Register Req", LookAt:=xlWhole
.Replace What:="TXTFMP", Replacement:="Process Desc", LookAt:=xlWhole
.Replace What:="TXTFMS", Replacement:="Soe Desc", LookAt:=xlWhole
.Replace What:="TXTFSM", Replacement:="Build ID Desc", LookAt:=xlWhole
.Replace What:="TXTFTM", Replacement:="Tool Desc", LookAt:=xlWhole

End With

End Sub

 

[StuckInTheMiddle]

Read this

http://office.microsoft.com/en-us/assistance/HP051993911033.aspx

A,

 

[netrusher]

A.

Thanks,

I did not see any help to my question on the links at the site you provide.

 

[xlbo]

your code would need to be placed in the workbook_open event for it to work in that particular workbook. To have it work in EVERY spreadsheet would require some serious programming as you would 1st have to create an add-in - this add-in would have to be able to know when any other workbook was opening and then run the code - this is not simpe. I would also ask whether you really want this to run for every workbook that you open in excel...really? every single one ??