Hi all
I'm not an expert far from it in VBA but I like the tool
I wrote a piece of code with different pieces of macro to have an auto-numbering of my parts
I memorize the counter in a cell of an Excel file that I copy, increment and paste into the clipboard
technically it works
but the execution time is rather long, because I open the Excel file, write, save and close it again
I was wondering if there was not the possibility of storing the value of my meter elsewhere so that it would be faster
while searching on the net, I saw that you could store information in an INI file but the code doesn't work with Solidworks
Do you have any ideas for storing the value of my counter (4 characters 1234)
I was wondering if there was not a way to store this counter in the macro .swp file directly
What do you think
Thank you
JC.
For your information, the Smartproperties counter from Visiativ uses xml.
Here is an example of how to store the options of a userform (checkbox)
To do this in reference check Microsoft XML, v3.0 (next version office)
Code du main:
sPathXML = Environ("USERPROFILE") & "\.SaveMacroSldworks\"
sPathNameXML = sPathXML & nomfichierXML
Debug.Print sPathXML & nomfichierXML
'On vérifie si le dossier de sauvegarde existe sinon création de ce dossier
If Dir(sPathXML, vbDirectory + vbHidden) = "" Then
Debug.Print "Création du dossier: " & sPathXML
MkDir sPathXML
End If
FichierXml = sPathXML & nomfichierXML
Debug.Print "Fichier xml:" & FichierXml
To create it from the macro the function:
Sub CreationCompleteXML() 'Création du fichier xml de sauvegardes des paramètres du menu options
Dim oXML As Object
Dim oNode As Object
Dim root As Object
Dim elem As Object
Dim rel As Object
Set oXML = New MSXML2.DOMDocument
Set oNode = oXML.createProcessingInstruction("xml", "version=""1.0"" encoding=""ISO-8859-1""")
oXML.appendChild oNode
With oXML.appendChild(oXML.createElement("OPTIONS"))
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxLectureSeule"))
.Text = IIf(UserformOptionsMacro.CheckBoxLectureSeule.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxCacherPièce"))
.Text = IIf(UserformOptionsMacro.CheckBoxCacherPièce.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxCacherPlan"))
.Text = IIf(UserformOptionsMacro.CheckBoxCacherPlan.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxDesignation"))
.Text = IIf(UserformOptionsMacro.CheckBoxDesignation.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxCouleur"))
.Text = IIf(UserformOptionsMacro.CheckBoxCouleur.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
.appendChild oXML.createTextNode(vbTab)
With .appendChild(oXML.createElement("CheckBoxRevision"))
.Text = IIf(UserformOptionsMacro.CheckBoxRevision.Value = 0, "False", "True")
End With
.appendChild oXML.createTextNode(vbCrLf)
End With
oXML.Save FichierXml
End Sub
My userform to retrieve the options stored in the xml file
'Initialisation du formulaire
Private Sub UserForm_Initialize()
'position du userform par rapport à la fenêtre (1= centré/fenêtre 2= centré écran, 3=angle en haut à gauche)
'Me.StartUpPosition = 0
Me.StartUpPosition = 2 - CenterScreen
'UserformOptionsMacro.Left = Activewindow.Left + Activewindow.Width / 2 - UserformOptionsMacro.Width / 2
'initialisation du userform
CommandButtonExec.SetFocus
'On parcours le fichier xml pour récupérer les valeures sauvegardées.
Debug.Print "Fichier xml:" & FichierXml
If Dir(FichierXml) = "" Then
Debug.Print "Pas de fichier xml trouvé -> les valeures par défaut seront sélectionnées"
CheckBoxLectureSeule = True
CheckBoxCouleur = True
CheckBoxDesignation = True
CheckBoxCacherPlan = True
CheckBoxCacherPièce = True
CheckBoxRevision = False
Else
Debug.Print "Fichier xml trouvé -> les valeures sauvegardées seront sélectionnées"
Dim oXML As MSXML2.DOMDocument
Dim oNode As MSXML2.IXMLDOMNode
Set oXML = New MSXML2.DOMDocument
oXML.async = False
oXML.Load FichierXml
For Each oNode In oXML.documentElement.childNodes
For Each osubnode In oNode.childNodes
Debug.Print " - " & oNode.baseName, "=" & osubnode.Text
If oNode.baseName = "CheckBoxLectureSeule" Then CheckBoxLectureSeule = osubnode.Text
If oNode.baseName = "CheckBoxCouleur" Then CheckBoxCouleur = osubnode.Text
If oNode.baseName = "CheckBoxDesignation" Then CheckBoxDesignation = osubnode.Text
If oNode.baseName = "CheckBoxCacherPlan" Then CheckBoxCacherPlan = osubnode.Text
If oNode.baseName = "CheckBoxCacherPièce" Then CheckBoxCacherPièce = osubnode.Text
If oNode.baseName = "CheckBoxRevision" Then CheckBoxRevision = osubnode.Text
Next
Next
End If
End Sub
Code to adapt to your needs,
In terms of speed, you don't notice the writing/reading.
Great thank you, I'll look into it, I'll keep you informed
JC
Hello, and welcome.
Your goal is to be able to find the state of your meter after turning off the computer for example and resume 3 days later? Or simply for a session? Or something else?
Depending on the need, it seems to me that a declared Collection object must be de-declared after use otherwise it remains in memory, so I think that maybe the collection also retains its values. I never did the test.
Or share a meter with several people? (XML file over network)
Good evening, yes, I want to share the meter and yes, it must remain in memory after turning off the computer
For my culture, what is a collector's item?
Thank you
For the collection object:
But once SW is turned off for me the collection disappears and the collection object would be different on each post.
The most appropriate for your need seems to me the Xml like Visiativ for their coin counter that we used at 15-20 stations without ever having had any problems.
Now we have switched to the SQL version to be able to get a number even when teleworking.
FYI, the site from which I extracted the code for the Xml file:
I had then adapted the code planned for office thanks to this forum and a vba specialist.