run a macro in excel from an access vba

  • Thread starter Thread starter dracula
  • Start date Start date
D

dracula

Private Sub Knop20_Click()

Dim tabel As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

tabel = "s:\seminaries\Seminaries.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & tabel
";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("gekozen", , adCmdTable)

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim ranger As Range
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)


xlSheet.Range("A1").Value = "DATUM"
xlSheet.Range("D1").Value = Forms![Hoeveelhei
inschrijvingen]![Cursdt]
xlSheet.Range("A2").Value = "TITEL"
xlSheet.Range("D2").Value = Forms![Hoeveelhei
inschrijvingen]![Cdcurs]
xlSheet.Range("A3").Value = "SPREKER"
xlSheet.Range("D3").Value = Forms![Hoeveelhei
inschrijvingen]![Sprekr]
xlSheet.Range("A4").Value = "PLAATS"
xlSheet.Range("D4").Value = Forms![Hoeveelhei
inschrijvingen]![Plaats]
xlSheet.Range("A7").Value = "Aantal"
xlSheet.Range("B7").Value = "N°"
xlSheet.Range("C7").Value = "Aanwezig"
xlSheet.Range("D7").Value = "Naam"
xlSheet.Range("E7").Value = "Adres"
xlSheet.Range("F7").Value = "PC"
xlSheet.Range("G7").Value = "Plaats"
xlSheet.Range("H7").Value = "Groep"
xlSheet.Range("I7").Value = "Taal"
xlSheet.Range("A8").copyfromrecordset rs
xlApp.Run ("Persnlk.xls!Macro1")


xlBook.SaveAs "c:\inschrijving.xls"
xlApp.Quit

rs.Close
conn.Close

This is my code, the lin "xlApp.Run ("Persnlk.xls!Macro1") " does no
work, i'm trying to call a macro that has been saved to use with al
excel workbooks. When i run te code i got a message that the macro ha
not been found.

Can you help me
 
Perhaps dim another variable as a workbook and open the
book containing your macro...I suspect that books with
code need to be open before thay can be "run"

Patrick Molloy
Microsoft Excel MVP
 
When you open excel through automation, personal.xls is not automatically
loaded as it would be if you opened excel manually. So, as Patrick
surmised, you need to explicitly open Personal.xls.
 
Back
Top