Thursday, November 30, 2006 - 01:55 pm, by: Antony Borlase(Borlase)
Got a couple of questions regarding VBA, more specifically using a VBA For/IF/Next loop to summarise a excel dump of records to graphing purposes.
Wondering the best way of converting 4 or so columns on a file that is from 5 to 20,000 lines long into a table on a new sheet, and then generating the graphs from this (tried Pivot tables by some values may be present on one project, but not the other 5)
Thursday, November 30, 2006 - 10:10 pm, by: Antony Borlase(Borlase)
It is a Excel or CSV dump from an SQL database.
In short, halfway through doing the third of five 'Make life easier for the project staff' by designed/creating macro's to all all projects to get the same repaetable results on output from our EAD system. The code I have so far (and ignore the big long sections of texty stuff, I've only been doing this for a month)
Sub NCRtest() ' ' Non-Conformance Report Macro ' Macro created 24/11/2006 by aborlase ' ' Setup numbers Dim Lines As Integer Dim Status As Integer Dim TTC As Integer Dim Cause As Integer Dim NCR0, NCR1, NCR2 As Variant
' Make everything look hidden and fast ActiveSheet.Name = "Data" Application.ScreenUpdating = False Application.DisplayAlerts = False Application.StatusBar = "Summarising NCR Export for Report" ActiveWindow.Zoom = 75
'Get rid of blank sheets (there can only be one!) Dim sh As Variant For Each sh In Sheets If Not IsChart(sh) Then If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete End If Next sh
' First find out how big FinalRow = Cells(65536, 1).End(xlUp).Row FinalCol = Cells(1, 255).End(xlToLeft).Column
' We now have the Summary Worksheet so lets names the ranges NCR0 = Worksheets("Summary").Range("C3:J14") NCR0 = Worksheets("Summary").Range("K3:R14") NCR0 = Worksheets("Summary").Range("S3:Z14")
' Now do For...Next...If loop for each line/Status/TTC/Cause Worksheets("Data").Select For Lines = 2 To FinalRow For Status = 0 To 2 For TTC = 1 To 8 For Cause = 0 To 14 Check = Status & TTC & Cause If Cells(Lines, 1).Value = Check Then If Cells(Lines, 14) = 0 Then NCR0(Cause, TTC) = NCR0(Cause, TTC) + 1 ElseIf Cells(Lines, 14) = 1 Then NCR1(TTC, Cause) = NCR1(TTC, Cause) + 1 ElseIf Cells(Lines, 14) = 2 Then NCR2(TTC, Cause) = NCR2(TTC, Cause) + 1 ' End If End If Next Cause Next TTC Next Status Next Lines
' We have all values now lets place them into the summary Sheets("Summary").Range("C3").Select For TTC = 1 To 8 For Cause = 0 To 14 For Status = 0 To 2
' Make everything look normal again and show problems if there Exits: Sheets("Summary").Cells.EntireColumn.AutoFit Application.ScreenUpdating = True Application.DisplayAlerts = True Application.StatusBar = False
End Sub
Public Function IsChart(sh) As Boolean Dim tmpChart As Chart On Error Resume Next Set tmpChart = Charts(sh.Name) IsChart = IIf(tmpChart Is Nothing, False, True) End Function
I am pretty sure this extracts the data from across each line, but trying to display the array results is just causing me to flip (more so than I normally am)
Friday, December 01, 2006 - 01:19 am, by: Benny Gammelmark(Oldfield)
Read the bloody data into an access database with a field for each coloumn and export the stuff to Excel. In that way you can do whatever you like with the data.
It will mean that you will have to do a bit of "manual" work the first time but it'll be the quickest way.
Then when everything works that way, make a script that does the export for you.
NB! You should NEVER code variable or semi-variable headings. They should reside in a table.
The code above looks very much like the type of code I would have used when I started 25 years ago (although a bit more simplistic since we didn't have Excel).
And btw: I HATE MICROSOFT PROGRAMMING. It's the only company that ever got away with a flaky language translator.
Friday, December 01, 2006 - 09:12 am, by: Antony Borlase(Borlase)
Unfortunately these macro's, which I shall be converting in VB Express to standalone apps, are to be circulated to 7 project teams, when means about 5-15 people each project depending on size.
So the manual work you mention would have to be done by 40 or so people, at different times for different reasons on the same data (not all the sites we work on have any communications except for our little satellite dishes)
Benny Gammelmark wrote on Friday, December 01, 2006 - 01:19 am:
Read the bloody data into an access database
Why? we already have a sweet XLS version with the right data in columns.
Maybe I am over-complicating things, but Auto sum/Pivot tables don't work for the graphical results as some projects may not have 'Commissioning' issues yet, and MS doesn't handle NULL values well.
I will have to find a easy way of using these variables in the (so-called) advanced filter, and look at transposing those figures.
The biggest laugh I had is that its bloody similar to Basic that I first used on a Commodore 4016 (way before the VIC 20 everyone), only now you have lovely colours...oh be still my beating heart!
The other thing about this whole macro, is once it works on one DB dump, the applications to run it can have a couple more selection boxes to then allow it to run all the basic project reports. I have finished the ones that do the forecasting on our 5 projects, and also one that does productivity and loss with the costings dumps. Just trying to make many projects all have the same reporting/analysis methods.
Friday, December 01, 2006 - 01:13 pm, by: Benny Gammelmark(Oldfield)
I still think it would be easier to load into an Access database (or use it directly as a linked table) and do the work there. Export can be automated but if it's a report you need it wouldn't be that hard to write it in Access.
The nulls issue could also just be done with an update query.
Monday, December 04, 2006 - 01:51 am, by: Toan Nguyen(Soarer_gt)
He needs to export the script to VBE and other applications which negates the import/export idea to MS Access.
I'm with you though. For that amount of information, you need a centralised db or at least a transaction db per team which after processing can sync with the main db. Doing it in excel and VBA will just cause more issues down the track, but it seems the methods and processes that are currently in place seem to work.
It's like those transition periods companies go through where the current technology has reached it's limits. eg. Using excel to do payroll for 3 people is fine... Using excel to do payroll for 500 people starts to be painful.