Create a Excel file using VBA Inventor 2021+Excel2016 not works

[mrako]

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

[yan.gauthier]

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

[mrako]

Thanks Yan

I've already tried

Set oWorkbook = oExcel.WorkBooks.add

does 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

[mrako]

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

[yan.gauthier]

Weird,  this code works on my PC with Inventor 2020 + Excel 365 (Office 16).

could this be a 32bit vs 64bit issue ?

[WCrihfield]

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 If

Same 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 = True

But 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

[mrako]

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 Sub

After  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

[WCrihfield]

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' 👍.

[WCrihfield]

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.

[mrako]

I've tried that before

Dim oExcel As Excel.Application
Set oExcel = New Excel.Application

must 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. 

[mrako]

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

[WCrihfield]

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.

[mrako]

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

[WCrihfield]

   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 Sub

    Now, 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.

[mrako]

Yes I also use it to show my forms.

Sub Show_Frm_TestExcel()
    'Show/Launch the UserForm named "Frm_TestExcel"
    Frm_TestExcel.Show
End Sub

Message 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