Writing to excel with VBA

[Thomas.Long]

I've been having an error in a part that I want to write to excel. The gist is that this part is a generator for a number of parts of the same geometry of it and I want them to be reusable so I have them write to an excel file so that I can do a lookup and report the file number if the specified part already exists. 

I tried the built in ILogic commands but every time I used that excel would throw out a message asking you to save the file and no amount of commands telling it to just go ahead and save or silence the program was enough to make it stop throwing those save messages on closing the excel file. So I used vba instead. The issue with the vba program is that it seems to be opening the program twice every single time. So it opens the file, then opens it again, and then reports that the file is open by someone else (yourself) and cannot be written to. 

Can anyone tell me why its throwing this error? 

Thank you,

Thomas Long

Sub Main()

On Error GoTo ErrorHandler

    If Not Dir("C:\Users\" & Environ$("Username") & "\Documents\RESUME.XLW") = "" Then Kill ("C:\Users\" & Environ$("Username") & "\Documents\RESUME.XLW")

    ' Create the Excel application.
    Dim oRow As Integer
    
    Dim oParameters As Parameters
    Dim oLength As Parameter
    Dim oWidth As Parameter
    Dim oMaterial As Parameter
    Dim oType As Parameter
    
    Dim odoc As PartDocument
    
    Dim oExcel As Object
    Dim oBook As Object
    Dim objWorkbook As Object
    Dim oSheet As Object
    
    Dim UOM As UnitsOfMeasure
    
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks
    Set objWorkbook = oBook.Open("N:\Mechpart\TLONG\Parts Lists\INVENTOR COVER PLATES.xlsx")
    Set oSheet = objWorkbook.Worksheets(1)
    
    Set odoc = ThisApplication.ActiveDocument
    Set oParameters = odoc.ComponentDefinition.Parameters
    Set UOM = odoc.UnitsOfMeasure
    
    'Makes Excel invisibile
    oExcel.Visible = False
    
    Set oLength = oParameters.Item("Length")
    Set oWidth = oParameters.Item("Width")
    Set oMaterial = oParameters.Item("Material")
    Set oType = oParameters.Item("Type")


'Write in Data
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    With oSheet
        oRow = 1
        
        Do While .range("A" & oRow) <> ""
            oRow = oRow + 1
        Loop

        .range("A" & oRow) = UOM.ConvertUnits(oLength.Value, UOM.GetTypeFromString(UOM.GetDatabaseUnitsFromExpression(oLength.Expression, oLength.Units)), oLength.Units)
        .range("B" & oRow) = UOM.ConvertUnits(oWidth.Value, UOM.GetTypeFromString(UOM.GetDatabaseUnitsFromExpression(oWidth.Expression, oWidth.Units)), oWidth.Units)
        .range("C" & oRow) = oMaterial.Value
        .range("D" & oRow) = oType.Value
        .range("E" & oRow) = odoc.File.FullFileName
        .range("F" & oRow) = Split(Split(odoc.File.FullFileName, "\")(UBound(Split(odoc.File.FullFileName, "\"))), ".")(0)
        
    End With
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    
    
'Saving and exiting Excel
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    oExcel.Save
    oExcel.Quit

    Set oSheet = Nothing
    Set oBook = Nothing
    Set objWorkbook = Nothing
    Set oExcel = Nothing
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    Exit Sub


ErrorHandler:
    oExcel.Save
    oExcel.Quit

    Set oSheet = Nothing
    Set oBook = Nothing
    Set objWorkbook = Nothing
    Set oExcel = Nothing

    Exit Sub
End Sub

[ssurratt]

inlcude the excel object library in your vba project. then replace all those object dims with actual excel dims

[WCrihfield]

As @ssurratt said, you will likely have much better success working with Excel after you have turned on the reference to the Excel Object Library.

Just in case you don't know how to do that, I will try to explain here.

1. Open Ivnentor's VBA Editor
2. Click on the "Tools" menu, then slide down and click on "References..."
3. In the "References - ApplicationProject" dialog box that opens, scroll within the list of "Available References:" until you see something like "Microsoft Excel 16.0 Object Library", then check the little checkbox beside its name in that list.  (You may have a different version of Excel, so "16.0" may be different in that name.)
4. Then click the [OK] button on that dialog box to finish and accept the changes.

Now you should have access to, and be able to use, all the objects (Types) defined within the Excel library, such as Workbook, Worksheet, Range, etc., and enable the recognition of those objects, so that when you put a dot (period) after a variable of one of these type objects, it will automatically suggest any available properties, methods, events, etc..

Then within your code, you can change the 'Type' of those variables from 'Object' to their actual 'Type' as defined within Excel.  You will then, also be able to create a new instance of the Excel application the natural way, (New Excel.Application) without having to use CreateObject(), then you won't have to destroy those object variables at the end anymore either.

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you have time, please... Vote For My IDEAS 💡or you can Explore My CONTRIBUTIONS

Inventor 2021 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum