Is it possible to create an excel instance with VBA through visio?

[GSG9]

I have been creating a macro to export information from microsoft visio to an excel spreadsheet (the macro runs through an open visio document). However when i began writing the code to create an excel instance trough this macro i got a compile error that said "user defined type not defined" when i ran this bit of code

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
objExcel.Visible = True
objExcel.SheetsInNewWorkbook = 1
objExcel.Workbooks.Add

i'm assuming this error is occuring because the excel VBA library is not available in visio and thus i'm wondering if there is any way to activate the library in visio

can anyone help?

 

[cheerio]

I am nor familiar with the detail of Visio but the principle is the same for all programs that use vba.

In the VBA editor you should be able to choose Tools, References and tick a reference to Excel. This will make the Excel object library visible to your code.


Good luck

Ken

 

[GSG9]

Great it worked,thanks alot