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?
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
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
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
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.
Thanks! I assume before it "opened" without showing the interface.