I have about 750 excel files. I need to clean them so that they all contain same formatting - i.e. so they all contain same number of columns.
Some files (80%) contain extra columns containing labels with an asterisk e.g. "*1 subject".
Is there a way using visual basic to go through all of the files in my folder to delete all columns containing an asterisk so that all files don't have any such columns? Will the fact that an asterisk is a wild card in computer speak make a difference?
Write a macro that uses filesystemobjects to loop through directory where the spreadsheets are. Loop through each sheet and analyse the column names.
Here is how you would loop through each sheet.
Private Sub CommandButton7_Click()
Dim ws As Excel.Worksheet
Dim iCol As Integer
Dim strName As String
Dim iIndex As Integer
'Loop through the sheets.
For iIndex = 1 To Application.Worksheets.Count
Set ws = Application.Worksheets(iIndex)
'Loop through the columns.
For iCol = 1 To ws.UsedRange.Columns.Count
'Check row 1 of this column for first char of *
If Left(ws.Cells(1, iCol).Value, 1) = "*" Then
'We have found a column with the first char of *
ws.Columns(iCol).EntireColumn.Delete
End If
Next iCol
Next iIndex
ActiveWorkbook.SaveAs Filename:="C:\temp\newfiles\" & ActiveWorkbook.Name, FileFormat:=xlWorkbookNormal
End Sub
If you want to look for an * anywhere in the cell you could use instr()
Private Sub CommandButton7_Click()
Dim ws As Excel.Worksheet
Dim iCol As Integer
Dim strName As String
Dim iIndex As Integer
'Loop through the sheets.
For iIndex = 1 To Application.Worksheets.Count
Set ws = Application.Worksheets(iIndex)
'Loop through the columns.
For iCol = 1 To ws.UsedRange.Columns.Count
'Check row 1 of this column for the char of *
If instr(ws.Cells(1, iCol).Value, "*") > 0 Then
'We have found a column with the char of *
ws.Columns(iCol).EntireColumn.Delete
End If
Next iCol
Next iIndex
ActiveWorkbook.SaveAs Filename:="C:\temp\newfiles\" & ActiveWorkbook.Name, FileFormat:=xlWorkbookNormal
End Sub
Here is a basic loop files in a given directory. Hope this gets you there.
Private Sub CommandButton7_Click()
Dim wb As Workbook
Dim ws As Excel.Worksheet
Dim iCol As Integer
Dim strName As String
Dim iIndex As Integer
Dim strPath As String
Dim strFile As String
strPath = "c:\temp\oldfiles\"
strFile = Dir(strPath & "*.xlsx")
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strPath & strFile)
'Loop through the sheets.
For iIndex = 1 To Application.Worksheets.Count
Set ws = Application.Worksheets(iIndex)
'Loop through the columns.
For iCol = 1 To ws.UsedRange.Columns.Count
'Check row 1 of this column for the char of *
If InStr(ws.Cells(1, iCol).Value, "*") > 0 Then
'We have found a column with the char of *
ws.Columns(iCol).EntireColumn.Delete
End If
Next iCol
Next iIndex
wb.SaveAs Filename:="C:\temp\newfiles\" & wb.Name, FileFormat:=xlOpenXMLWorkbook
wb.Close SaveChanges:=False
strFile = Dir
Loop
End Sub
