Change in format warning

[Smythe]

Hi
I recieve a spreadsheet daily which I then apply a macro to do do various bits and bobs. What I have found is that the spreadsheet does occasionally change in format or layout - for example very occasionally a new column is added. This makes my macro fall over.

Is there a way of the macro first performing a check to ensure that the format is exactly as it should be? or if there has been a change in columns to issue some sort of warning? Macro below if that helps

Sub Dublin()
'
Application.WindowState = xlMinimized
'remove formats and cellmerge etc
Cells.Select
Selection.ClearFormats
'deletes first row to remove heading
Rows("1:1").Select
Selection.Delete Shift:=xlUp
'removes any hidden rows
Cells.Select
Cells.EntireRow.AutoFit
'formats premium column as numeric
Columns("J:J").Select
Selection.NumberFormat = "0.00"
'names column U1 "date"
Range("U1").Select
ActiveCell.FormulaR1C1 = "date"
Range("V1").Select
ActiveCell.FormulaR1C1 = "dummy"
                
'extracts the date from the Dublin sheet
Range("AA3").Formula = "=MID(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1))+1,LEN(CELL(""filename"",A1))-FIND(""]"",CELL(""filename"",A1))+11)"
Range("AA4").Formula = "=RIGHT(AA3,10)"
With Range("AA5")
    .NumberFormat = "mm/dd/yyyy"
    .Formula = "=SUBSTITUTE(AA4,"" "",""/"")+0"
End With
'adds extracted date into new date column "U"
Range("AA5").Select
Selection.Copy
Range(Range("U" & Rows.Count).End(xlUp).Offset(1, 0), Range("T" & Rows.Count).End(xlUp).Offset(0, 1)).Select
    
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("U2").Select
Selection.Copy
Range(Range("V" & Rows.Count).End(xlUp).Offset(1, 0), Range("U" & Rows.Count).End(xlUp).Offset(0, 1)).Select
    
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
'format column to date
Columns("U:U").Select
Selection.NumberFormat = "mm/dd/yyyy"
'format x column
Columns("V:V").Select
Selection.NumberFormat = "General"

'remove workings
Columns("AA:AA").Select
Selection.Delete Shift:=xlToLeft

Application.DisplayAlerts = False
'save in Dublin Folder
With ActiveWorkbook
    .SaveAs Filename:="C:\Blah" & .Name
End With

'save as CSV for SAS import
With ActiveWorkbook
    .SaveAs Filename:="D:\Dublin.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End With
Application.DisplayAlerts = True
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
 
End Sub

 

[perco754]

I would consider preventing the users of that spreadsheet from adding/changing anything that has to do with the structure of the sheet such as adding/deleting rows/columns etc. Do that by using the locking mechanism provided in Excel (Tools->Protection).

 

[Smythe]

Hi
Thanks Perco. Unfortunatley I have no control whatsoever over the structure of the sheet I recieve.

 

[njimack]

A better approach would be for the code to determine which columns need to be formatted/deleted based on their content, i.e searching for a particular header in row 1.

Also, you should set ScreenUpdating to False at the start of your code, and it's rarely necessary to select cells to perform actions on them. For example, this section of code:

Cells.Select
Selection.ClearFormats
'deletes first row to remove heading
Rows("1:1").Select
Selection.Delete Shift:=xlUp
'removes any hidden rows
Cells.Select
Cells.EntireRow.AutoFit
'formats premium column as numeric
Columns("J:J").Select
Selection.NumberFormat = "0.00"
'names column U1 "date"
Range("U1").Select
ActiveCell.FormulaR1C1 = "date"
Range("V1").Select
ActiveCell.FormulaR1C1 = "dummy"

could/should be written as:

With Cells
    .ClearFormats
    .EntireRow.AutoFit
End With
'deletes first row to remove heading
Rows("1:1").Delete Shift:=xlUp
 
'formats premium column as numeric
Columns("J:J").NumberFormat = "0.00"
'names column U1 "date"
Range("U1") = "date"
Range("V1") = "dummy"

 

[perco754]

Ok, sorry but then I don't know how you should move on.
If you can't assume anything at all converning format/layout etc. then I guess what's left is to do a lot of searching (by names if you know these?) for the right cells/rows/columns instead of hard-coded references (as you are using now).
I would also suggest you to remove all .Select and instead use objects.

For instance, after Cells.Select how do you know what Cells are selected? I mean, if you have a Workbook with several sheets, or you even have several Workbooks open, you wouldn't know.

So for example instead of Cells.Select I would have objects like this

Dim wb as WorkBook
Dim ws as WorkSheet
Dim rng as Range
 
    Set wb = Application.ActiveWorkbook
    Set ws = wb.Sheets("Name of sheet")
    Set rng = ws.Cells
    rng.ClearFormats

etc.

 

[Smythe]

Thanks guys

Will have a play around with your suggestions!

Cheers