Solidworks API: Creating an Excel File

Out of category
1 
Set xlApplication = CreateObject("Excel.Application")
    xlApplication.Visible = True
    DoEvents
    Set xlWorkBook = xlApplication.Workbooks
    xlWorkBook.Add
    Set xlFeuilEnse = xlApplication.ActiveSheet
    DoEvents
    
    'Création et renommage des feuilles
    
    Set xlFeuilMeca = xlApplication.Sheets.Add(After:=Worksheets(Worksheets.Count))
    Set xlFeuilComm = xlApplication.Sheets.Add(After:=Worksheets(Worksheets.Count))
    xlFeuilEnse.Name = "ENSEMBLES"
    xlFeuilMeca.Name = "PIECES MECANIQUES"
    xlFeuilComm.Name = "ELEMENTS DU COMMERCE"

Hello

 

I'm making a macro to create an excel file and add information to it. I have a problem with the creation of the excel file.

Macro works 1 time out of 2 and 1 time out of 2 strictly. It's in my creation of my file that I think there is a problem but I looked on the net and I found nothing.

 

Can anyone help me?

Here is my snippet of code for the creation of the excel file:

 

 

 

2

I may have had the same problem recently, look at the processes, after using your macro if excel is still in use it means that you do not completely close the process and it goes wrong on the second launch

https://www.lynkoa.com/forum/solidworks/probl%C3%A8me-s%C3%A9lection-fichier-api

 

For example at the end of your macro: 

Sub FermetureExcel()

    Set xlFeuilMeca = Nothing
    Set xlFeuilComm = Nothing

    'Fermeture du classeur et de Excel
    oXL.DisplayAlerts = False
    oXL.Workbooks.Close
    oXL.Quit
    oXL.DisplayAlerts = True
    'Désallocation de la mémoire
    Set oSH = Nothing
    Set oWB = Nothing
    Set oXL = Nothing
End Sub

 

Kind regards

2 Likes
3

Hello

As mandrake said, and as indicated on the link to which he refers:

" it smells like an Excel resource that is not freed !! "

Kind regards

4

Hello

 

Thank you for your answer. That's right, but the problem is that at the end of my macro I don't want to close excel. I want him to stay open.

 

Kind regards

5

I tried to add at the end of my macro:

Set xlSheet = nothing

Set xlWB = nothing

Set xlApp = nothing

But nothing helps.

Can we free up the Excel resource without closing excel?

 

Thank you in advance,

6

If you restart the macro and it's still on the same file it can't create the page 'SETS', 'MECHANICAL PARTS' and 'COMMERCIAL ITEMS' twice

 

Put a condition, if the page doesn't exist it is created.

Your macro fills your excel file every time you click following the assembly that is open, you create/open a new excel file every time you store all the information on a single file?

7

I create an excel file every time I launch my macro.

8

I know it's an unreleased excel resource issue. I tried to close my excel file via excel when my macro is finished and restart my macro but I still have the same problem.

Is there a test that allows you to know if the resource is released and if not released from freeing it?

9

Try with:

Set xlWorkBook = Nothing

Set xlFeuilEnse = Nothing

Set xlFeuilMeca = Nothing 

Set xlFeuilComm = Nothing

 

You have to reset everything you define above.

10

You have to look in the processes, once you have closed the excel go to your task manager (Ctrl + SHIFT + Esc) -> Process    and check that there is no EXCEL.EXE

11

I've tried it before.

I put it at the end of my macro... But it doesn't work.

I tried to declare my excel application as a new excel application but doesn't work either:

Dim xlApplication as New Excel.Application

12

I do have an Excel process that is present. And I thought the process stopped when I manually closed excel but apparently not. 

13

Maybe it's my addition that's bad?

Set xlMeca Sheet = xlApplication.Sheets.Add(After:=Worksheets(Worksheets.Count))
Set xlSheetComm = xlApplication.Sheets.Add(After:=Worksheets(Worksheets.Count))

14

Hello

 

I've had this problem before, too.

The only solution I've found so far is to kill the Excel process at the end of the processing.

Otherwise he is as if lost between the two Excel processes present and does not know in which one to run and indifferently takes the good or the bad.

The only explanation I found with my few VB acquaintances.

Kind regards

 

A. Mendes

 

 

 

15

It depends on how your variables are declared:

Sun xlLeafMeca As ...

Dim xlSheetComm As ...

Have you gone to see the penultimate post of the link that your put mandrake, you will find some useful information ...

You can also go and see HERE, there is a lot of information on the method to use.

Kind regards

16

They are declared as follows:

Dim xlFeuilMeca                                             As Excel.Worksheet
Dim xlFeuilComm                                             As Excel.Worksheet
Dim xlFeuilEnse                                             As Excel.Worksheet

I changed the way I create my sheets like this:

    xlApplication.Sheets.Add
    xlApplication.Sheets.Add
    Set xlFeuilEnse = xlApplication.ActiveSheet
    xlFeuilEnse.Name = "ENSEMBLES"
    Set xlFeuilMeca = xlApplication.Sheets(2)
    xlFeuilMeca.Name = "PIECES MECANIQUES"
    Set xlFeuilComm = xlApplication.Sheets(3)
    xlFeuilComm.Name = "ELEMENTS DU COMMERCE"

 

I don't have a bug anymore in this place but I have one on the formatting of my spreadsheet on this line:

xlFeuilMeca.ListObjects.Add(xlSrcRange, Range(xlFeuilMeca.Cells(5, 1), xlFeuilMeca.Cells(i - 1, 7)), , xlYes).Name = "Tableau1"

 

Given that the problem is that on formatting and that my excel is correctly filled in we will say that this problem is "acceptable"...

17

I think I've found the problem: my IMPATIENCE

Apparently I was restarting the macro too quickly which didn't leave time for the process to stop.