Need Help Transferring Data From Excel To Access

[LongFeiFengWu]

I have never done this and don't even know where to begin. What I need to do is write a script that copies an entire Excel Spreadsheet to an access table without over-writing the existing data. Also the Excel sheets I'll be pulling from lack consistency from cell to cell. The first several rows contain data I don't need and the length of the Excel sheet will change daily.

With all that said...Can anyone out there help me?

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[Programmer1974]

Hello Long!

I don't have time to provide a lot of code for you, but I may be able to provide a starting point. If you're not familiar with VBA in excel, you can quickly learn by recording a macro (Tools -> Macro -> Record New Macro). After recording your macro, edit the macro and review the code. After some experimentation, you should be able to learn how to access all the data you need.

The next question is getting it into access. There are a couple ways to do this.
1. Create an ODBC entry pointing to the access database and add your records via ODBC.
2. Control access as an object. Ok, here is a little bit of code...

Dim objAccess

Const WindowNorm = 1, WindowMin = 2, WindowMax = 3

MsgBox "Begin"
Set objAccess = CreateObject("Access.Application")

Call subAccessTest1()

Set objAccess = Nothing
MsgBox "End"

Sub subAccessTest1()
	objAccess.Visible = True
	objAccess.OpenCurrentDatabase "C:\test.mdb"
	objAccess.DoCmd.RunMacro "macTest"
	objAccess.Quit True
End Sub

You'll have to experiment here to find out how to control the table you want to insert in to.

Sorry I wasn't much more help, but this should get you on the right track. This forumn has plenty of information you can search for to aid you.