Using Document Manager API from Excel

[gerard]

So,

I've got a Document Manager Key, and have created a vba macro in SolidWorks which verifies all is fine and good in Document Manager land.

So, I want to do some custom property stuff from within Excel, so I look at this wonderful source https://www.codestack.net/solidworks-do ... functions/

I've got basically the exact same code to connect to Document Manager that works under SolidWorks. All the same references are checked, yadda, yadda, yadda.

But when it gets to Set swDMClassFactory = CreateObject("SwDocumentMGR.SwDMClassFactory")

VBA burps and says"

Run Time error '-2147221164 (80040154)':
Class not registered.

Yes I have the SwDocumentMgr 2022 Type Library selected in references.

Any idea why this is spitting at me?

Could this be a 32 bit vs 64 bit thing? Unfortunately we are running the 32 bit version of Excel 2016.

Thanks,

[AlexB]

Could this be a 32 bit vs 64 bit thing? Unfortunately we are running the 32 bit version of Excel 2016.

This is almost certainly the issue. It's a similar error I get when I forget to un-check "Prefer 32-bit" when I use Document Manager in Visual Studio.

[gerard]

This is almost certainly the issue. It's a similar error I get when I forget to un-check "Prefer 32-bit" when I use Document Manager in Visual Studio.

Thanks.

Yea, a bit of digging indicates that the dll is 64 bit. There do seem to be some ways around it, but I'll have to get my IT folks involved.

cheers

[gerard]

So,

I flipped it around and wrote the thing in the Swx IDE. Writing to an excel file is easy.

It's trudging away as we speak....

[gerard]

Ok.

Thanks so far.

The macro works fine.......

But it's slow, and as it trudges through folders gets slower.

Currently I use GetPreviewBitmap to create an stdole.stdpicture object

Then use stdole.savePicture to save a physical image on the drive.

Then use sheet.shape.addpicture to inset the image into a cell.

Is there anyway to insert the stdole.stdpicture object directly into a cell?

I've looked around and haven't found anything helpful, but I suspect I really don't know what to look for.

Thanks in advance.

[gerard]

More "goodness".

The tool works fine, but....

I'm running it multiple times to select folders to write these "catalog" files in.

Every other time I run the thing Excel opens with a new file and then burps with "run-time error 1004 Method "ThisWorkbook' of object '_Global' failed" when I try to write anything to the sheet.

Simply killing the macro, and closing excel and then restarting the macro, it runs just fine.

Any ideas why?

Thanks,

[JSculley]

Post your code that is interacting with Excel. Without it, we'll be guessing.

[gerard]

Ok,

I use a subroutine to do all the setup before writing.

sub template_setup(xlApp as Excel.Application, xlBook as Excel.Workbook, xlSheet as Excel.Worksheet)

set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet

xlSheet.Columns("A:A").Select
Selection.ColumnWidth = 25 ====> This is where it burps, every other time the routine runs.
.
.
.
end sub

In my main routine:

sub main()

'at the end of execution

xlApp.DisplayAlerts = False
xlBook.SaveAs (strxlfilename)
xlBook.Close
xlApp.DisplayAlerts = True

' and then finally:

set xlBook = Nothing
set xlSheet = Nothing
set xlapp = Nothing

end sub

The odd part of all this is there is a dialog box for the user to select file types to catalog (it uses radio buttons).

The first time the routine runs, none of the radio buttons are select. The second time, the radio button selected last time is selected. If this is the case then the Excel part of this will burp. I've tried forcibly resetting the form, but that doesn't need to help.

How does one go about COMPLETELY resetting everything after a VBA macro is done? I've set all the various objects to Nothing, but it doesn't seem to help.

Thanks

G

[JSculley]

Ok,

I use a subroutine to do all the setup before writing.

sub template_setup(xlApp as Excel.Application, xlBook as Excel.Workbook, xlSheet as Excel.Worksheet)

It's a little strange to pass these three objects to the subroutine and then initialize them. Why not simply Dim the three variables in your module and then just initialize them in the setup routine?

Code: Select all

Dim xlApp as Excel.Application
Dim xlBook as Workbook
Dim xlSheet as Worksheet

Sub main()
   ...
   ...
   ...
   template_setup
   ...
   ...
   ...
End sub

Sub template_setup()
  Set xlApp = CreateObject("Excel.Application")
  ...
  ...
End Sub

...
...
...

xlApp.DisplayAlerts = False
xlBook.SaveAs (strxlfilename)
xlBook.Close
xlApp.DisplayAlerts = True

' and then finally:

set xlBook = Nothing
set xlSheet = Nothing
set xlapp = Nothing

end sub

You did everything except tell Excel to actually stop. Add a call to

Code: Select all

xlApp.Quit

[gerard]

It's a little strange to pass these three objects to the subroutine and then initialize them. Why not simply Dim the three variables in your module and then just initialize them in the setup routine?
...

You did everything except tell Excel to actually stop. Add a call to

Code: Select all

xlApp.Quit

That makes sense.

I had the initialization in the main sub, but moved it here to attempt to isolate the issue.

I'll add the stop code.

thank you

[gerard]

Sigh. No change.

I refactored the set up routine, so instead of:

xlSheet.Columns("A:A").Select
Selection.ColumnWidth = 25

Its:

xlSheet.Columns("A:A").ColumnWidth = 25 ===> Which is where it purps.

Some innerweb spelunking hints that it is a duplicate worksheet hiding somewhere.

When it burps, there is an Excel Background Process running.

If I directly close Excel (with the "X"), it closes the background process and then the macro will run successfully.

If I end the background process from Task Manager, Excel burps with an automation error.

How do I end the background process from the macro.

cheers,

G

[JSculley]

Can you upload your entire macro?

[gerard]

Unfortunately, no. Protected enclave.

More digging indicates that a linkage to an add-in com object is happening, which remains active after killing Excel in VBA.

Microsoft's solution is not just to de-activate the COM add-in, but completely remove it.

https://learn.microsoft.com/en-us/offic ... es-running

Of course this requires admin privilege's that I don't have as the add-in's are done on a group level. Again, protected enclave...

Sigh.

Thanks for the help. I'm just going to have to call this an unsolvable problem.

[JSculley]

Rather than closing Excel each time, perhaps you can use GetObject first (to attach to an existing Excel instance if it exists) and if it fails, then call CreateObject. See this link for details.

[gerard]

Yay!

The answer: https://support.microsoft.com/en-us/top ... 815d47702d

All my references were changed to specifically call out the sheet object:

e.g.

column("A2")

became

xlsheet.column("A2")



This is a function of the fact that it's all being done from the SolidWorks VBA tool vice the excel VBA tool. In Excel it wouldn't have mattered.

Thanks,

G