I need a Macro to dump certain cells in a worksheet into an access
database.

This will be done on a daily basis - I want them all on the same
database.

I'm doing the exact same thing right now actually.

i) Create a named range in excel over the range of cells you want
dumped into access. Lets call it 'EXPORT'.
ii) Open access. Create a linked table to your excel file and select
the range EXPORT.Call this linked table LINKED_EXPORT
iii) Create a Table in access with the same column headings as the ones
in the range EXPORT. This table will be blank for now. Call this table
TABLE_EXPORT
iv) Create an APPEND QUERY in Access. Append LINKED_EXPORT to
TABLE_EXPORT. Save the query as QUERY_EXPORT.
v) Create a Macro in access. Setwarnings = off, OpenQuery =
QUERY_EXPORT
vi) Put this into your vba code:

Sub EXPORT_DATA()

Dim mdb_Obj As Object
Set mdb_Obj = CreateObject("Access.Application")
Dim app As Application
Set app = Application
mdb_Obj.Visible = False

mdb_Obj.OpenCurrentDatabase ("c:\location\of\your\Database.mdb")
mdb_Obj.DoCmd.RunMacro "QUERY_EXPORT"
mdb_Obj.Quit

End Sub

Done:)

SmartyPants wrote:
I need a Macro to dump certain cells in a worksheet into an access
database.

This will be done on a daily basis - I want them all on the same
database.

Sorry - you'll need to save your macro as MACRO_EXPORT, then in the vba
code this line:

mdb_Obj.DoCmd.RunMacro "QUERY_EXPORT"

should read

mdb_Obj.DoCmd.RunMacro "MACRO_EXPORT"

Got ahead of myself :)

Fiddle with this as the basics are there. You do not necessarily have
to append new data to a table every time, but this is what I was doing
so it was easiest to explain.
-SD

S Davis wrote:
I'm doing the exact same thing right now actually.

i) Create a named range in excel over the range of cells you want
dumped into access. Lets call it 'EXPORT'.
ii) Open access. Create a linked table to your excel file and select
the range EXPORT.Call this linked table LINKED_EXPORT
iii) Create a Table in access with the same column headings as the ones
in the range EXPORT. This table will be blank for now. Call this table
TABLE_EXPORT
iv) Create an APPEND QUERY in Access. Append LINKED_EXPORT to
TABLE_EXPORT. Save the query as QUERY_EXPORT.
v) Create a Macro in access. Setwarnings = off, OpenQuery =
QUERY_EXPORT
vi) Put this into your vba code:

Sub EXPORT_DATA()

Dim mdb_Obj As Object
Set mdb_Obj = CreateObject("Access.Application")
Dim app As Application
Set app = Application
mdb_Obj.Visible = False

mdb_Obj.OpenCurrentDatabase ("c:\location\of\your\Database.mdb")
mdb_Obj.DoCmd.RunMacro "QUERY_EXPORT"
mdb_Obj.Quit

End Sub

Done:)

SmartyPants wrote:
I need a Macro to dump certain cells in a worksheet into an access
database.

This will be done on a daily basis - I want them all on the same
database.

http://www.erlandsendata.no/english/...badacexportdao

Hth,
Merjet