Excel to Access

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I have created a macro in an Excel workbook to do various things with a web
query, then export one sheet of the workbook as a txt file. I then get the
macro to open Access.

What I am wanting to do is:- I want my macro to, not only open Access, but
to open a specific Access Database and if possible, run a macro within that
database to import the data that I have exported from Excel.

Any ideas?

Thanks
 
Keith said:
I have created a macro in an Excel workbook to do various things with a web
query, then export one sheet of the workbook as a txt file. I then get the
macro to open Access.

What I am wanting to do is:- I want my macro to, not only open Access, but
to open a specific Access Database and if possible, run a macro within that
database to import the data that I have exported from Excel.

Any ideas?
Click to expand...

Sure
http://support.microsoft.com/default.aspx?scid=kb;en-us;209207

Here are all the command line options for Access. You can open a
specific database, run a macro.. do all sorts of stuff.
 
Why don't you insert the data from excel in the database.
It works for me like a charm.


First set reference to Microsoft ActiveX Data Objects Library

'--------------------------------------------------------------------------------

ConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source='C:\Data\data.mdb';"

Set Connection = New ADODB.Connection

Connection.Open ConnectionString:=ConnectStr

On Error GoTo CloseConnection

LastRow = Range("A1").End(xlDown).Row

For i = 1 To LastRow

CodeStr = Replace(Range("A" & i).Value, ";", ""))

CountStr = "UPDATE DBTable SET Code = '" & StrCode & "' WHERE
.....

Connection.Execute CountStr, RecordCount1

'RecordCount is number of records effected

Next i

CloseConnection:

Connection.Close
 
Is this VBA coding for Excel? if so, I get an error with the line "CodeStr =
........." (Highlights it in red) and if I try to run it, I get an error with
the "Set Connection ......" line also (Compile error: User Defined Type Not
Defined)
 
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.
 
Back
Top