Advise please........

[kathleen]

I have written a macro that opens an excel file generated from COGNOS then filters data by cost center (department), charts it and emails to the appropriate people. My dilemna is.......

It will only work as long as the user doesn't modify the base data. Since that information is coming from COGNOS the user can of course modify the recordset that is returned and then save the file. (no my IS department will not give me an ODBC connection or provide me with the passwords to the Oracle Databases).

Lets say they were to add a column .......... Well then structurally my file is not the same and it could cause issues (hehehe or Opportunities for a consultant). I am confirming that the file only contains one sheet and I am also confirming that the headings are as anticipated, its alot of coding. Is there an easier way to skin this horse? Is there something else I can check? I appreciate any input you EXPERTS could offer.

Thanks in advance.


Note AP Detail.xls is the file returned from COGNOS.

Sub LoadAPDetail()

'Clear AP Detail Sheet, we will replace with information as it comes to us from Cognos
'--------------------------------------------------------------------------------------------
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("AP Detail").Select
Cells.Select
Selection.ClearContents

Workbooks.Open Filename:="C:\Divisional Budget\AP Detail.xls"
Workbooks("AP Detail.xls").Activate

'Confirm that file is in expected format
'-------------------------------------------------------------------------------------------
If Workbooks("AP Detail.xls").Worksheets.Count > 1 Then NotAPDetail

Range("A1").Select

If ActiveCell.Value <> "Business Unit" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Deptid" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Account" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Vendor Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Vendor Name" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Descr" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Voucher Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Journal Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Gl Date" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Po Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Line Nbr" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Sched Nbr" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Req Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Req Line Nbr" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Req Sched Nbr" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Invoice Id" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Reversal Cd" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Source" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ActiveCell.Value <> "Amount" Then NotAPDetail
ActiveCell.Offset(0, 1).Select

If ProblemSheets <> "" Then MsgBox ("The AP Detail File has to be used exactly as it comes out of Cognos. Please rerun the AP Detail"), vbCritical
If ProblemSheets <> "" Then Exit Sub

'File is correct copy and add to main file
'-------------------------------------------------------------------------------------------
Stop

 

[al_b_cnu]

Hi,
My 2 pence worth, you could shorten your code somewhat as follows:

Sub LoadAPDetail()
Dim rData As Range, sData As String
'Clear AP Detail Sheet, we will replace with information as it comes to us from Cognos
'--------------------------------------------------------------------------------------------
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("AP Detail").Cells.ClearContents

Workbooks.Open Filename:="C:\Divisional Budget\AP Detail.xls"
'Workbooks("AP Detail.xls").Activate

'Confirm that file is in expected format
'-------------------------------------------------------------------------------------------
If ThisWorkbook.Worksheets.Count > 1 Then NotAPDetail

Set rData = Range("A1")
sData = "|Business Unit|Deptid|Account|Vendor Id|Vendor Name" & _
        "|Descr|Voucher Id|Journal Id|Gl Date|Po Id|Line Nbr|Sched Nbr" & _
        "|Req Id|Req Line Nbr|Req Sched Nbr|Invoice Id|Reversal Cd|Source|Amount||"

If InStr(sData, "|" & rData.Text & "|") <> 0 Then NotAPDetail

If ProblemSheets <> "" Then
    MsgBox ("The AP Detail File has to be used exactly as it comes out of Cognos. Please rerun the AP Detail"), vbCritical
    Exit Sub
End If

'File is correct copy and add to main file
'-------------------------------------------------------------------------------------------
Stop
End Sub

 

[kathleen422]

Thank you that is much better! Apprceiate it. Is the only way to confirm that the file is the anticipated file? You know users come up with some ingenious ways of making things BOMB!

 

[al_b_cnu]

Hi,

not too sure what you want, but this code checks if any cell in row 1 contains one of the strings "Business Unit" / "Deptid" / "Account" / "Vendor Id" etc. - if so calls your 'NotAPDetail' macro (whatever that does )

Sub LoadAPDetail()
Dim rData As Range, sData As String, R As Range
'Clear AP Detail Sheet, we will replace with information as it comes to us from Cognos
'--------------------------------------------------------------------------------------------
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("AP Detail").Cells.ClearContents

Workbooks.Open Filename:="C:\Divisional Budget\AP Detail.xls"
'Workbooks("AP Detail.xls").Activate

'Confirm that file is in expected format
'-------------------------------------------------------------------------------------------
If ThisWorkbook.Worksheets.Count > 1 Then NotAPDetail

Set rData = Range("A1", Cells(1, Columns.cout(xlToLeft).Column.Address))
sData = LCase$("|Business Unit|Deptid|Account|Vendor Id|Vendor Name" & _
               "|Descr|Voucher Id|Journal Id|Gl Date|Po Id|Line Nbr|Sched Nbr" & _
               "|Req Id|Req Line Nbr|Req Sched Nbr|Invoice Id|Reversal Cd|Source|Amount||")
For Each R In Range("A1", Cells(1, Columns.cout(xlToLeft).Column.Address))
    If InStr(Trim$(LCase$(R.Text)), "|" & rData.Text & "|") <> 0 Then
        NotAPDetail
        Exit For
Next R

If ProblemSheets <> "" Then
    MsgBox ("The AP Detail File has to be used exactly as it comes out of Cognos. Please rerun the AP Detail"), vbCritical
    Exit Sub
End If

'File is correct copy and add to main file
'-------------------------------------------------------------------------------------------
End Sub