PDA

View Full Version : Data from Excel to Access in a macro?



baxter300
11-20-2013, 07:44 AM
I have a table in Access which is linked to a list in sharepoint.

I also have an Excel file which uses a macro to gather the data I need to enter into the Access table.

Is there a bit of code I can add on the end of my existing macro which will copy it into Access for me?

mrojas
11-20-2013, 09:13 AM
I'd recommend using the connection string approach; there may be other approaches, but this chunk of code (you may need to modify) works for me.

dim gstrProvide as string
dim gstrFileName as string
dim conDatabase as ADODB.Connection
dim rstAccessTable as ADODB.Recordset
gstrProvider = "Provider=Microsoft.Jet.OLEDB.4.0; "
' The database file name is a static name
gstrFileName = "YourAccessDatabase.mdb"


Set conDatabase = New ADODB.Connection ' Declare connection name to use
conDatabase.ConnectionString = gstrProvider & _
"Data Source = '" & theFile & "'"
'Create Record Set
Set rstAccessTable = ADODB.Recordset
rstAccessTable .Open conDatabase, , , adCmdText 'Open the connection

' Manipulate table's data as needed