Extraction from excel

Macro
1

Hello
From solidworks I launch a macro with a USF which should display values from an Excel workbook when opening it

This code works in Excel
retrieves cell H2 from the tab ' List '

sub Excel_test()
Url = "C:\Users\xx\Documents\SYSTEL\02-PROG\Macros\"
Fichier_Excel = "PART_Step.xlsm"
Cells(1, 10) = ExecuteExcel4Macro("'" & Url & "[" & Fichier_Excel & "]Liste'!" & Range("H2").Address(, , xlR1C1)) 'URL entrée

end sub

since Solidworks I have this error
Runtime error ‹ 1004 ›:
The ' Range ' method of the object ' _Global ' failed

Private Sub UserForm_Initialize()
Dim swApp As SldWorks.SldWorks
Set swApp = Application.SldWorks

Url = "C:\Users\xx\Documents\SYSTEL\02-PROG\Macros\"
Fichier_Excel = "PART_Step.xlsm"
label3= ExecuteExcel4Macro("'" & Url & "[" & Fichier_Excel & "]Liste'!" & Range("H2").Address(, , xlR1C1)) 'URL entrée
end sub

if anyone has an idea

2

Bonjour;
Ne manquerait-il pas les déclaration/references pour Excel ?

`Dim xlApp As Excel.Application
Set xlApp = New Excel.Application`

Voir (pour plus d’informations):

3 Likes
3

I added

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application'

it doesn't work either

4

Hello

Unless I'm mistaken, in VBA, it is not possible to access the content of an Excel file without opening it.
If it's for some kind of " database " to feed a drop-down list for example, the easiest way is to write it in a text file and read it.

1 Like
5

I just tested it's possible on Excel, I can't find the match with Solidworks

sub Excel_test()
Url = "C:\Users\xx\Documents\SYSTEL\02-PROG\Macros\"
Fichier_Excel = "PART_Step.xlsm"
Cells(1, 10) = ExecuteExcel4Macro("'" & Url & "[" & Fichier_Excel & "]Liste'!" & Range("H2").Address(, , xlR1C1)) 'URL entrée

end sub
6

et en remplaçant

Cells(1, 10) = ExecuteExcel4Macro(.....

par

Set xlapp = CreateObject("Excel.application")
Label13 = xlapp.ExecuteExcel4Macro(.....

des pistes relativement identiques ici:

1 Like
7

I just tested, so it does work on Excel but on SW not (error on the range line).
At first glance I think it's a compatibility problem between SW which is in 64bit and Office in 32bit.

8

essayez avec
GetObject(“Excel.Application”)
à la place de
CreateObject(“Excel.Application”)
… il faut aussi penser à « tuer » tous les processus excel residuels:
Shell “TASKKILL /F /IM Excel.exe”, vbHide
a voir aussi:

mais aussi:

9

It's not so much the ExecuteExcel4Macro line that crashes but the call to Range.

10

The error message is as follows, as quoted
The 'Range' method of object '_Global' failed

11

Hello

The range method is not supported via the version of vba integrated into SW, so there are two solutions left (not tested the second one at this stage):

  1. Create a loop with a function to retrieve each desired column/row, see ExecuteExcel4Macro - Microsoft Community

  2. Try ADODB-like functions. Connection, see Read and Write in Closed Excel Workbooks (developpez.com)

So I only tested the first one that works properly.

2 Likes
12

can you put the appropriate macro, I can't do it with solution 1

13

Can you just tell me on which range you want to retrieve the values? (type A1:C2 for example)

14

here is my original code that doesn't work, H2

Private Sub UserForm_Initialize()
Dim swApp As SldWorks.SldWorks
Set swApp = Application.SldWorks

Url = "C:\Users\xx\Documents\SYSTEL\02-PROG\Macros\"
Fichier_Excel = "PART_Step.xlsm"
label3= ExecuteExcel4Macro("'" & Url & "[" & Fichier_Excel & "]Liste'!" & Range("H2").Address(, , xlR1C1)) 'URL entrée
end sub
15

To fully understand, do you just want the H2 cell?

16

Yes

17

In this case, much simpler:

label3= ExecuteExcel4Macro("'" & Url & "[" & Fichier_Excel & "]Liste'!" & "R2C8") 'URL entrée
1 Like
18

Great, it works

Here's another example for those who are interested

Label2 = ExecuteExcel4Macro("'" & url & "[" & Fichier_Excel & "]Liste'!" & "R11C8") & "\" & _
         ExecuteExcel4Macro("'" & url & "[" & Fichier_Excel & "]Liste'!" & "R11C7") & _
         "-STEP\"  'URL sortie
2 Likes
19

It's still strange that the "ExecuteExcel4Macro" function accepts table coordinates such as " Row "+" Column " but not the "Range... The joys of computing and its mysteries... :crazy_face:

1 Like
20

As I said, I think that the SW API is on 64 bit functions where in Excel it's 32.
If Excel was in 64 I think there would be no problem.
I have the case of some macros that can only be launched from SW and are in error if I launch them from Excel

2 Likes