running an Acess Macro from excel

[stuart]

hi everyone

not sure if this is possible but you never know

I have in an ms access database a macro that will export a table to a
spreadsheet (not a vb macro but one of the objects in access type things - i
get confused easily so thought id say)

anyway, i have built up an excel application that will retrieve data and
show it nicely on a form list view etc (works good even if i do say so myself
lol), but what im wanting to be able to do next is press a button in excel
and have it export the table my data is on to excel

ok so here is the question - is it possible to use MS excel VBA to run a MS
Access macro object to export the data

thanks in advance

stuart

 

[Mike]

Not sure if you can do what you want. But you can pull the data from excel
with something along these lines.
Sub pullDataFromAccess()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Dim SODrng As Range
Dim rowNumber, iCol As Long
Dim sSQL As String

Const strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& " Data Source=C:\Path\to database\database.mdb;Persist Security
Info=False"

Set SODrng = Sheet1.Range("A6")

sSQL = "SELECT Table1.* FROM Table1;"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open strConn
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic

'get field names
iCol = 1
For Each fld In rs.Fields
Sheet1.Cells(5, iCol) = fld.Name
iCol = iCol + 1
Next

SODrng.CopyFromRecordset rs
Cells.EntireColumn.AutoFit

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

 

[XP]

If you want to run a program you wrote in an MS-Access module, then:

The following will run a program named "MyProgram" in an Access DB at the
path
entered as shown below. The program must reside in a module:

Dim oAccess As Object
Dim sFullName As String

sFullName = "add the full path and file name with extension here"
Set oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase sFullName
oAccess.Run "MyProgram"

HTH

 

[stuart]

thanks everyone

ill play with these and i know it will be workign for me by the end of the day

regards

Stuart