You are here:

VB Script/Import from Excel to Access 2010

Advertisement


Question
Hello,
I have :
- Access database called Dosimetry.
- A table within called ReadingResult
- An Excel file called MicroStar that contains one sheet called Sheet1 (5 columns)
- I need a VBA script that I can run when clicking a command button. This script should import the content of Sheet1 to ReadingResult table.
Thank you
Maya

Answer
There are multiple ways to import excel data into Access table.  You can do this using Excel VBA or by using Access forms code.  Here is excel VBA example:

You can try with below code, please ensure your filenames, its path are correct.  I have considered column A to E (5 columns) and 9 rows (Row #2 to Row10) for import from excel to Access.

Sub AccImport()
   Dim acc As New Access.Application
   acc.OpenCurrentDatabase "C:\Dosimetry.accdb"
   acc.DoCmd.TransferSpreadsheet _
         TransferType:=acImport, _
         SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
         TableName:="ReadingResult", _
         Filename:=Application.ActiveWorkbook.FullName, _
         HasFieldNames:=True, _
         Range:="Sheet1$A2:E10"
   acc.CloseCurrentDatabase
   acc.Quit
   Set acc = Nothing
End Sub


Thanks
Subbu.

VB Script

All Answers


Answers by Expert:


Ask Experts

Volunteer


Subbu

Expertise

I can answer VB Script questions which are related to Visual Basic / VBA / QTP Scripts.

Experience

8 Years

Education/Credentials
Master of Computer Applications

Awards and Honors
Established Member from QA Forums and Brainbench certification

©2016 About.com. All rights reserved.