Import Excel Data to Access Table MACRO (1 Viewer)

[santoshdream]

Hi All,

I want to Import Excel file data in Access Table on Submit Button click through code. I would appreciate if you could help me in this reqard. Please find the enclosed files for the reference.

In this file export button is working fine. But I am confused in
" How to write a code in on click button to import the excel data to access table automatically.


Need urgent help.

Santosh

 

[Ron_dK]

There are lots of examples on this forum. Key words : transfer spreadsheet or import excel data.

Here's an example :
http://www.access-programmers.co.uk/forums/showthread.php?t=163138&highlight=import+excel

Hth

 

[santoshdream]

Hi,

I am trying to use the following code but its not working

My access table name is = DATA

File which I want to import name is = dps.xls

Private Sub Command3_Click()

Dim myfile As String, sql As String
Dim db As Database, rs As Recordset, rs_target As Recordset
Set db = DBEngine(0)(0)
'myfile = OpenFile("c:\d\", "xls", "*.xls", 0)
myfile = OpenFile("c:\d\", "dps", "*dps.xls", 0)
If myfile = "" Then
End
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from DATA"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DATA", myfile ', , "Importfile!"
Set rs = db.OpenRecordset("DATA")

DoCmd.SetWarnings True

MsgBox "File has been imported "
End Sub

Thanks

 

[Ron_dK]

What's the error that you get ?

Btw.: better not name a table as DATA. Data is a preserved word in Acces which might give you errors.

Hth

 

[santoshdream]

Error is showing....

COMPILE ERROR:
SUB OR FUNCTION NOT DEFINED


please find the enclosed print shot for the error

 

[Ron_dK]

myfile = OpenFile("c:\d\", "dps", "*dps.xls", 0)

Try changing this to :

myfile = OpenFile("c:\d\", "xls", "*dps.xls", 0)

 

[Alansidman]

Here is a thread that I posted some code in that may prove helpful. I use this to import multiple sheets in a workbook into separate tables.

http://www.access-programmers.co.uk/forums/showthread.php?t=165146

Alan