Reading a closed excel from SolidWorks in VBA (SW 2016)

3D Design Volume Model
1

Hello

I would like to populate the list of a ComboBox with an Excel without opening the Excel in question (my ComboBox is in a SolidWorks macro).

So I have to be able to read cells from SolidWorks in a Closed Excel.

 

In Excel, to read a cell in another closed Excel, the following VBA code is used:

'Lecture de la cellule A1 dans la Feuil1 du classeur fermé

When I try to use this code in SolidWorks, I get a compilation error that says the ExecuteExcel4Macri function is not defined.

 

Anyone have a solution?       Thank you in advance for your help.

2

See this link 

https://www.developpez.net/forums/d1388072/logiciels/microsoft-office/excel/macros-vba-excel/macro-copier-plage-d-fichier-excel-ferme/

@+

3 Likes
3

Hello

I think, as explained in the link given by gt22, that it will be easier for you to work on an open Excel file. You can open this Excel file, manipulate it, save it and then close it programmatically in your macro without displaying the Excel window, so in a transparent way for the user.

Kind regards

2 Likes
4

Above all, you need to save the target Excel file

It was very simple... I get the full path with ActiveWorkbook.Path and it works flawlessly now... The only condition is that my source files are in the same folder or a subfolder of the workbook... no big deal... Thanks again! I foolishly lost a few days

under the same folder or subfolder  to make the links work well

from what I understood ;-(

@+

1 Like
5

If you really want to do it on a closed Excel, see: http://silkyroad.developpez.com/VBA/ClasseursFermes/ but there seem to be a lot of restrictions on the possibilities ...

2 Likes
6

Wouldn't you miss the Microsoft.Interop.Office and Microsoft.Interop.Excel references by any chance?

If these references are not enabled, you cannot use the ExecuteExcel4Macro() function since it does not exist in any referenced assembly.

3 Likes
7

Thank you Yves.T,

I was missing the reference "Microsoft Excel XX.X Object library".

How do we know which references are needed for which code??

 

Otherwise, to power my Combobox I used the following code: 

Private Sub UserForm_Initialize()
Dim i As Byte
Dim e As String

i = 1
Do
    ComboBox1.AddItem ExecuteExcel4Macro("'C:\Documents and Settings\mimi\dossier\excel\[ClasseurBase.xlsx]Feuil1'!R" & i & "C1")
    i = i + 1
    e = ExecuteExcel4Macro("'C:\Documents and Settings\mimi\dossier\excel\[ClasseurBase.xlsx]Feuil1'!R" & i & "C1")

Loop While Len(e) > 1

End Sub

Do you think we can make a simpler code?

Why not use a "ComboBox1.RowSource" that I couldn't combine with the ExecuteExcel4Macro()?

8

It's not complicated:

If you are using a procedure or function that you have created, there is no reference to add.

If you are using a procedure or function that you did not create, you must add a reference.

Pressing F1 when the cursor is on an instruction opens the help on that function/procedure. Its assembly is indicated in the help.

More generally, if you must use Excel, you need the reference to Office (which provides the basic objects for Office applications) and a reference to Excel (to have access to the specific objects, procedures and functions of Excel)

If you are using Word, you need the reference to Office and Word.

To simplify your macro, except if you can pass a range as a parameter to the ExecuteExcel4Macro() function , it should return an array of Strings. All that's left to do is bind the array to the combobox's RowSource property. (To check that the value returned by the macro is indeed an array of Strings, if not, convert it)

It is also possible to do as in the link proposed by d.roger.

Using ADO for this is a bit like using a nuclear weapon to kill a mosquito but it's largely festable. (as long as we understand the principle of ODA)

To put it simply, ADO is a set of tools that allow you to access data, whether it is a SQL or Access database, an Excel file, etc.

It uses a database engine, SQL queries, and a pointer to fetch data. We open a connection by specifying the database engine to use and the file, we ask a question to the database engine (the SQL query) and we browse the result of the query with the pointer.

2 Likes
9

OK thank you for this information Yves.T,

For the ADO method, this is what I wanted to avoid using: inadequate method (too powerful for the simple thing I want to do).

So I'm going to stay on the ExecuteExcel4Macro() function .