If you have learned about Excel, you will realize that Excel has a huge disadvantage. Specifically Excel 2010 is limited to 1.048.576 rows and 16.384 columns.
So to solve this problem, how do we do it?
You can solve this problem by saving data in a separate database management system. Then take the data back from that database to make a report.
Hạt will share how to import data into Access from Excel and retrieve data from Access to Excel through several articles. This article is about how to create an Access database from Excel. Please follow the post.
Create an Access database from Excel
First, you create a Database.accdb file with no data like the image, with an accdb extension of Access.
Next, you create an Excel file to import data and retrieve report data. You can name the file Learning.xlsm.
Then, press Atl + F11 to open the programming window and create a new Module.
Below is the code to create a data table called Learning.
Dim oConn As Object
Dim myRecord As Object
Dim sConn As String
Dim s As String
Dim Query As String
Dim ItemNumber As String
s = "A:\Database.accdb"
'Correct your drive\
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & s
Set oConn = CreateObject("ADODB.Connection")
Query = "CREATE TABLE Learning " _
& "(Knowledge CHAR, Subjects CHAR);"
Explaining the code:
This is the code to create Learning data table with 2 columns: knowledge and subjects.
It works based on the CreateObject () function. After creating a connection to the Database file (stored in drive A), the VBA code will run a Query to create the data table with the specified data type columns.