Home > Solution Categories > Microsoft Office > Transfer data from Excel to Access.
Question
No of visitors who read this post: 217
Type: Question
Author: Heather Finn
Your rating: None Average: 5 (1 vote)

Is there a faster way to add data from Excel 2010 to an Access 2010 database using VBA?

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

# (Solution Accepted)

Submitted by Gideon Yasti on Wed, 2012-04-04 05:58. Comment Id: 52451

Hello,

You want to import data from excel to access. That’s why you need do import data from excel to access first and then normalize the data by using Table Analyzer Wizard. Finally you should connect access data from excel spreadsheet.

At first import all data from excel to access with attentively. Then drag selected columns to a new table for create relationship. Then you should use button commands to rename a table then you should add a primary key then you should make an existing column a primary key. After complete previous process let you should connect access data.

Thanks.

#

Submitted by Sjamora on Mon, 2012-04-16 05:57. Comment Id: 54385
Hi,
 
If you are trying to do some VBA codes for transferring Excel data to Access, then here is a VBA script that uses RecordSet in Importing XLS data, this might be able to help you out:
 
  • First, you have to create a new form in MS Access, which could be found on "Create" tab, then click "Form Design" button;
  • Then, create two objects (a text box and a button) in the form that you just created;
  • Then, click the button you have created;
  • You are now redirected to the VBA editor where you would be able to insert lines of codes.
  • Always remember to place the VBA code inside this function call button property "Click".

Private Sub Command8_Click()

<--- lines of codes here-->
 
End Sub
 
For this question, insert these lines of codes:
 
Dim rs as New ADODB.Recordset
 
Dim cn as New ADODB.Connection
 
Dim cmd as New ADODB.Command
 
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
 
cn.ConnectionString = "Data Source=" TextBox1.Text; " & " Extended Properties=Excel 8.0;" //Note: TextBox1.Text should contain the path of where the Excel File you want to Import is located.
 
cn.Open
 
Set cmd.ActiveConnection = cn
 
cmd.CommandType = adCmdText
 
cmd.CommandText = "Select * From [Sheet1$]" //Note: You can do filtering on this query if needed. Sheet1$ is the first worksheet on your data source spreadsheet.
 
rs.CursorLocation = adUseClient
 
rs.CursorType = adOpenStatic
 
rs.LockType = adLockReadOnly
 
rs.Open cmd
 
While Not rs.EOF
 
<--- do some stuff in here-->
 
rs.MoveNext
 
Then, try running the form you have made. Done. You should now be able to add your Excel data into Access using VBA codes.
Hope this helps.
 
external data-import excel spreadsheet
 
Good Luck.
 
~Sjamora
Previous Previous Page
211 of 390
Next Page Next