Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Looking for an easier solution

    I have an extensive Excel report that gets populated with data every month. The data comes from user-entered records in my Access database. The Excel report is required in its current format and the powers that be will not accept an Access report with the same data.

    The Excel report has some 400 rows of data and it is divided into sections (Facilities, Number of People, Miles, Minutes, Ages, etc.)

    I have an Access Report (that is much prettier) that compiles the data for each of those sections where it can be reviewed easily. Once it is reviewed, it is exported to the Excel report at the click of the button.

    The data is compiled by month. The dates are selected in a central report compilation form.

    I will use the Facilities section as my example as it is the most extensive section of the Excel report. My module looks something like this:

    Code:
    strRpt = "CAMTS_AgencyMisType"
    
    DestName = "Excel Report File Here"
    
    Set xlApp = New Excel.Application
        With xlApp
            .visible = True
            
    Set xlWB = .Workbooks.Open(DestName)
        With xlWB
            With .Sheets("2003TC")
                
                'COLUMN DETERMINATION, BASED ON DATES SELECTED IN THE CENTRAL REPORT COMPILATION FORM
                    Select Case Forms!Reports!txtStart
                        Case "Jul"
                            colValue = "C"
                        Case "Aug"
                            colValue = "D"
                        Case "Sep"
                            colValue = "E"
                        Case "Oct"
                            colValue = "F"
                        Case "Nov"
                            colValue = "G"
                        Case "Dec"
                            colValue = "H"
                        Case "Jan"
                            colValue = "I"
                        Case "Feb"
                            colValue = "J"
                        Case "Mar"
                            colValue = "K"
                        Case "Apr"
                            colValue = "L"
                        Case "May"
                            colValue = "M"
                        Case "Jun"
                            colValue = "N"
                    End Select
                    
                    'HOW I GET THE DATA FROM THE REPORT TO THE EXCEL SHEET. colValue = Month and correlates to the column
                            '40's
                            .Cells.Range(colValue & 43).Value = Reports(strRpt)!txtFirstFacility
                            .Cells.Range(colValue & 44).Value = Reports(strRpt)!txtSecondFacility
                            .Cells.Range(colValue & 45).Value = Reports(strRpt)!txtThirdFacility
                            .Cells.Range(colValue & 46).Value = Reports(strRpt)!txtFourthFacility
                            .Cells.Range(colValue & 47).Value = Reports(strRpt)!txtFifthFacility
                         etc. etc. etc about 300+ more times
    The problem is, sometimes I have to add new rows to the Excel sheet to add another facility in this instance. This means that I have to go back in the module, and update the row numbers (colValue & rowNumber).

    I want to be able to change the row numbers in a table rather than do so in the code. This will make it much friendlier to work with and less prone to mistakes.

    What I can't figure out is how I can relate the row number in the .Cells.Range(colValue & rowNumber).Value = Reports....etc. lines to the row number on the Excel Report

    Here's what I did so far, and it works, but it looks very redundant and I think there is surely a better way to do it.

    Code:
    strSQL(0) = "SELECT rowNumber FROM RptCardDataCells WHERE ctlName = ""SumofTotMiles"""
    strSQL(1) = "SELECT rowNumber FROM RptCardDataCells WHERE ctlName = ""SumofLdMiles"""
    strSQL(2) = "SELECT rowNumber FROM RptCardDataCells WHERE ctlName = ""SumoffltMinutes"""
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL(0))
        rowValue = rs!rowNumber
                .Cells.Range(colValue & rowValue).Value = Reports!(strRpt)!txtFirstFacility
    
    
    Set rs = db.OpenRecordset(strSQL(1))
        rowValue = rs!rowNumber
                .Cells.Range(colValue & rowValue).Value = Reports!(strRpt)!txtSecondFacility
    
    
    Set rs = db.OpenRecordset(strSQL(2))
        rowValue = rs!rowNumber
                .Cells.Range(colValue & rowValue).Value = Reports!(strRpt)!txtThirdFacility
    
    etc. etc. 300+ times
    
    rs.Close
    Set rs = Nothing
    I've also considered drawing the data from the queries themselves but I'm not sure how to do that for this scenario.

    Anybody want to give it a shot?
    Version: Access 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it should be possible without having to write god knows how many lines.
    I guess you could generate the (Excel) line number as part of the query.
    or you could create your query, and stuff lines into Excel as part of the VBA process.
    what its going to come down to is how you marshall the data, effectively thats the query.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    You know, the facilities are not hard coded into the database. They reside in a contact list table that is modifiable by users. I could add an "administrative" field that includes a value that correlates to the row number on the report. At that point though, I have two queries to work with:
    Code:
    .Cells.Range(colValue & contactLISTqueryFORrowNumber).Value = Value from report field. (or query)
    The query would be something along the lines of:
    Code:
    strSQL = "SELECT rowNumber FROM ContactList WHERE FacilityName= """Report!ReportName!lblChuckECheese.Caption""""
    'In this case the facility name in lblChuckECheese is the exact same as the name of the facility in the contact list
    So:
    Code:
    .Cells.Range(colValue & strSQL).Value = Report!ReportName!txtFacilityValue
    I've never been successful referencing a control in a SQL expression that resides in a different form though. This should work if I can get the SQL statement right. How would I get "SELECT rowNumber FROM ContactList WHERE FacilityName= """Report!ReportName!lblChuckECheese.Caption"" "" to work?
    Version: Access 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •