Excel/MDB 5.0

Do you know ?


I am not sure if you have noticed this with some of Excel older versions for Eg: 2003. When you have a mdb which has more than 65,536 rows lets say 65,537, now when you try to pull in these mdb records into Excel? What happens then. You will notice that there will not be any error message flashed by Excel. So it hard to catch it, but the cause is pretty simple. We are trying pull in more rows than the capacity of Excel which is not possible.


This limitation of Excel 2003 is not present in Excel2007.


Cheers!!

Excel declaring same range twice in the Worksheet Post 4.0

Today I would be sharing about a small & simple thing. But this is indeed an important thing and might lead to trouble in case you are not aware of it!!
This  limitation is with Excel 2003 and some lower versions of excel. Try declaring some range name Test in Sheet1!A1.
Now again go to Sheet2!A1 and give again same range name in this cell. Excel quietly gives this name to this cell without popping any Error message(That range name already exists). So in this process what happens is that the range name Test which was there in Sheet1!A1 is deleted implicitly. 


Impact:As a result of this wherever you were using Test (Sheet!A1) now will start using Sheet2!A1 so that calculation will start going wrong in the worksheet/Workbook.


Solution: To avoid this Issue whenever you declare any new range just check for the range name if already exists. To check this you can go to keyboard Ctrl+G and enter the new range name to see if it exits.


Now one good thing is that Excel higher versions like Windows Vista does not have this Issue and Excel throws a warning message explicitly.

Connect to DataBase from VBA/Excel Post 3.0

Through VBA we can connect Microsoft Database(MDB) and can access the data from tables in the MDB. This data can be populated in the Excel sheet from these tables.
I have already explained some of the basics regarding VBA in my earlier posts. So today we will see how we can connect to MDB through VBA.
Below is the code which can be used to pull data from MDB and populate in Excel:
Before moving the actual code we should understand what is .accdb file. The accdb file format was introduced with release of Microsoft Access 2007 to replace the older MDB file format. So when we use ACCDB format allow users to leverage enhanced fucntionality in access ,including the use of multivalued fields and strong cryptography.




Sub prConnecttoMDB()
On Error GoTo Err_prConnecttoMDB
Dim strConnection   As String
Dim strSQl          As String


strConnection = "ODBC; DSN=MS Access Database;DBQ=D:\LearnVBA\Test_01.accdb; Driver={Driver do Microsoft Access (*.accdb)}"
strSQl = "SELECT TestTable.SerialNo, TestTable.MemberName, TestTable.School,TestTable.NativePlace,TestTable.Income FROM TestTable"


With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=ActiveSheet.Range("Test"))
    .CommandText = strSQl
    .Name = "TestTable"
    .Refresh BackgroundQuery:=False
End With


Exit_prConnecttoMDB:
    Exit Sub


Err_prConnecttoMDB:
    GoTo Exit_prConnecttoMDB
End Sub

The below code pulls the data from the MDB file placed at D:\LearnVBA\Test_01.accdb to Excel active sheet in range Test.