I created a macro from solidworks that launches an excel file to extract data, my problem is that from time to time my program bugs.
Option Explicit
Dim oXL As Excel.Application
Dim oWB As Workbook
Const DEBUGFLY = 1
Sub initialiseExcel(ByVal strReference As String)
'Start excel and get application
Set oXL = CreateObject("Excel.application")
oXL.Visible = (DEBUGFLY = 1)
Set oWB = oXL.Workbooks.Open("K:\" & strReference & ".xlsm")
Sheets.Add.Name = "Tableau importation"
The problem comes down the line : Sheets.Add.Name = "Import table"
In my opinion it didn't define the application or the file of the creation well. For several days I have been looking for the solution but impossible to find on the different forums.
When this line passes I have no problem anymore but it takes me the cabbage ...
Wouldn't the bug happen on files that already have a sheet named "Import Table"?
Be careful, Excel does not accept to have several sheets with the same name, you may need to integrate a check on the name of the existing sheets in the file before creating your sheet.
It's indeed a possibility but it's not the problem.
Sometimes I have the bug, I restart 4-5 times and it ends up passing, I just end up with 4-5 times the excel program launch, with the same name just the 'read only' that changes.
No I don't close excel and don't free up the memory, I'm still finalizing my macro, so I'm using the import table as a means of verification.
To know if it is importing the data and it is transforming it in the way I want.
I just thought that there will be another way to check my data, which is to save the sheet in a folder to control the perfectible cases.
I'm getting to a point where my excel part is finished, well the processing part after I just finalized my information.
I still have some problems on my macro on the Solidworks side (saving my assemblies) but it will be part of another post if I don't find the solution in a few days.
Option Explicit
Dim oXL As Excel.Application
Dim oWB As Workbook
Dim oSH As Worksheet
Sub main()
'Démarrage de Excel en mode visible
Set oXL = CreateObject("Excel.application")
oXL.Visible = True
'Ouverture du classeur
Set oWB = oXL.Workbooks.Open("C:\Users\XXX\Desktop\Test.xlsx") 'Attention, il faut Changer le chemin et nom du fichier
'Ajout de la feuille
oWB.Sheets.Add.Name = "Tableau importation"
'Pause de 2 secondes
Dim newHour As Date
Dim newMinute As Date
Dim newSecond As Date
Dim waitTime As Date
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
oXL.Wait waitTime
'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
Be careful, I added "oWB" at the beginning of the line "Sheets.Add.Name = "Import table""
As a result, it doesn't close my other instances of excel but each time I launch a macro, at the end it makes my backup, closes excel (visually) but I still have a process in progress, my code:
Sub BackUpExcel(ByVal strReference As String)
oXL.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=_
"T:\petit chemin\" & "-TabImp.xlsm", CreateBackup:=False
oXL.DisplayAlerts = False
oXL.Workbooks.Close
oXL.Quit
Set oWB = Nothing
Set oXL = Nothing
End Sub
On the second run, if I don't delete the process, it creates my page but aims further, on the designation of the leaves.
Yes, I did add the oWB at the beginning of the line, it corrects the problem in this module.
Now my problem is that I created another module to gain readability and now it lets me down.
My code after modification:
Option Explicit
Dim oXL As Excel.Application
Dim oWB As Workbook
Sub initialiseExcel(ByVal strReference As String)
'Start excel and get application
Set oXL = CreateObject("Excel.application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("K:\" & strReference & ".xlsm")
oWB.Sheets.Add.Name = "Tableau importation"
End Sub
Sub BackUpExcel(ByVal strReference As String)
oXL.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="chemin" & "-TabImp.xlsm", CreateBackup:=False
oXL.DisplayAlerts = False
oXL.Workbooks.Close
oXL.Quit
Set oWB = Nothing
Set oXL = Nothing
End Sub
Sub FabTab(d, b, c)
Dim i As Integer
Dim FT As Object
Dim SLD As Object
Dim RSG As Object
Set FT = oWB.Sheets("FT")
Set SLD = oWB.Sheets("Tableau importation")
Set RSG = oWB.Sheets("RSG")
If d = 17 Then
Call Plateau
Other module:
Dim FT As Object
Dim SLD As Object
Dim RSG As Object
Sub Plateau()
Set FT = Sheets("FT")
and then it crashes "Runtime error '1004' error defined by the app or by the object. But only on the second launch when I don't clean up my processes.
By the way, before I didn't have to specify oWb.Sheets in my 'FabTab', I wrote sheets(FT). I imagine that it will make me gain in resources.
Sorry if these are beginner's questions but I don't have much hair left...
Same thing, in the "Sub Plateau()", you have to put "Set FT = oWB.Sheets("FT")" instead of "Set FT = Sheets("FT")" and, if you want your Excel process to close, you also have to think about freeing the resource of the FT object before starting to close Excel. In the following example, this is what the line "Set FT = Nothing" present in the "Sub RecupExcelDeux(ByVal nameSheet As String)" does, without it, the Excel process does not close despite calling the "CloseExcel" procedure present at the end of the "Sub main()" which is logical because the FT object is linked to Excel since it is A copy of an Excel-related object:
Option Explicit
Dim oXL As Excel.Application
Dim oWB As Workbook
Dim oSH As Worksheet
Dim FT As Object
Dim newHour As Date
Dim newMinute As Date
Dim newSecond As Date
Dim waitTime As Date
Sub main()
InitialiseExcel ("C:\Users\DRO\Desktop\Test.xlsx")
PauseExcel (2)
RecupExcel ("Tableau importation")
FermetureExcel
End Sub
Sub InitialiseExcel(ByVal strReference As String)
'Start excel and get application
Set oXL = CreateObject("Excel.application")
oXL.Visible = True
'Ouverture du classeur
Set oWB = oXL.Workbooks.Open(strReference)
'Ajout de la feuille
oWB.Sheets.Add.Name = "Tableau importation"
End Sub
Sub FermetureExcel()
'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
Sub PauseExcel(ByVal duree As Integer)
'Pause de 2 secondes
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + duree
waitTime = TimeSerial(newHour, newMinute, newSecond)
oXL.Wait waitTime
End Sub
Sub RecupExcel(ByVal nameSheet As String)
Set FT = oWB.Sheets(nameSheet)
PauseExcel (2)
Set FT = Nothing
RecupExcelDeux (nameSheet)
End Sub
Sub RecupExcelDeux(ByVal nameSheet As String)
Set FT = oWB.Sheets(nameSheet)
PauseExcel (2)
Set FT = Nothing
End Sub
Okay, thank you for this information. Sorry for the extended weekend response time...
How can I link my variable 'FT' to excel in another module? Each time I get the error Object variable or block variable not defined.
Option Explicit
Dim FT As Object
Dim SLD As Object
'Dim RSG As Object
Dim oWB As Excel.Workbook
Sub Plateau()
Set FT = oWB.Sheets("FT")
Set SLD = oWB.Sheets("Tableau importation")
'Set RSG = oWB.Sheets("RSG")
I could all add to the same module but it would lose readability:(
What's the difference between Workbook and Worksheet?
This is how OOP (object-oriented programming) works, a notion that it can be useful to know a little before starting to create macros...
Firstly: If you create a reference to an internal object of an Excel.Application object without releasing it, then this will prevent Excel from closing.
The objects in the Workbook class represent Excel workbooks, they are members of the Workbooks collection. Objects in the Worksheet class represent worksheets, they are members of the Worksheet collection of the Workbook object.
Dim oWB As Workbook ==> Set oWB = oXL.Workbooks.Open(....) ==> you define oWB as a Workbook object (So it's an object contained in the Excel object model). Dim FT As Object ==> Set FT = oWB.Sheets(....) ==> you define FT as an object in which you assign a Workbook object (So it's an object contained in the Excel object model).
Thank you for your patience and your answer. I will find out about these different tracks.
As we get further and further away from the subject, I put the answer with the "Set FT = nothing" to free up the resources as the best. I didn't give all the information at the beginning I'm sorry.