A classy alternative

[LARiot]

Hi folks.

I have this code that imports an Excel sheet into an Access table sheet by first opening a dialog box asking for it's location.

It does it's job fine (thanks to help from members in this forum). However my suspicion is that using Class Modules could in some way improve the process.

Any advice is truly appreciated.

-Neema

 

[LARiot]

... almost forgot:

Function ImportLoanSales()
    Dim cn As ADODB.Connection
    Dim rsTable As ADODB.Recordset
    
    Dim varAllFiles As Variant
    Dim strSQL As String
    Dim strFile As String
    Dim i As Integer
    
    Set cn = CreateObject("ADODB.Connection")
    Set rsTable = CreateObject("ADODB.Recordset")
    
    varAllFiles = OpenFiles()
    If blnCancel = True Then Exit Function

    For i = 0 To UBound(varAllFiles)
        strFile = varAllFiles(i)
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;MaxScanRows=0;IMEX=0"";"
        strSQL = "SELECT * FROM [Sheet1$]"
        rsTable.Open strSQL, cn

        Do While Not rsTable.EOF
            DoCmd.SetWarnings False
            strSQL = "INSERT INTO dbo_CustomDatafields" & _
                     "(" & _
                        "[LoanID], [Date Funded], [Amount Funded], [Principle Purchase Amount], [Paid to Date], " & _
                        "[Next Due Date], [Selling Price], [Investor Price %], [Investor SPRP %], " & _
                        "[Investor Tax Fee], [Investor Flood Fee], [Investor UW Fee], [Investor Misc Fee], " & _
                        "[SPM to Collect Pymt], [No Pymts SPM to Collect], [Impounds Expected], " & _
                        "[Impounds Investor Funded], [Impounds Discrepancies], [Buydown Funds Investor], " & _
                        "[Interest Investor Funded], [Investor Account #], [Investor Servicer LN], " & _
                        "[Loan Stage], [Act Buy Price], [Totlnv Fee's]" & _
                     ") " & _
                     "VALUES ('" & _
                        rsTable.Fields("Loan_ID") & "', '" & _
                        rsTable.Fields("DateFunded") & "', '" & _
                        rsTable.Fields("AmountFunded") & "', '" & _
                        rsTable.Fields("PrinciplePurchaseAmt") & "', '" & _
                        rsTable.Fields("PaidToDate") & "', '" & _
                        rsTable.Fields("NextDueDate") & "', '" & _
                        rsTable.Fields("SellingPrice") & "', '" & _
                        rsTable.Fields("InvestorPricePerc") & "', '" & _
                        rsTable.Fields("InvestorSRPPerc") & "', '" & _
                        rsTable.Fields("InvestorTaxFee") & "', '" & _
                        rsTable.Fields("InvestorFloodFee") & "', '" & _
                        rsTable.Fields("InvestorUWFee") & "', '"
            strSQL = strSQL & _
                        rsTable.Fields("InvestorMiscFee") & "', '" & _
                        rsTable.Fields("FLStoCollectPymt") & "', '" & _
                        rsTable.Fields("NoPymtsFLStoCollect") & "', '" & _
                        rsTable.Fields("ImpoundExpected") & "', '" & _
                        rsTable.Fields("ImpoundsInvestorFunded") & "', '" & _
                        rsTable.Fields("ImpoundsDiscrepancies") & "', '" & _
                        rsTable.Fields("BuydownFundsInvestor") & "', '" & _
                        rsTable.Fields("InterestInvestorFunded") & "', '" & _
                        rsTable.Fields("InvestorLoanNumber") & "', '" & _
                        rsTable.Fields("InvestorServicerLnNo") & "', '" & _
                        rsTable.Fields("LoanStage") & "', '" & _
                        rsTable.Fields("ActBuyPrice") & "', '" & _
                        rsTable.Fields("TotInvFees") & _
                     "')"
'            Debug.Print strSQL
            DoCmd.RunSQL strSQL
            rsTable.MoveNext
        Loop
        rsTable.Close
        cn.Close
    Next
    
    DoCmd.SetWarnings True
    MsgBox "Done."
End Function

Public Function OpenFiles() As Variant
    Dim varFile As Variant
    Dim strAllFiles As String
    
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Title = "Please select one or more Excel files"
        
        .Filters.Clear
        .Filters.Add "Excel Worksheets", "*.xls"
        
        If .Show = True Then
            For Each varFile In .SelectedItems
                strAllFiles = strAllFiles & varFile & "|"
            Next
            blnCancel = False
        Else
            MsgBox "You clicked Cancel in the file dialog box."
            blnCancel = True
            Exit Function
        End If
    End With
        
    strAllFiles = Left(strAllFiles, Len(strAllFiles) - 1)
    OpenFiles = Split(strAllFiles, "|")
End Function

-Neema

 

[Remou]

Is there any reason you are inserting rows from the excel sheet one by one, rather than SELECTing the whole table?

 

[LARiot]

The reason is because I didn't know I could. To be honest with you, I still don't see how.

Does the insert statement allow for such a thing? Should SQL be avoided? Please tell me more. This is very interesting.

-Neema

 

[Remou]

Something like this:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open CurrentProject.Connection

    strsql = "strSQL = "INSERT INTO dbo_CustomDatafields" & _
                     "(" & _
                        "[LoanID], [Date Funded], [Amount Funded], [Principle Purchase Amount], [Paid to Date], " & _
                        "[Next Due Date], [Selling Price], [Investor Price %], [Investor SPRP %], " & _
                        "[Investor Tax Fee], [Investor Flood Fee], [Investor UW Fee], [Investor Misc Fee], " & _
                        "[SPM to Collect Pymt], [No Pymts SPM to Collect], [Impounds Expected], " & _
                        "[Impounds Investor Funded], [Impounds Discrepancies], [Buydown Funds Investor], " & _
                        "[Interest Investor Funded], [Investor Account #], [Investor Servicer LN], " & _
                        "[Loan Stage], [Act Buy Price], [Totlnv Fee's]" & _
                     ") " & _
SELECT [i]List,TheFields,FromExcel,Here[/i] " _
    & "FROM [Sheet1$] IN '' [Excel 8.0;database=" & strFile & "];"
    cn.Execute strsql

 

[LARiot]

SELECT List,TheFields,FromExcel,Here

This part doesn't make sense. The field names are exact matches.

-Neema

 

[Remou]

It is generally considered a good idea to list the fields you wish to insert, for one, so that the order is also correct. I felt I could leave that tedious job for you.

 

[LARiot]

Sorry, I meant the field names are not an exact match.

I was just trying to figure what you meant. The commas threw me off.

Thanks for the help.

I still wonder if a Class Module would be useful here.

-Neema

 

[Remou]

I cannot see how a class module would improve the process. What were you hoping for?

 

[LARiot]

That it would so I can try one out.

Thanks for the help and the feedback.

-Neema

 

[Remou]

Here is an example of a Class module:

faq702-6304

Is this the kind of thing you wanted?

 

[LARiot]

That looks very interesting. I have never used a class module so in essence that's exactly what I wanted.

Thanks again.

-Neema

 

[JoeAtWork]

Using class modules is an architecture choice, they do nothing to make your code more efficient (since the code you would put in a class module is the same as what would be put in a regular module - albeit somewhat differently organized). In fact, one could argue they usually make your code somewhat less efficient, as it tends to add layers to the overall design (but in my opinion that is a small price in exchange for the benefits of object oriented design).

You need to understand object oriented programming to get the benefit of using class modules. In a nutshell, the benefit is better organized code.