- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Create a Excel file using VBA Inventor 2021+Excel2016 not works
Please help
After updating from Inventor 2018 to Inventor 2021,
I can't create an excel file !
Inventor 2018 + Excel 2007 32-bit , code works
Inventor 2021+ Excel 2007 32-bit , code works
After updating to Inventor 2021
Inventor 2021+ Excel 2007 32-bit , code works
Inventor 2021+ Excel 2016 32-bit , code does not works !
Thank you
Private Sub Conect_To_Excel()
''Dim oExcel As Excel.Application
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
'Create new workbook
Call oExcel.Workbooks.Add
Dim oWorkbook As Object ''Workbook
Set oWorkbook = oExcel.Workbooks.Item(1)
Dim oWorkSheet As WorkSheet
Set oWorkSheet = oWorkbook.Sheets.Item(1)
oWorkSheet.Cells(1, 1).Value = "writing works"
oWorkbook.SaveAs ("E:\test.xls") 'Save excel
oExcel.Quit
Set oWorkSheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing
End Sub- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
simply change your code like this:
set oWorkbook = oExcel.WorkBooks.add
Also, a Workbook created from VBA does not contain any sheets. you must add them first
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thanks Yan
I've already tried
Set oWorkbook = oExcel.WorkBooks.adddoes not work
Set oWorkbook = oExcel.Workbooks.Add(Template:="c:\Temp\xy_Template.xls")does not work too and show error on next command
Dim oWorkSheet As WorkSheet
Set oWorkSheet = oWorkbook.Sheets.Item(1)I don't know where I'm making a mistake, but that's how it worked for me for years and still works with excel 2007 but does not work with excel 2016+Inventor 2021
If I run Excel manually before running the macro
that's how it works ???
Sorry about my English
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thanks Yan
This same in Ilogic
inventory 2021 + Excel 2007 works
inventory 2021 + Excel 2016 doesn't work ??. Why ??
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main()
Dim oExcel As Object ''As Excel.Application
oExcel = CreateObject("Excel.Application")
oExcel.Visible = True ''zviditelnit okno s Excelom
''Create new workbook
Dim oWorkbook As Object ''Workbook
oWorkbook= oExcel.Workbooks.Add
''oWorkbook = oExcel.Workbooks.Add (Template:="C:\Temp\PK_Template.xls")
oWorkbook = oExcel.Workbooks.Item(1)
oWorkbook.activate
Dim oWorkSheet As Worksheet
oWorkSheet = oWorkbook.Sheets.Item(1)
oWorkSheet.Cells(1, 1).Value = "works !"
oWorkbook.SaveAs ("C:\Temp\test.xls") 'Save excel
oExcel.Quit
oWorkSheet = Nothing
oWorkbook = Nothing
oExcel = Nothing
End Sub
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Weird, this code works on my PC with Inventor 2020 + Excel 365 (Office 16).
could this be a 32bit vs 64bit issue ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Usually when you create a new instance of the Excel application by code, it will not already have a Workbook open. There is a checkbox setting within Excel > File menu > Options > General > (under the "Start up options" heading) > called "Show the Start screen when this application starts". This will allow you to skip the splash screen and/or the screen that would normally pop-up asking you to select either a new blank workbook or start one from a template, or open an existing one, when you manually start up Excel, but I don't believe it will behave this way by default.
Also, there is a setting within Excel > File menu > Options > General > (under the "When creating new workbooks" heading) > where you can set "Include this many sheets:". This can only be set between 1 and 255, so 0 is not an option. However, this setting may only effect when you create a Workbook through manual means, and may not have any effect when creating one by code.
What you can do before attempting to get/set the Workbook variable is check If the Workbooks.Count property = 0 (then Add one), else get either the ActiveWorkbook or the first one.
Dim oWB As Workbook
If oExcel.Workbooks.Count = 0 Then
oWB = oExcel.Workbooks.Add()
Else
oWB = oExcel.Workbooks.Item(1)
'or
'oWB = oExcel.ActiveWorkbook
End IfSame with the Worksheets and specifying your Worksheet variable. You can check the Worksheets.Count to see if it is greater than 0. If the count = 0 then, Add one, else Get either the first one or the ActiveSheet.
Dim oWS As Worksheet
If oWB.Worksheets.Count = 0 Then
oWS = oWB.Worksheets.Add
Else
oWS = oWB.ActiveSheet
'or
'oWS = oWB.Worksheets..Item(1)
End If
Also, if you have two version of Excel installed on your machine, it may be opening the wrong version. There are ways to open a specific application, by specifying its full path and file name, if that's what you need. Since I only have one version of Excel installed, I usually start it for my code with the following line:
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass
oExcel.DisplayAlerts = False
oExcel.Visible = TrueBut you can use the Process route too, which you may need to do if it's opening the wrong version.
Similarly, in Inventor, there are many actions that when done manually, there are often intuitive helpers listening to your actions in the background that will help you do what you're trying to do, but then when you try to do the same thing by code, you often have to specify more details and or settings to accomplish the same thing.
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE'
.
If you have time, please... Vote For My IDEAS
and Explore My CONTRIBUTIONS
Inventor 2020 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi WCrihfield
Your code also doesn't work, it's still a bug
Private Sub TestExcel2()
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Dim oWB As Workbook ''Workbook
If oExcel.Workbooks.Count = 0 Then
Set oWB = oExcel.Workbooks.Add()
Else
Set oWB = oExcel.Workbooks.Item(1)
'or
'oWB = oExcel.ActiveWorkbook
End If
Dim oWS As WorkSheet
If oWB.Worksheets.Count = 0 Then
oWS = oWB.Worksheets.Add
Else
oWS = oWB.ActiveSheet
'or
'oWS = oWB.Worksheets..Item(1)
End If
oWS.Cells(1, 1).Value = "Works !"
oWB.SaveAs ("c:\Temp\test.xls") 'Save excel
oExcel.Quit
oWS = Nothing
oWB = Nothing
oExcel = Nothing
End SubAfter command
Set oWB = oExcel.Workbooks.Add()
in watch window I see workbooks.count changed from 0 to 1
and show error and
oWB = Nothing !!
I note that on a PC where the Inventor2021 + excel2007 code works
does not work on PCs where it is Inventor 2021 + excel 2016
Sorry about my English
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I'm using Inventor Professional 2021.1 and Office 365 (the Account info within my Excel application says Microsoft Office Professional Plus 2019), and I'm using Windows 10 Enterprise.
Here is a sample VBA macro I used to test with, and it works for me without any errors.
Sub UseExcel()
'Create New instance of the Excel application
Dim oExcel As Excel.Application
Set oExcel = New Excel.Application
oExcel.DisplayAlerts = False
oExcel.Visible = True
'Get a Workbook
Dim oWB As Excel.Workbook
If oExcel.Workbooks.Count = 0 Then
Set oWB = oExcel.Workbooks.Add
Else
Set oWB = oExcel.ActiveWorkbook
'or
'Set oWB = oExcel.Workbooks.Item(1)
End If
'Set oWB = oExcel.Workbooks.Open("C:\Temp\Test.xlsx")
'Get a Worksheet
Dim oWS As Excel.WorkSheet
If oWB.Worksheets.Count = 0 Then
Set oWS = oWB.Worksheets.Add
Else
Set oWS = oWB.ActiveSheet
'or
'Set oWS = oWB.Worksheets.Item(1)
End If
'Set oWS = oWB.Worksheets.Item("Sheet1")
'Work with Cells by Row Index, then Column Index
Dim oCells As Excel.Range
Set oCells = oWS.cells
'Create column headers
'First number is Row index, second number is Column Index
oCells.Item(1, 1).Value = "FIRST"
oCells.Item(1, 2).Value = "LAST"
oCells.Item(1, 3).Value = "E-MAIL"
'Make All Columns AutoFit widths to fit contents
oWS.Columns.AutoFit
'oWB.Save
oWB.SaveAs ("C:\Temp\Test.xlsx")
'oWB.Close
'oExcel.Quit
End Sub
So if this isn't working for you, it would seem there is something going on other than just coding technique that's preventing this from working.
Is it possible your Excel application has some security settings preventing it from being manipulated by code.
I wander if the setting within Inventor's Tool tab > Options panel > iLogic Configuration > Excel Options setting. I have mine set to "COM", but I was under the impression this only effected the GoExcel stuff in iLogic.
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE'
.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
The thought also occurred that you may need to add a pause (wait) in there, right after you open the application, but before attempting to do anything else with the application, to allow for load time.
There have been documented issues (on "support.microsoft.com" & "social.msdn.microsoft.com", and other sources) with using the GetObject() and CreateObject() routes, because of similar load time issues. Apparently Microsoft Office applications don't immediately register themselves before starting up, as many other applications do, to help speed up the initial load time. The workaround I read about was to change the focus away from the Office application to something else, then back to the Office application, then wait a bit, then the Office application should have registered itself and can now be used further. That switch of focus seemed to trigger the register process, so that the application can be found.
Here is one of the sources I was reading from.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I've tried that before
Dim oExcel As Excel.Application
Set oExcel = New Excel.Applicationmust be
Dim oExcel As Object
I think it will be something in the Excel settings, but I've already
tried everything what I thought that could have an effect on it.
Setting iLogic to : COM has no effect too , I've also tried it before
Now I have reached your last post
and I think it will probably be the way to success.
When I will be again at work , in my office, I will try .
PCs on which the code does not work
Inventor Professional Build , Release 245 : 2021.1.2 and Microsoft Office 2016 for business and Windows 10 Pro.
Thank you for trying to help me.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi WCrihfield
Thanks for the great idea.
It already works somehow, but not always
and I don't know what to do with it.
The macro will create an excel only if immediately
after clicking on the CommandButton I click anywhere
on the desktop. If I do not do so, the macro after a few seconds
will end with an error
and file *.xls is not created.
I also attach the VBA project
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
OK. So it will work when you change the focus away then back again. That sounds just like what the external post talked about. But it seems the focus change isn't happening within the code as desired, and is relying on user interaction to change the focus. I was able to import your form, but was not able to reproduce the same results/behavior. I can see within UserForm code, that you are attempting to immediately change the focus from the Excel application that you just started using a Shell command, to the TextBox within the form. Then you're using GetObject to get the Excel application again, then immediately trying to set application options within it.
When use another Module to show this form, and launch this other module as a macro, it shows the form, then waits. When I click the CommandButton1, it instantly shows an error that says "Compile error: Sub of Function not defined", and when I click the OK button on that error dialog, the word "Sleep" is always highlighted. When I right click on that highlighted word "Sleep" and choose 'Definition', the pop-up says "Identifier under cursor is not recognized". It doesn't seem to know what this is. I know that the Sleep routine is a Windows API sub, and not a VBA sub, and so I thought you had to import or declare it within the local module before you could use it, but I could be wrong, because I don't really use it. But if that isn't causing you any troubles, it might just be something simple like a reference I don't currently have turned on, or something similar.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I'm sorry my mistake I forgot send everything.
Now i am sending the whole project userform + module. (Test.zip)
I hope it will be ok now.
Check - > Tools - > Rreferences - Microsoft Excel....
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I had the reference to the "Microsoft Excel 16.0 Object Library" turned on before, so that wasn't the issue. I downloaded your latest zip file, and loaded the project. It created a new Project called "Mtec", and contained one UserForm spec named "Frm_TestExcel' and one regular module named "ExcelTest". When I ran the Macro (which is the regular module code), it ran perfectly, as expected. It started a new instance of Excel, created a new workbook, filled in the first three column headers, and auto-fit the columns. However, that macro does not launch your custom UserForm. I believe your intentions were to launch the UserForm first, then when the user clicks the "CommandButton1", you want that to cause the code to run that creates the Excel file. To make this happen, I believe you need a regular module (macro) that just launches the form, then move most (if not all) of the code from your existing regular module (macro) over into that form spec, as the (what to do) reaction to the event of clicking on that button.
When I look at the code within your UserForm spec, it reads much like a regular module's/macro's code, instead of a UserForm specification. The UserForm's code should define the specifications of the form itself, and each of the "Controls" within it, and which "events" you want to watch for in relation to those form controls, and what actions to take in response to those events.
I'm not sure if any of this will help you with your base problem of Excel suddenly not launching correctly for you when working with a different combination of Inventor and Excel releases, but If you want to continue to try to use VBA to achieve this instead of iLogic, I will try to help you get this whole UserForm and Macro situation figured out.
I created another regular module to use as the Macro to launch your UserForm within your VBA Project. I changed the name of the module to "Show_Frm_TestExcel", and put the following code within it:
Sub Show_Frm_TestExcel()
'Show/Launch the UserForm named "Frm_TestExcel"
Frm_TestExcel.Show
End SubNow, when I run this new macro, it launches your UserForm, then awaits my interaction with it. If my Excel application is currently closed, and I click the "CommandButton1" button, and just wait without doing anything else at all for a while, a message will pop-up saying "GetObject still failing. Process ended.". I could see that it opened Excel and there was a workbook open within it (minimized in the taskbar only), and after clicking the message away, I checked the workbook and there was no contents written to it. However, when I try this process again, but this time click around on my screen after clicking the button, it then starts Excel and shows a message box saying "File not found", and there are two workbooks open within it. One is blank and the other says "Writing works" within cell A1. Is this the odd behavior you were inquiring about? Please note that this reaction from Excel was entirely defined within the UserForm specification's code, and didn't actually run the other standalone macro's code at all. This is what I was talking about earlier.
I'm going to point you to a post I created a while back that shows you how to completely design and use a very simple Windows Form and how to set up event listeners for it, and how to tell it what to do when those events happen. This is all contained within a single iLogic rule (VB.NET code). Just run the rule, it shows the form, when you interact with the form, it reacts to your interactions with messages, letting you know it works. Here's the link to the post: https://knowledge.autodesk.com/community/article/328361
VBA and VB.NET are very similar in some aspects, and both descended from and use the Visual Basic coding language. So most of the code within should be able to translate to your VBA UserForm's code. Or at least give you a much better understanding of how to set it all up. Test this out. Look it over thoroughly. And see if it helps you out.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Yes I also use it to show my forms.
Sub Show_Frm_TestExcel()
'Show/Launch the UserForm named "Frm_TestExcel"
Frm_TestExcel.Show
End SubMessage box saying "File not found" show because file c:\Temp\test.xls was not on disk
(Kill "c:\Temp\test.xls")
the error is not checked in the code because I have the file on the disk
Your Question :
"One is blank and the other says "Writing works" within cell A1.
Is this the odd behavior you were inquiring about?"
Answers : Yes
In short, the excel file will not create me.
It is interesting that every other
click on CommandButton the excel file is create ?!!!
1st show "GetObject still failing. Process ended."
2nd File created
3rd show "GetObject still failing. Process ended."
4th File created
.....
....
and I don't understand that
Would you be able to modify the code for me to make it work
Very interesting contribution also in terms of iLocic
He expresses himself very difficult with my weak English
Many thanks for trying to help me