CODEWITHSUNDEEP
ms-access-2010import-from-excel
himanshu
himanshu

Reputation: 1980

how to write a macro in access 2010 to import data of excel sheet to access 2010 table?

I want to write the Code which imports data from an excel table to a access table on a button click presents in a form.Because I am new in MS Access programming so pls explain answer briefly.

Edit:-

One thing i forgot to write that i am using webform not a simple form.At one forum i find that import can be achieve by using TransferSpreadSheet macro but i didn't get it in access 2010 macro commands.

Please give me some direction so that i can solve my problem. Thanx in advance.

I have written the below code in my module 

Private Sub ImportButton_Click()
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "imported data",    "C:\Documents and Settings\himanshu\Desktop\Sales Report1.xls", True
End Sub

however when i click on button nothing happend. Is there anything i am mising?

Upvotes: 0

Views: 22061

Answers (1)

hawbsl
hawbsl

Reputation: 16033

Have a look at DoCmd.TransferSpreadsheet. So, in VBA you would have Sub or Function with a line something like this in it:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", "c:\MyExcelFile.xls", True

If you prefer to use a macro you can do something similar using the TransferSpreadsheet Action. But using VBA will give you more flexibility over using a macro.

Upvotes: 1

Related Questions