Microsoft Excel
Sort macro
created a macro which solves the problem.
this macro helps a lot while we are comparing and analysing huge data.
Macro:
Sort Macro
Sub SortData() 'Sort all the data in this book based on this column in ascending order.
'once sorted, go to the first cell of data in this column.
iDepth = iDepth + 1
iDepth = 1
Call FinalizeMacro
Call InitializeMacro 'Suspend screen updating & autocalculation & save current statusbar setting.
Call LogUsage("Conversions", "SortData") ' Log the use of this macro
Range("A1", ActiveSheet.Cells.SpecialCells(xlLastCell).Address()).Sort Key1:=Range(ActiveCell.Address), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveCell.EntireColumn().End(xlUp).Offset(1, 0).Select
Call FinalizeMacro 'Restore screen updating & autocalculation & reset previous statusbar setting.
iDepth = iDepth - 1
End Sub
Function FinalizeMacro()
If iDepth = 1 Then
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = bOldStatusBar
Application.ScreenUpdating = True
End If
End Function
Function InitializeMacro()
If iDepth = 1 Then
Application.Calculation = xlCalculationManual
bOldStatusBar = Application.DisplayStatusBar
Application.ScreenUpdating = False
End If
End Function
Public Sub LogUsage(sMacroSheet As String, sMacroName As String)
' Close #1: Open "c:\DataServMacroUsageReport.txt" For Append As #1: Print #1, Application.UserName & vbTab & macroSheet & vbTab & macroName & vbTab & Now: Close #1
End Sub
Taggings:
Fetch data from an MySQL database trough excel
Mostly a large collection of data is stored in a relational database. Relational databases have the advantage that they are offering a high level of perfomance especially for a large number of data records and especially when having aggregated queries. SQL is the most common computer language to communicate and control a relational database management system (RDMS). It offers various possibilites to fetch data (e.g. a JOIN tables within one query in order to receive aggregated data from different tables in one result set). Furthermore it allows to make certain limitations (e.g. WHERE-statement and HAVING statement) as well as to group data (GROUP BY) and sorting.
Microsoft Excel is a well known spreadsheet software which offers a lot of powerful functions for data preparation and evaluation. Excel allows you to visualize data (e.g. Pivot table or various kind of charts) and it offers a lot of formulars for calculation, aggregation and reporting.
As mentioned at the beginning, data is mostly stored in databases for performance reasons but also due to the fact that a database represents a centralized storage possibility for data which can be accessed by everyone (according to the access policy of course). Webshops, ERP Systems, CMS systems, just to name but a few, are all using relational databases in most cases. MySQL is a very popular and often used RDMS but it offers not that powerful possibilites of data evaluation and reporting than Excel. So why do not user the actual data from MySQL (as the most reliable datasource), fetch them by using SQL (because of its powerful possibilities of JOINING) and process the final result set in Excel
Absolute references in Tables in Excel 2007
In MS Excel 2007 there are 3 types of cell references: absolute, relative and mixed. The use of correct type of reference provides correct behavior when dragging of the formula using references to other cells:
<ol>
<li><strong>Absolute</strong> e.g. $A$1
An absolute reference is not changed while dragging. For instance in cell A1 is number 100. In cell B1 we enter a formula <code>=$A$1</code>. Dragging of B1 to B2 or C1 will not change the behavior of the formula in B2 or C1 i.e. all 3 cells with the formula will show value 100.
</li>
<li><strong>Relative</strong> e.g. A1
A relative reference is changed in both directions while the formula is dragged. Let A1 is containing number 100, A2=200 and B1=300. We enter the formula formula <code>=A1</code> in C1. Afterwards we drag the formula into C2 and D1. The formulas in these cells will be altered so that C2 and D1 will contain formulas <code>=A2</code> (shows 200) and <code>=B1</code> (shows 300) respectively.
</li>
<li><strong>Mixed</strong> e.g. $A1
A mixed reference is dynamically changed in one direction only e.g. $A1 has a absolute column and relative row reference.
</li>
</ol>
The problem is that such notation is not working when using tables in Excel 2007. We want to find out how to make a absolute reference to the whole column of a table.
The syntax of relative reference to Column1 of Table1 is <code>=Table1[Column1]</code>
Taggings:
Fasten export from Microsoft Dynamics NAV to Microsoft Excel
<p>Automatic exports from Dynamics NAV to Microsoft Excel take long. There are various Excel-exports implemented in Dynamics NAV. For example you can export budgets to excel. There exists user dislike waiting for a couple of minutes until the export of huge amounts of data finishes.</p><p>If you have to export huge data amounts the export will take comparatively long.</p>
