run a macro in excel from an access vba
 

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

--
Message posted from http://www.ExcelForum.com

run a macro in excel from an access vba
 

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



-----Original Message-----
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![Hoeveelheid
inschrijvingen]![Cursdt]
xlSheet.Range("A2").Value = "TITEL"
xlSheet.Range("D2").Value = Forms![Hoeveelheid
inschrijvingen]![Cdcurs]
xlSheet.Range("A3").Value = "SPREKER"
xlSheet.Range("D3").Value = Forms![Hoeveelheid
inschrijvingen]![Sprekr]
xlSheet.Range("A4").Value = "PLAATS"
xlSheet.Range("D4").Value = Forms![Hoeveelheid
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 not
work, i'm trying to call a macro that has been saved to
use with all
excel workbooks. When i run te code i got a message that
the macro has
not been found.

Can you help me?


---
Message posted from http://www.ExcelForum.com/

.

run a macro in excel from an access vba
 

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.
--
Regards,
Tom Ogilvy

"dracula " wrote in message
...
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![Hoeveelheid
inschrijvingen]![Cursdt]
xlSheet.Range("A2").Value = "TITEL"
xlSheet.Range("D2").Value = Forms![Hoeveelheid
inschrijvingen]![Cdcurs]
xlSheet.Range("A3").Value = "SPREKER"
xlSheet.Range("D3").Value = Forms![Hoeveelheid
inschrijvingen]![Sprekr]
xlSheet.Range("A4").Value = "PLAATS"
xlSheet.Range("D4").Value = Forms![Hoeveelheid
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 not
work, i'm trying to call a macro that has been saved to use with all
excel workbooks. When i run te code i got a message that the macro has
not been found.

Can you help me?


---
Message posted from http://www.ExcelForum.com/