VBA: Trim Headers from Multiple Excel files

[PaigeWeber]

Hello! New to this forum.

I am looking for a code in VBA to automatically trim headers from 12 different Excel files that I receive once a week by Outlook.

I already have code written in Outlook to automatically download these Excel files from Outlook onto a network server.. I need the macro to trim the headers once downloaded into the folder so they can be imported into Access.

When attempting to import the files into Access, I get an error because a few of the headers have spaces in front of them

Here are the names of the files:

PACE007A_Rgn_NE_LTE_NSB.xlsx
PACE007A_Rgn_SW_LTE_CA.xlsx
PACE007A_Rgn_WE_UMTS_CA.xlsx
PACE007A_Rgn_WE_Mods2.xlsx
PACE007A_Rgn_WE_LTE_CA.xlsx
PACE007A_Rgn_WE_LTE_NSB.xlsx
PACE007A_Rgn_SW_UMTS_NSB.xlsx
PACE007A_Rgn_SW_Mods2.xlsx
PACE007A_Rgn_SW_UMTS_CA.xlsx
PACE007A_Rgn_SW_Mods.xlsx
PACE007A_Rgn_WE_Mods.xlsx
PACE007A_Rgn_WE_UMTS_NSB.xlsx

 

[frank_AL]

Paige,

A couple of questions before I try to provide a solution for you:

1. Is there only 1 worksheet in each of these files and if so, does the worksheet have a specific name or just "Sheet1" by default?
2. Are the headers located in Row 1 and do they start in Column A?
3. Can you provide a couple of examples of existing header names that have the extra space in them (e.g., " Date" or "Date ")?

 

[PaigeWeber]

Paige,

A couple of questions before I try to provide a solution for you:

1. Is there only 1 worksheet in each of these files and if so, does the worksheet have a specific name or just "Sheet1" by default?
2. Are the headers located in Row 1 and do they start in Column A?
3. Can you provide a couple of examples of existing header names that have the extra space in them (e.g., " Date" or "Date ")?

Hi Frank, See below:

1.) There are 2 tabs in each worksheet (Job Export List_1 and Description_2). I am only using data in the Job Export List_1 tab
2.) The headers are located in row 1, and do start in column A
3.) I am seeing spaces in front of text. (Example: " Job_Scope_Code")

Thanks!

 

[frank_AL]

Paige,

Give this code a try. You will need to edit the line that starts with "foldername =" to enter the folder where your files exist. Be sure to add the "" at the end.
Let me know if this gives you what you are looking for.

Option Explicit
Sub TrimHeaders()
Dim foldername As String
Dim strpathfile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim filelist(1 To 12) As String
Dim i As Integer
Dim x As Integer
Dim lc As Long
Dim currval As String


filelist(1) = "PACE007A_Rgn_LTE_NSB.xlsx"
filelist(2) = "PACE007A_Rgn_SW_LTE_CA.xlsx"
filelist(3) = "PACE007A_Rgn_WE_UMTS_CA.xlsx"
filelist(4) = "PACE007A_Rgn__WE_Mods2.xlsx"
filelist(5) = "PACE007A_Rgn_WE_LTE_CA.xlsx"
filelist(6) = "PACE007A_Rgn__WE_LTE_NSB.xlsx"
filelist(7) = "PACE007A_Rgn_SW_UMTS_NSB.xlsx"
filelist(8) = "PACE007A_Rgn_SW_Mods2.xlsx"
filelist(9) = "PACE007A_Rgn_SW_UMTS_CA.xlsx"
filelist(10) = "PACE007A_Rgn_SW_Mods.xlsx"
filelist(11) = "PACE007A_Rgn_WE_Mods.xlsx"
filelist(12) = "PACE007A_Rgn_WE_UMTS_NSB.xlsx"


foldername = "C:\Users\fbatu\Documents\VBA Code Examples\MrExcel\Trim Headers\"


For i = 1 To 12
    strpathfile = foldername & filelist(i)
    Set wb = Workbooks.Open(strpathfile)
    Set ws = Sheets("Job Export List_1")
    lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For x = 1 To lc
        currval = Trim(ws.Cells(1, x).Value)
        ws.Cells(1, x).Value = currval
    Next x
    ActiveWorkbook.Close savechanges:=False
Next i
        
End Sub