Moderators JLogan3o13 Posted June 18, 2013 Moderators Share Posted June 18, 2013 (edited) I have been working on a project for an insurance customer to connect to an Oracle db and pull a bunch of policy holder info. They would then like the data placed into an Excel spreadsheet. Unfortunately, because the data sets that I need to work from will be different each time, I'm finding it difficult to fulfill the objectives. 1. I pull the data from the database and plug it into a temp excel spreadsheet, performing some minor formatting. The temp worksheet looks like this: 2. I create an array of unique effective dates, and an array of unique class codes. Always 7 effective dates (not always the same dates), but anywhere from 1 to 16 Class Codes. These numbers are then plugged into the Master worksheet: 3. I then need to iterate through each row in the temp worksheet. I need to plug the sum value into the correct cell, by both the class code and the effective date. I was able to create something that works very well (below), but only when I know the value and number of class codes: $b = 32 $c = 32 For $i = 2 To 500 $ccode = _ExcelReadCell($oExcel, $i, 1) Select Case $ccode = "" ExitLoop Case $ccode = "0042" $line = $b $effDate = _ExcelReadCell($oExcel, $i, 5) $sum = _ExcelReadCell($oExcel, $i, 3) _ExcelWriteCell($oExcel1, $effDate, $b, 1) _ExcelWriteCell($oExcel1, $sum, $b, 3) $b += 1 Case $ccode = 5183 $line1 = $b $effDate = _ExcelReadCell($oExcel, $i, 5) $sum = _ExcelReadCell($oExcel, $i, 3) _ExcelWriteCell($oExcel1, $effDate, $c, 1) _ExcelWriteCell($oExcel1, $sum, $c, 4) $c += 1 EndSelect Next _ExcelBookSaveAs($oExcel1, @DesktopDir & "\Output.xls", "xls", 0, 1) I'm guessing I would need to do something like this, but have yet to get it to work: Start with the array of class codes. Loop through each row in the temp workbook. On each row, find the effective date and jump to that row in the Master Workbook. On each row, find the class code and jump to that column in the Master Workbook. Write that row's SUM value to the corresponding cell in the Master Workbook. Any suggestions would be greatly appreciated, or if there is another way to do this logically. Edited June 24, 2013 by JLogan3o13 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted June 19, 2013 Share Posted June 19, 2013 Could you please modify your example so that the variables get meaningful names? Would help to understand your code. If $i is the linename then please name it $iLine, the currently processed column should be named $iColumn etc. Could save a lot of time to help you. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted June 24, 2013 Author Moderators Share Posted June 24, 2013 Thanks for the suggestion, I went through and tidied the code a bit. I believe I have resolved the issue as well, though I am always open to suggestions for improvement (maybe a better way to get the index rather than going back for it each time). Below is the current, working solution: Gather the list of Effective Dates: Func _writeEffectiveDates() $rowCount = $oExcel.Cells.CurrentRegion.Rows.Count $aTempArray = _ExcelReadArray($oExcel, 1, 5, $rowCount, 1) _ArrayDelete($aTempArray, 0) Global $aDates = _ArrayUnique($aTempArray) $iColumn = 1 ;Begin inserting data in the first column on the Master workbook. $iRow = 32 ;Begin inserting date on this row on the Master workbook. For $i = 1 To $aDates[0] _ExcelWriteCell($oExcel1, $aDates[$i], $iRow, $iColumn) $iRow += 1 Next EndFunc Then gather the list of CLASS CODES: $rowCount = $oExcel.Cells.CurrentRegion.Rows.Count $aTempArray = _ExcelReadArray($oExcel, 1, 1, $rowCount, 1) _ArrayDelete($aTempArray, 0) Global $aCCodes = _ArrayUnique($aTempArray) _ArraySort($aCCodes, 0, 1) $iColumn = 3 ;Begin inserting data in the first column on the Master workbook. $iRow = 30 ;Begin inserting date on this row on the Master workbook. For $i = 1 To $aCCodes[0] _ExcelWriteCell($oExcel1, $aCCodes[$i], $iRow, $iColumn) $iColumn +=1 Next Finally, find the row and column the SUM value needs to go into, based on the Effective Date (row) and CLASS CODE (column) $rowCount = $oExcel.Cells.CurrentRegion.Rows.Count $iCCColumn = 1 $iSumColumn = 3 $iEffColumn = 5 $iStartRow = 31 $iStartColumn = 2 For $i = 2 To $rowCount $cCode = _ExcelReadCell($oExcel, $i, $iCCColumn) $sum = _ExcelReadCell($oExcel, $i, $iSumColumn) $effDate = _ExcelReadCell($oExcel, $i, $iEffColumn) $iIndex1 = _ArraySearch($aDates, $effDate, 0, 0, 0, 1) $iIndex2 = _ArraySearch($aCCodes, $cCode, 0, 0, 0, 1) _ExcelWriteCell($oExcel1, $sum, $iStartRow + $iIndex1, $iStartColumn + $iIndex2) Next "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now