Help needed in copying data from excel to MS Access

[blyssz]

I have an excel spreadsheet named Employee with fields Name, DOJ, Salary and State. I want to dump the data into a MS access Temp-EmployeeData table from execl spreadsheet everyday so that I can create a report anytime needed for any date.

I created Temp-EmployeeData table with EmpName as Text type, DateOfJoining as Date type, Salary as Currency and State as Text type.

I am writing a code but it is coping the salary and Date field into the Temp-EmployeeData table from Employee spreadsheet but for Name and State, the error is Item could not be found.

MY Code is :

Dim MyWk_XLS As Workspace, MyDB_XLS As Database, MyTable_XLS As Recordset
Set MyDB_XLS = DBEngine.Workspaces(0).Databases(0)
Set MyTable_XLS = MyDB_XLS.OpenRecordset("Employee")

Dim MyWk_Temp As Workspace, MyDB_Temp As Database, MyTable_Temp As Recordset
Set MyDB_Temp = DBEngine.Workspaces(0).Databases(0)
Set MyTable_Temp = MyDB_Temp.OpenRecordset("Temp-EmployeeData")

'here's logic to fill temp table
MyTable_XLS.MoveFirst
Do Until MyTable_XLS.EOF


MyTable_Temp.AddNew
MyTable_Temp![EmpName] = MyTable_XLS![Name]
MyTable_Temp![DateOfJoining] = MyTable_XLS![DOJ]
MyTable_Temp![Salary] = MyTable_XLS![Salary]
MyTable_Temp![State] = MyTable_XLS![State]
MyTable_Temp.Update


MyTable_XLS.MoveNext
Loop


Thanks,
Blyssz

 

[kjv1611]

1. Wrong forum, try forum705 for future Access VBA questions.

2. We'll give this a shot here for now.

With these lines:

Set MyDB_XLS = DBEngine.Workspaces(0).Databases(0)
Set MyTable_XLS = MyDB_XLS.OpenRecordset("Employee")

Are you trying to refer to an Excel workbook and worksheet?


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And have you tried using this instead of looping through all the records?

DoCmd.TransferSpreadsheet [GREEN]'Details....[/GREEN]

--

"If to err is human, then I must be some kind of human!" -Me

 

[blyssz]

I am trying to refer to a worksheet

 

[kjv1611]

Have you tried the DoCmd.TransferSpreadsheet method to date? Just to see, set up a separate Sub procedure, and try that method instead. All the info you'll need is in the help file.

After trying that, post back with further questions. Unless you've already tried that method, you won't know whether it will work, and it's much faster and much simpler than trying to open the workbook and worksheet in code, and loop through rows and columns. Your computer will thank you!

--

"If to err is human, then I must be some kind of human!" -Me

 

[blyssz]

Thanks a lot..It worked

 

[kjv1611]

Any time!

--

"If to err is human, then I must be some kind of human!" -Me