Hi all I would like to come and write an Excel file (existing) from SW (with a vba macro) to write the name of my part, the name of the client... But I run into a problem when I use this method:
Dim xlApp As Excel.Application
Set xlApp = Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet
With xlApp
.Visible = True
.Workbooks.Open ("mondoc.xlsm")
End With
The problem is that this code creates a new instance of Excel for me And this causes me a problem, especially if the mondoc file is already open in a first instance, the file is read-only in case the file is not opened, it works, I can write, but my mondoc file itself contains macros that can no longer work with the other open files (because they are not all in the same instance of Excel).
My question is it possible to open via SW the mondoc file in the same instance as the one already in progress on my workstation If so, how to do it? I've tested several things, but my VBA level is not very high Thank you for the time.
Hello; To check if an Excel file is already open: If so, we use this file. Otherwise we open this file:
Sub CheckAndOpenExcelFile()
Dim xlApp As Object
Dim xlWorkbook As Object
Dim filePath As String
Dim isFileOpen As Boolean
' Chemin du fichier Excel
filePath = "C:\Chemin\Vers\Votre\Fichier.xlsx"
' Vérifiez si Excel est déjà ouvert
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
' Si Excel n'est pas ouvert, ouvrez une nouvelle instance
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
' Vérifiez si le fichier est déjà ouvert
isFileOpen = False
For Each xlWorkbook In xlApp.Workbooks
If xlWorkbook.FullName = filePath Then
isFileOpen = True
Set xlWorkbook = xlApp.Workbooks.Open(filePath)
Exit For
End If
Next xlWorkbook
' Si le fichier n'est pas ouvert, ouvrez-le
If Not isFileOpen Then
Set xlWorkbook = xlApp.Workbooks.Open(filePath)
End If
' Affichez Excel (facultatif)
xlApp.Visible = True
' Faites quelque chose avec le fichier Excel
' ...
' Nettoyez
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub
By replacing the path indicated in " filepath " with the location of your Excel file.
Note: remember to use the </> tags when posting a macro
… otherwise it may be automatically translated into French text. which does not make it easy to read
Hello My code is almost finished, but I still have a small problem that I can't seem to solve when the Excel file is opened, it's ok (it shows up and I can select my row) on the other hand, if the Excel file is closed, the file opens well, but it remains in the background, I am forced to select it via the toolbar Is there the possibility to force the display in the foreground of the Excel file??? If so, how to do it? Thank you I almost forgot, the Excel file is online (office365) and it also contains macros when opened.
Sub EXPDDC()
Set swApp = GetObject(, "SldWorks.Application") 'CreateObject("SldWorks.Application")
Set swmodel = swApp.ActiveDoc
If swmodel Is Nothing Then
Exit Sub
End If
If swmodel.GetType = 3 Then
Set swDraw = swmodel
Set swView = swDraw.GetFirstView
Set swView = swView.GetNextView
Set swParentModel = swView.ReferencedDocument
prop1 = swParentModel.GetTitle & "-" & swParentModel.CustomInfo2("", "REVISION")
If swParentModel.CustomInfo2("", "type") = "FAB" Then
prop2 = swParentModel.CustomInfo2("", "client")
ElseIf prop4 = "QUINCAI" Then
prop2 = ""
Else: MsgBox "MANQUE CODE FAMILLE"
End If
If swmodel.CustomInfo2("", "ETAT") = "VALIDE" And swParentModel.CustomInfo2("", "ETAT") = "VALIDE" Then prop3 = "etat ok"
Else
prop1 = swmodel.GetTitle & "-" & swmodel.CustomInfo2("", "REVISION")
If swmodel.CustomInfo2("", "type") = "FAB" Then
prop2 = swmodel.CustomInfo2("", "client")
ElseIf prop4 = "QUINCAI" Then
prop2 = ""
Else: MsgBox "MANQUE CODE FAMILLE"
End If
If swmodel.CustomInfo2("", "ETAT") = "VALIDE" Then prop3 = "etat ok"
End If
If prop3 <> "etat ok" Then
réponse = MsgBox("LES FICHIERS NE SONT PAS A L'ETAT " & Chr(34) & " VALIDÉ " & Chr(34) & vbCrLf & "SOUHAITEZ VOUS TOUS DE MÊME EXPORTER VERS" & vbCrLf & Chr(34) & " DEMANDE DE CHIFFRAGE " & Chr(34) & " ?", vbExclamation + vbYesNo + vbDefaultButton2, "ETAT FICHIER")
End If
If réponse = vbYes Or prop3 = "etat ok" Then
'traitement
Dim xlApp As Object
Dim xlWorkbook As Object
Dim filePath As String
Dim Selec As Range
' Chemin du fichier Excel
filePath = "chemin/monfichier.xlsm"
'Vérifiez si Excel est déjà ouvert
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
' Si Excel n'est pas ouvert, ouvrez une nouvelle instance
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlWorkbook = xlApp.Workbooks.Open(filePath)
' Affichez Excel (facultatif)
xlApp.Visible = True
xlWorkbook.Sheets("feuil1").Activate
' Faites quelque chose avec le fichier Excel
On Error Resume Next 'au cas où on clique sur Annuler
Set Selec = xlApp.InputBox("CLIQUER SUR LA LIGNE DE DESTINATION", Type:=8)
On Error GoTo 0
If Not Selec Is Nothing Then
Ligne = Selec.Row
xlWorkbook.Sheets("feuil1").Cells(Ligne, 2) = prop1
xlWorkbook.Sheets("feuil1").Cells(Ligne, 4) = prop2
xlWorkbook.Save
Else
swApp.Visible = True
swApp.SendMsgToUser "EXPORT ANNULÉ!"
End If
' Nettoyez
Set xlWorkbook = Nothing
Set xlApp = Nothing
Else
MsgBox "VEUILLEZ MODIFIER L'ETAT DES FICHIERS" & vbCrLf & "( AUCUNE ACTION EFECTUEE !!! )"
End If
End Sub
saisissez ou collez du code ici
… I'm perplexed, the whole Macro should not work with Excel in online version (you would have to open a web browser, log in, validate the ID), moreover Solidworks is not known to be super-compatible with office 365... and I don't think it's possible to manage it in VBA...
In theory, to display the Excel window in the foreground (for an offline excel), the command:
For the readability of your code, remember to put your variable declarations The " Dim..." " at the top... just under " Sub EXPDDC() " … move the lines:
' Nettoyez
Set xlWorkbook = Nothing
Set xlApp = Nothing
try the step-by-step mode (F8 key) and tell me if excel is visible in the foreground, when you get to the command "xlWorkbook.Sheets("feuil1").Activate" (line 68) .
So, I put a stop on the previous line and in step by step mode when I cross the line
xlWorkbook.Sheets("feuil1").Activate
nothing happens
(I can't do all the code in step mode, because when opening the Excel file which itself contains macros, I am forced to put the file in the foreground to execute them in step mode)
However, the line works I tested by changing tabs So it doesn't show up, but when I put it in the foreground manually, I of course follow the chosen tab
