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

    Unanswered: Looking for a better solution

    I have the following function that exports data from a query. Data was user-entered and saved to a table. The function transport the data into an excel file at specific rows and columns. Please ignore the xlApp and loop stuff, some of which was omitted because it is irrelevant to the question.

    strSQLInit: Table RptCardDataCells, rowNumber cooresponds to the row number on the excel sheet

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim strSQL(150) As String
    Dim strSQLInit As String
    Dim varValues As Variant
    DestName = "C:\Users\User\Desktop\QID\Reportcard-FY15.xlsx"
    strSQLInit = "SELECT rowNumber FROM RptCardDataCells WHERE ctlName = "
    'Miles & Minutes
    strSQL(0) = strSQLInit & """SumofTotMiles"""
    strSQL(1) = strSQLInit & """SumofLdMiles"""
    strSQL(2) = strSQLInit & """SumoffltMinutes"""
    'Agencies - REFERRING
    strSQL(3) = strSQLInit & """SumOfBaptistHealthMadisonville"""
    strSQL(4) = strSQLInit & """SumOfBreckenridgeHealthHosp"""
    strSQL(5) = strSQLInit & """SumOfCaldwellCountyMedCentHosp"""
    strSQL(6) = strSQLInit & """SumOfCarleFoundationHosp"""
    strSQL(7) = strSQLInit & """SumOfClarkRegionalMedCentHosp"""
    strSQL(8) = strSQLInit & """SumOfClayCountyHosp"""
    Set xlApp = New Excel.Application
        With xlApp
            .Visible = True
    Set xlWB = .Workbooks.Open(DestName)
        With xlWB
            With .Sheets("2003TC")
                'Column determination
                    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
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL(0))
        rowValue = rs!rowNumber
        varValues = DLookup("SumOfTotMiles", "CAMTS_MilesMinutes")
            .Cells.Range(colValue & rowValue).Value = varValues 'Total Miles
    Set rs = db.OpenRecordset(strSQL(1))
        rowValue = rs!rowNumber
        varValues = DLookup("SumOfLdMiles", "CAMTS_MilesMinutes")
            .Cells.Range(colValue & rowValue).Value = varValues 'loaded Miles
    Set rs = db.OpenRecordset(strSQL(2))
        rowValue = rs!rowNumber
        varValues = DLookup("SumofFltMinutes", "CAMTS_MilesMinutes")
            .Cells.Range(colValue & rowValue).Value = varValues 'flt Minutes
    'Referring Agency
    Set rs = db.OpenRecordset(strSQL(3))
        rowValue = rs!rowNumber
        varValues = DCount("[refAgency]", "[CAMTS_AgencyMisType]", "[refAgency] = 'Baptist Health Madisonville'")
                .Cells.Range(colValue & rowValue).Value = varValues 'Baptist Health Madisonville
    Set rs = db.OpenRecordset(strSQL(4))
        rowValue = rs!rowNumber
        varValues = DCount("[refAgency]", "[CAMTS_AgencyMisType]", "[refAgency] = 'Breckenridge Health Inc.'")
            .Cells.Range(colValue & rowValue).Value = varValues 'Breckenridge Health Inc.
    Set rs = db.OpenRecordset(strSQL(5))
        rowValue = rs!rowNumber
        varValues = DCount("[refAgency]", "[CAMTS_AgencyMisType]", "[refAgency] = 'Caldwell County Medical Center'")
             .Cells.Range(colValue & rowValue).Value = varValues 'Caldwell County Medical Center
    Set rs = db.OpenRecordset(strSQL(6))
        rowValue = rs!rowNumber
        varValues = DCount("[refAgency]", "[CAMTS_AgencyMisType]", "[refAgency] = 'Carle Foundation Hospital'")
            .Cells.Range(colValue & rowValue).Value = varValues 'Carle Foundation Hospital
    Set rs = db.OpenRecordset(strSQL(7))
        rowValue = rs!rowNumber
        varValues = DCount("[refAgency]", "[CAMTS_AgencyMisType]", "[refAgency] = 'Clark Regional Medical Center'")
            .Cells.Range(colValue & rowValue).Value = varValues 'Clark Regional Medical Center
    Set rs = Nothing
    There is more code below that ends with statements and closes stuff I said, irrelevant.

    I'm looking for a more compact way of doing this. I would like to avoid writing 150+ SQL statements and just being so repetitive.

    Last edited by Pis7ftw; 05-25-15 at 00:59.
    Version: Access 2010

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 23
    No need for any of this code.
    It is a single statement, build a query with the results you want in the workbook
    export it via macro:

    TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQRY, vFILE, True, vSheet

  3. #3
    Join Date
    Nov 2012
    Well...there actually is a reason for at least some of the code. "Client" requires the data in a specific format on a specific excel template, hence the row numbers and column number dictation.

    Column number is easy, based on date. Row number is tricky...based on the position of the agency on the excel template. Previously I hardcoded the row number. Now the row numbers are pulled from a table...making it only slightly less tedious to add a new agency and correspond that to a row number on the excel sheet.

    Eventually what I want is a complete function where a user can update the excel sheet and update the agency row numbers on the table without me having to change any code. Being only a mere mortal at this thing it's beyond my capabilities to piece together. I only suspect that a few intricate loops are involved.

Posting Permissions

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