Stepping through the code works (F8) but running the code (F5) doesn't. FIXED

[Frank Hall]

Hi guys, I'm new here but I wanted to share some knowledge that I've found over the past few days. I spent ages searching online for solutions to this but instead randomly came across the solution myself, which I'd like to pass on.

I've been working with VBA in Excel, in conjunction with AutoCAD Inventor. The basis for my project was to capture the file names of around 4000 pdf files held in a centralised location, cross reference other locations to check if .ipt, .iam and .idw files existed that corresponded with the pdf files and then using these Inventor files, collect a load of data including Thumbnails. It turns out that there is a recent bug with Inventor 64bit, which won't pass bitmap data to Excel:

Mod the Machine: Document Thumbnails and Button Icons

The workaround in Excel for retrieving the Thumbnails is somewhat complicated and as part of this process we have to copy and paste the image data. This is where the strange anomaly occurred. When the data is pasted, it doesn't actually appear straight away when the code is running full speed. Stepping into the code using F8 or adding a break point (clicking on the grey strip to the left of the code) meant that the image would appear. I tried nearly every trick in the book to delay the code enough to allow windows to pass the image data from the clipboard to Excel. I had even used several instances of DoEvents in between the paste operation and the save operation.

It was only when I put this DoEvents function into a For loop that I had success. I started with very large values for the For loop counter thinking that Windows would require hundreds of milliseconds to pass the data but I actually reduced this right down to 1, so a single loop and the process still works most of the time.


To access the code below, an Inventor file is needed. The total Path and file name including file extension goes in as the first argument. Destination folder is self explanatory. Even if you don't intend on getting the below code working, if you run into similar troubles and you've been wondering why your code only works when stepping through as I've seen, then you can at least try this fix. Let me know any thoughts. Thanks!

Public Function SaveThumbnail(NameAndPath As String, DestinationFolder As String)

    'Saves thumbnail to a chosen folder in the same format that BOM Maker uses
    'Requires a blank Worksheet called "Thumbnails".
    'Destination folder must end in a final backslash: c:\temp\thumbnails\
    Dim oApprentice As ApprenticeServerComponent
    Dim oADoc As ApprenticeServerDocument
    Dim Thumbnail As stdole.IPictureDisp
    Dim Pic As Picture
    Dim MyChart As Chart
    Dim objChart As Excel.Chart
    Dim NameAndExt As String
    Dim Name As String
    Dim iCount As Integer
    
    'Remove the Path to leave just the File name (inc ext)
    NameAndExt = Right(NameAndPath, (Len(NameAndPath) - InStrRev(NameAndPath, "\")))
    'Remove the extension to leave just the File name
    Name = Left(NameAndExt, InStrRev(NameAndExt, ".") - 1)
    
    'An apprentice server component is like opening an Inventor application but with less overheads.
    Set oApprentice = New ApprenticeServerComponent
    'If the path is not equal to nothing
    If Dir(NameAndPath) <> "" Then
        'If it's an assembly document, suppress all components so it opens faster.
        If Right(NameAndPath, 4) = ".iam" Then
            Set oADoc = oApprentice.Open(NameAndPath & "<all components="" suppressed="">")
        Else
            Set oADoc = oApprentice.Open(NameAndPath)
        End If
    Else
        Exit Function
    End If


    Worksheets("Thumbnails").Activate
    On Error GoTo errorhandler
    
    'Extract thumbnail and save in temporary file (this image file will only open in Excel)
    Set Thumbnail = oADoc.Thumbnail
    'The following function is an Excel built-in function
    Call SavePicture(Thumbnail, DestinationFolder & Name & ".temp")
    
    'Import picture on to Thumbnails worksheet and copy to clipboard
    Set Pic = Worksheets("Thumbnails").Pictures.Insert(DestinationFolder & Name & ".temp")
    Pic.Copy
    
    'Create and select a chart
    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Thumbnails"
    Selection.Border.LineStyle = 0
    ActiveChart.Parent.Height = 128
    ActiveChart.Parent.Width = 128
    ActiveChart.ChartArea.Select


    'Put the image on to a chart and export the chart as a png
    ActiveChart.Paste
    'Error occurs now in releasing the pasted image if the following For loop is not used.
    'The number of For loop cycles all the way down to 1 work to release the pasted image,
    'but a value of 50 is fast enough to compute and has the added redundancy.
    For iCount = 0 To 50
    DoEvents
    Next iCount


    Worksheets("Thumbnails").ChartObjects(1).Chart.Export Filename:=DestinationFolder & Name & ".png", FilterName:="png"


    'Delete temp objects and files
    Pic.Delete
    Set Pic = Nothing
    ActiveChart.Parent.Delete
    Kill DestinationFolder & Name & ".temp"
    'Worksheets("List").Activate
    Exit Function
errorhandler:
    Debug.Print "Failed to make thumbnail for " & NameAndPath
End Function

</all>