Thread: Looking for an easier solution
11-09-14, 01:02 #1Registered User
- Join Date
- Nov 2012
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:
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
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.
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
Anybody want to give it a shot?Version: Access 2010
11-09-14, 10:02 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
11-09-14, 20:30 #3Registered User
- Join Date
- Nov 2012
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:
.Cells.Range(colValue & contactLISTqueryFORrowNumber).Value = Value from report field. (or query)
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
.Cells.Range(colValue & strSQL).Value = Report!ReportName!txtFacilityValueVersion: Access 2010