CONVERT COLUMN HEADINGS

netrusher · Feb 15, 2006

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.

Thanks for your help.

StuckInTheMiddle · Feb 15, 2006

Hi netrusher

Here is some code that you could save to the exported XL sheet (hit ALT+F11) and paste this into the 'thisworkbook' area.

Code:

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

    If ws.Cells(1, 1) = "CDEFFM" Then
        ws.Cells(1, 1) = "PROCESS"
    End If
    
    If ws.Cells(1, 2) = "CDEFMS" Then
        ws.Cells(1, 2) = "SOECODE"
    End If
    
    If ws.Cells(1, 3) = "CDEFSM" Then
        ws.Cells(1, 3) = "BUILD ID"
    End If

Next ws

End Sub

You can amend this to run in a seperate XL sheet against a different XL file each time too if you need that.

A.

netrusher · Feb 15, 2006

Can you tell me what this is actually doing? I inserted the Code as you said and opened the Excel file and nothing happened. Also I would like to have a way to make this happen with each Excel file that is exported from my application. Maybe have a toolbar button to hit if I need to.

xlbo · Feb 15, 2006

netrusher - if you require code answers, please post in the VBA forum (Forum707)

The answer to your question is that you probably havn't put the code in the ThisWorkbook module....

Please post back in the VBA forum and rather than asking for general advice, please ask specific questions - showing or explaining what you have tried already also helps...

netrusher · Feb 15, 2006

xlbo,

I posted here because I do not know if I need code or not. I was hoping a macro or lookup might be the answer. I did put the code in the workbook and it did not work. If I switch to another forum how will I get a answer from the person who posted the code in the first place? I am willing to post wherever I need but I wanted to follow up with A. who gave me the code.

Thanks

xlbo · Feb 15, 2006

a macro in excel = code

You miss the point about the Thisworkbook module. Your code may be in the workbook but is probably in a standard module.

The whole point about this being a forum is that there are lots of people that can answer your question. Don't worry overly about being able to follow up with the 1st person that responds to your question

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CONVERT COLUMN HEADINGS

netrusher

Technical User

StuckInTheMiddle

Programmer

netrusher

Technical User

xlbo

MIS

netrusher

Technical User

xlbo

MIS

Similar threads

Part and Inventory Search

Sponsor