hexagon logo

Downloading the Assignment and saving the values in excel

I have a problem with the code, saving to excel works, but getting the value from assignment V1 causes an error


Sub Main()

Dim objExcel As Object
Dim objWorkbook As Object
Dim objWorksheet As Object

'Nazwa pliku Excel
Dim excelFileName As String
excelFileName = "Excel.xlsx"

'Ścieżka Do pliku Excel
Dim excelFilePath As String
excelFilePath = "T:\Kontrola_jakosci\PC-DMIS\Damian\" & excelFileName

'Jeśli plik Excel nie istnieje, utwórz nowy plik
If Dir(excelFilePath) = "" Then
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Add
    Set objWorksheet = objWorkbook.Sheets(1)

Else
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open(excelFilePath)
    Set objWorksheet = objWorkbook.Sheets(1)
End If

Dim App As Object
Set App = CreateObject ("PCDLRN.Application")
Dim Part As Object
Set Part = App.ActivePartProgram
Dim Var As Object
Set Var = Part.GetVariableValue ("V1")

Dim i As Integer
objWorksheet.Cells(1, 1 ).Value =   "Wymiar x"

i = 1
Do Until objWorksheet.Cells(i,1).Value = empty
i = i +1
Loop

objWorksheet.Cells(i, 1 ).Value  =  Var


objWorkbook.Save
objWorkbook.Close

objExcel.Quit
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub​


  • What is the error?

    Honestly I am not sure what this code is meant to do -

    i = 1
    Do Until objWorksheet.Cells(i,1).Value = empty
    i = i +1
    Loop​

    The title implies you are writing to Excel from PC-DMIS but first glance at the code snippet implies to me as if it is trying to read from Excel?

    Can you pseudo-code the workflow you are trying to achieve here please? It would save the trouble of trying to reverse engineer the code
  • VAR is an object variable which gives access to the properties and methods of the object (in this case an ASSIGNMENT)

    You access the value using VAR.STRINGVALUE, or VAR.LONGVALUE if I remember correctly.

  • Sub Main()
    
    Dim objExcel As Object
    Dim objWorkbook As Object
    Dim objWorksheet As Object
    
    'Excel file name
    Dim excelFileName As String
    excelFileName = "Excel.xlsx"
    
    'The path to the Excel file
    Dim excelFilePath As String
    excelFilePath = "T:\Kontrola_jakosci\PC-DMIS\Damian\" & excelFileName
    
    'If the Excel file doesn't exist, create a new file
    If Dir(excelFilePath) = "" Then
        Set objExcel = CreateObject("Excel.Application")
        Set objWorkbook = objExcel.Workbooks.Add
        Set objWorksheet = objWorkbook.Sheets(1)
    Else
        Set objExcel = CreateObject("Excel.Application")
        Set objWorkbook = objExcel.Workbooks.Open(excelFilePath)
        Set objWorksheet = objWorkbook.Sheets(1)
    End If
    
    'Column title
    Dim i As Integer
    objWorksheet.Cells(1, 1 ).Value =   "Wymiar x"
    
    'Finding the first available row
    i = 1
    Do Until objWorksheet.Cells(i,1).Value = empty
    i = i +1
    Loop
    
    'Using Assignment V1 to input values into a row
    Dim App As Object
    Set App = CreateObject ("PCDLRN.Application")
    Dim Part As Object
    Set Part = App.ActivePartProgram
    Dim Var As Object
    Set Var = Part.GetVariableValue ("V1")
    
    objWorksheet.Cells(i, 1 ).Value  =  Var.LongValue
    
    
    'Saving and closing Excel
    objWorkbook.Save
    objWorkbook.Close
    
    objExcel.Quit
    Set objExcel = Nothing
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    End Sub​


    The code is designed to conduct statistics from measurements for key dimensions and document it in Excel

    This part of the code is not working:

    'Using Assignment V1 to input values into a row
    Dim App As Object
    Set App = CreateObject ("PCDLRN.Application")
    Dim Part As Object
    Set Part = App.ActivePartProgram
    Dim Var As Object
    Set Var = Part.GetVariableValue ("V1")
    
    objWorksheet.Cells(i, 1 ).Value  =  Var.LongValue
    ​


    At this moment, it inputs the value of 0 instead of the measured value. This is a screenshot from the program:


  • Good day,

    "long" belongs to group of integers (no decimal range)

    "LOC2.D.MEAS" looks like a "Double" though

    try "Var.DoubleValue", that should work​


    PS:
    you can also use a script to read data directly from a dimmension-command (your "LOC2" is a dimmension-command).
    the detour via the variables is possible but unnecessarily prolonged
    there are a few examples in the code example section
  • Looks to me that he is looking for the first empty cell.
  • The use of Var.DoubleValue works correctly. Thank you for your help.
  • Thank you VPT.se, I try not to think too hard on Friday afternoon.