循环更新Userform上的图表图像

问题描述

好的，

所以我试着整合下面的代码：

So I am trying to integrate the following code:

`Private Sub UserForm_Initialize()
    Dim Fname As String

    Call SaveChart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    Me.Image1.Picture = LoadPicture(Fname)
End Sub

Private Sub SaveChart()
    Dim MyChart As Chart
    Dim Fname As String

    Set MyChart = Sheets("Data").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    MyChart.Export Filename:=Fname, FilterName:="GIF"
End Sub`

来自此帖子：到现有循环中，以便上面的代码段与循环一起循环，并且每次循环执行时更新映像。我会将此发布为对上述问题的评论，但我没有足够的代表。

from this post: Displaying "live" chart data in Excel Userform into an existing loop so that the above section of code iterates with the loop and the image is updated every time the loop executes. I would have posted this as a comment on the above mentioned question, but I do not have enough rep.

'
' Get last logged data
'
Private Sub GetLastLoggedData()
    Dim rangeToWrite
    Dim lStartFileSize As Long
    Dim lNextFileSize As Long
    Dim dtStartTime As Date
    Dim lElapsedTime As Long
    Dim bDone As Boolean
    Dim sLastHeader As String
    Dim sLastData As String
    Dim iCol As Integer
    Dim sNextValue As String
    Dim iLoop As Integer
    Dim Fname As String
    Dim MyChart As Chart

    On Error GoTo ErrorHandler

   ' Initialize global_bHasScanCount flag
   global_bHasScanCount = False

   ' Get the file size of the log file
    lStartFileSize = FileLen(global_sLogFile)

    ' Initialize timer
    dtStartTime = Now

    ' Wait for filesize to change
    UpdateLogStatus Now & " : Data Monitor: waiting for file size to change..."
    bDone = False
    Do
        ' Get the file size of the log file and see if it's changed
        lNextFileSize = FileLen(global_sLogFile)
        If lNextFileSize <> lStartFileSize And lNextFileSize <> 0 Then
            bDone = True
        Else
            lElapsedTime = DateDiff("s", dtStartTime, Now)
            If (lElapsedTime >= global_lTimeout) Or (lElapsedTime < 0) Then
                bDone = True
            End If
        End If

        DoEvents

    Loop Until bDone = True

    ' Backup the file
    UpdateLogStatus Now & " : Data Monitor: backing up data file..."
    'FileCopy global_sLogFile, global_sLogFileBackup

    ' Read the log file
    UpdateLogStatus Now & " : Data Monitor: reading data file..."
    sLastData = ""
    sLastHeader = ""

    If ReadLogFile(global_sLogFile, sLastData, sLastHeader) = False Then
        ' Delete the backup log file
        'Kill global_sLogFileBackup

        Exit Sub
    End If

    ' Delete the backup log file
    'Kill global_sLogFileBackup

    UpdateLogStatus Now & " : Data Monitor: updating worksheet..."


    ' Clear previous Results in Excel Spreadsheet
    ThisWorkbook.Worksheets("ACQUIRE DATA").Range("A2:IV2").ClearContents

    ' Parse comma delimeted header and place data into worksheet cells.  If we have the scan count then
    ' start writing in column1, else starting writing in column2.

    If global_bHasScanCount = True Then
        iCol = 1
    Else
        iCol = 2
    End If

    If sLastHeader <> "" Then
        ' Clear previous Results in Excel Spreadsheet
        ThisWorkbook.Worksheets("ACQUIRE DATA").Range("A1:IV1").ClearContents

        Do
            sNextValue = GetToken(sLastHeader, ",")

            ' Copy Results to Excel worksheet
            ThisWorkbook.Worksheets("ACQUIRE DATA").Cells(1, iCol).Value = sNextValue
            iCol = iCol + 1

        Loop Until sLastHeader = ""
    End If

    ' Parse comma delimeted results and place data into worksheet cells.  If we have the scan count then
    ' start writing in column1, else starting writing in column2.

    If global_bHasScanCount = True Then
        iCol = 1
    Else
        iCol = 2
    End If
    Do
        sNextValue = GetToken(sLastData, ",")

        ' Copy Results to Excel worksheet
        ThisWorkbook.Worksheets("ACQUIRE DATA").Cells(2, iCol).Value = sNextValue
        iCol = iCol + 1
        'Copy Current Data to Control Panel
        UserForm2.TextBox2.Text = Sheets("ACQUIRE DATA").Range("B12")
        'Copy Time to Control Panel
        UserForm2.TextBox3 = Format(Sheets("ACQUIRE DATA").Range("B13"), "hh:mm:ss")
        'Copy Speed to Control Panel
        UserForm2.TextBox4.Text = Sheets("ACQUIRE DATA").Range("B14")

   'create .gif file of current PerfMap chart
    Set MyChart = Sheets("PerfMap").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp1.gif"
    MyChart.Export Filename:=Fname, FilterName:="GIF"
    Fname = ThisWorkbook.Path & "\temp1.gif"
    'set live data chart image to most recent image
    UserForm3.Image1.Picture = LoadPicture(Fname)


    Loop Until sLastData = ""
    UpdateLogStatus ""



    Exit Sub

ErrorHandler:
    BuildErrorMessage "GetLastLoggedData", "Failed to get last logged data."
    UpdateLogStatus ""



End Sub'

但是每当我这样做，我得到一个由错误处理程序构建的运行时错误1004。

However whenever I do this I get a Runtime Error 1004 that is built by the error handler. Any help on why this is not working is much appreciated.

编辑：走了24小时后，我意识到有问题的工作表是一个图表工作表，而不是一个嵌入式图表，因此上面尝试使用的方法不起作用。

EDIT: After walking away for 24 hours, I realized that the sheet in question is a chart sheet, and not an embedded chart, therefore the method I was attempting to use above was not working.

图表位于PerfMap的图表表图表名称为图7。

The chart is on a chart sheet called PerfMap, but the chart name is Chart 7.

推荐答案

下面的代码解决了我的问题。

The below code solved my problem.

'create .gif file of current PerfMap chart
    Set PerfMap = Charts("PerfMap")
    Fname = ThisWorkbook.Path & "\temp1.bmp"
    PerfMap.Export Filename:=Fname, FilterName:="BMP"

    'set live data chart image to most recent image
    UserForm3.Image1.Picture = LoadPicture(Fname)

根据此MSDN 必须使用BMP才能使图片正常工作

According to this MSDN https://support.microsoft.com/en-us/kb/175918 one must use BMP to get the picture to work properly.

现在我的新问题是上面的代码会使我的循环变慢，随着时间的推移，我最终使用99％的系统内存，直到我退出excel，重新打开它。

Now my new problem is that the above code slows my loop to a crawl and over time I end up using 99% of my system memory until I exit excel and re-open it.

这篇关于循环更新Userform上的图表图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！