Opening an Excel File from Visio

Started by Dean, February 11, 2016, 03:42:03 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Dean

This code opens an excel file and it works when executed from Excel

Sub main()
Dim objWorkbook As Workbook
Set objWorkbook = Workbooks.Open( _
"C:\Task_Descriptions.xlsx")
End Sub

The EXACT same code doesnt work when doing it from Visio. I get an error "user defined type not defined"

What is causing this?

SubPlanner

#1
Try this code:Function OpenSaveExcel()
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\FileNameHere\ExcelNameHere.xlsx") 'Change the excel File location and name when needed
objXLApp.Application.Visible = False
With objXLApp.ActiveWorkbook
.RefreshAll
.Save
.Close
End With
objXLApp.Quit

End Function


I use this code from Visio to Open/Refresh/Close an external excel file.
You can comment out the stuff you don't need.

You may need to check your references to be sure you have the right ones.
I enclosed a snapshot of what I have in my VBA.


SubPlanner



Dean

Sub  Planner,

Just before you posted a reply, I activated as many references to ensure object libraries are picked up. After doing so, when I activated the code, there was no error however there was no excel file opening either.

I tried your code with refernces enables and it didnt work either. Im going to try enable the exact same references you have and in that specific order to see if it works

Dean

Nope no luck, I dont have the reference "MS Forms 2.0" like in your list.

My excel file is just not opening yet theres no syntax error

SubPlanner

#4
Oops, I left out a little step.
I guess because of the way I use this, you need to set the visibility to true.

See this code.
Function OpenSaveExcel()
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\FileName\ExcelName.xlsx") 'Change the excel File location and name when needed
objXLApp.Application.Visible = True 'Set this to true to see and false to hide
'With objXLApp.ActiveWorkbook
'.RefreshAll
'.Save
' .Close
'End With
'objXLApp.Quit

End Function

SubPlanner.

Dean

Thanks! I assume before it "opened" without showing the interface.