Programming Challenge

[crobg]

I have a report from our ERP (SAP) that is saved to a text file. That report is then opened in Excel and normalized with a macro and exported to an Access table via an ADO connection. (BTW I'm using Access 97.)

The problem is that there is a column that is sometimes there and other times it is not. So we have to modify the code from week to week. I'd like to add some inteligence to the code so it knows when the extra column is present.

Here is the code:

Sub FormatReport()
    
    Dim r As Integer
    Dim sOrder As String
    
    With ShReport
        .Range("A1:A2").EntireRow.Insert
        r = 3
        
        'Copy the order type to the column
        sOrder = ""
        Do While Trim(.Cells(r, 12)) <> "Orders not Shipped - Report Total"
            If sOrder <> Trim(.Cells(r, 12)) And _
               Trim(.Cells(r, 12)) <> "Orders Past Due Date" And _
               Trim(.Cells(r, 12)) <> "" Then
               sOrder = Trim(.Cells(r, 12))
            End If
            .Cells(r, 12) = sOrder
            
            r = r + 1
        Loop
        
        'Delete the Last Page Summary report
        .Range(.Cells(r, 1), .Cells(r + 65, 1)).EntireRow.Delete
        
        'Assign Column Names to Row 1
        .Cells(1, 1) = "Order #"
        .Cells(1, 3) = "User Status"
        .Cells(1, 6) = "Acct #"
        .Cells(1, 7) = "Customer Name"
        .Cells(1, 12) = "Order Type"
        .Cells(1, 17) = "Cust Grp"
        .Cells(1, 18) = "Sales Off"
        .Cells(1, 19) = "Plant"
        .Cells(1, 23) = "PO Number"
        .Cells(1, 28) = "Sidemark"
        .Cells(1, 30) = "Date Entered"
        .Cells(1, 33) = "Date Booked"
        .Cells(1, 36) = "Requested Ship Date"
        .Cells(1, 39) = "Delivery CR/Hold"
        .Cells(1, 43) = "Material Group"
        .Cells(1, 44) = "Sales Amount"
        .Cells(1, 46) = "Order Units"
        .Cells(1, 48) = "Order Reason"
        
        
        'Delete the blank rows
        r = 3
        Do While .Cells(r, 12) <> ""
            If Trim(.Cells(r, 6)) = "" Or _
               Trim(.Cells(r, 6)) = "Customer #" Then
                
                .Range(.Cells(r, 1), .Cells(r, 1)).EntireRow.Delete
                r = r - 1
            
            End If
            r = r + 1
        Loop
        
        'Delete Extra Columns
        .Range(.Cells(1, 48), .Cells(1, 100)).EntireColumn.Delete
        .Range("AS1:AS1").EntireColumn.Delete
        .Range("An1:Ap1").EntireColumn.Delete
        .Range("Ak1:Al1").EntireColumn.Delete
        .Range("Ah1:Ai1").EntireColumn.Delete
        .Range("Ae1:Af1").EntireColumn.Delete
        .Range("Ac1:Ac1").EntireColumn.Delete
        .Range("X1:AA1").EntireColumn.Delete
        .Range("T1:V1").EntireColumn.Delete
        .Range("M1:P1").EntireColumn.Delete
        .Range("H1:K1").EntireColumn.Delete
        .Range("D1:E1").EntireColumn.Delete
        .Range("B1:B1").EntireColumn.Delete
        
        
        
    End With

End Sub

 

[MichaelRed]

w/o wading through the "code" in detail, I don't quite understand all. But (there MUST be a but?) I also within the brief scan didn't notice any indication that the code recognized the presence (or absence) of any part of the source or adjusted the target based on the indicator(s).

First issue wouold be to determine the condition which is (would be?) recognizable as the indicator of the presence / absence of the optional "column". This might be as simple as determining the actual range limits of the "data", or it could be something much nore complex. but (the inievitable ... ) you need to have this well defined and understood before anything else.

After the knowledge / understanding of how many (and possibly WHAT) fields are avgailable in the source, you can then plan how to accomodate the variance. ONE easy way might be to just ALWAYS include all of the fields, and just fill the empty "cells" with null, wheather they include an entire column or not then becomes irrelevant.





MichaelRed

 

[crobg]

Thanks, I was able to use the exisisting column headers to determine what columns were used to store the different fields. I was then able to loop throught the header row and assign the new headers accordingly. Then I replaced the extra column deletion code with a loop to delete columns with no header.

 

[easyit]

hi,

Why don't you import it straight into Access? Missing a field should be a big problem that way; there are several options:
Make use of a smart import defintion
or make two and decide after a test which of both to be used.

I'm using SAP downloads everyday, and never use Excel first. Actually, Excel gives me a headache sometimes because excel fields are preformatted; I cannot change them at import.

Maarten

 

[Darrylles]

Hi,

I understand your 'missing column' problem, because I get the same(-ish) problem with a Pervasive database via a front-end application called Paragon (in my case, it simply mixes columns across columns though - v-nasty).

I think the problem is that the export method will export all columns IF there is data in ALL columns.
If there is no data in a particular column for all rows in THAT particular export, then it won't bother exporting that column header either.

If this IS the case, then an option is to use a simple VBA function to open the text file and count the tab control chars in the first row. This will give you an either / or result. (6 headers as opposed to 7).

(I've just realised that you have found a workaround, but this gives you another if you encounter problems).

ATB

Darrylle







Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[crobg]

Easyit:
I'm just cleaning up other's work at this point. The other person was more comfortable with the Excel to Access method. What do you mean by a Smart Import definition? We are still using Office 97 and I'm not sure I've seen that.

Darrylle:
That is exactly the problem I am having. My solution looks at the row in which the headers are stored. The names and reletive position to the data stays the same, just not the exact position.

Thanks for the ideas, I will be sure to pass them on to the team.