嵌入基于单元格的OLE对象 [英] Embed OLEobject based on cell

问题描述

我想在OLEObject(文本文件)中嵌入Excel，文件名是从特定单元格派生的.我可以将其做为一次性操作，但现在尝试使它在列中的所有单元格中循环运行，直到遇到空单元格时结束.

我似乎无法获得正确的语法来使If/Else循环正常工作:

  Sub Insert_Text_File()作为OLEObject的昏暗昏暗的路径作为字符串昏暗的文件作为字符串昏暗的filename不可见为字符串昏暗范围A作为范围昏暗范围D作为范围路径= ActiveWorkbook.Path文件=范围(i，1).值&"-Live"和.文本文件"设置范围A =范围("A"& i)设置rangeD = Range("D"& i)当我= 2至200如果Range("A"& i)<>"然后设置ol = Worksheets("Inventory").OLEObjects.Add(文件名:=路径&"\"&文件，链接:= False，DisplayAsIcon:= True，高度:= 10)ol.Top = Range("D"& i).topol.left = Range("D"& i).left万一接下来我结束子 

解决方案

我认为您当前方法的问题在于，您仅将值分配给 path 变量一次- file=范围(i，1).值&"-Live"和循环增加 i 之前为".txt" .

一种需要较少变量的更好方法是使用 Range 类型的 cell 变量为每个循环使用，并依靠VBA查找最后一行而不是将200硬编码到循环中.

尝试这种方法，让我们知道是否可行.

  Sub Insert_Text_File()Application.ScreenUpdating = False暗单元格范围'循环A列中的每个单元格对于Range("A2:A"& Range("A"& Rows.Count).End(xlUp).Row)中的每个单元格'在进行任何工作之前，请确保单元格不为空如果不是IsEmpty(cell)然后'根据路径创建并插入新的OleObject作为OLEObject的昏暗ActiveWorkbook.path&"\"&单元格"-Live.txt"将使文件名设置ol = ActiveSheet.OLEObjects.Add(_文件名:= ActiveWorkbook.path&"\"&单元格"-Live.txt"，_连结:= False，_DisplayAsIcon:=正确，_高度:= 10)'将OleObject与D列对齐-(0行，A列右边3列)与ol.Top = cell.Offset(0，3).Top.Left = cell.Offset(0，3).Left结束于万一下一个Application.ScreenUpdating =真结束子 

I want to embed an OLEObject (text file) in Excel, with the filename being derived from a particular cell. I can do this as a one off action but am now trying to make it work in a loop through all the cells in a column, finishing when it comes across an empty cell.

I can't seem to get the right syntax to make the If/Else loop work:

Sub Insert_Text_File()

Dim ol As OLEObject
Dim path As String
Dim file As String
Dim filenameinvisible As String
Dim rangeA As Range
Dim rangeD As Range

path = ActiveWorkbook.Path
file = Range(i,1).Value & "-Live" & ".txt"
Set rangeA = Range("A" & i)
Set rangeD = Range("D" & i)

For i = 2 To 200
    If Range("A" & i) <> "" Then
        Set ol = Worksheets("Inventory").OLEObjects.Add (Filename:= path & "\" & file, Link:=False, DisplayAsIcon:=True, Height:=10)

        ol.Top =Range("D" & i).top

        ol.left=Range("D" & i).left

    End If
    Next i
End Sub

解决方案

I think the problem with your current approach is that your are assigning the value to the path variable only once - file = Range(i,1).Value & "-Live" & ".txt" before the loop increases i.

A better approach requiring less variables would be using a for each loop using a cell variable of Range type and relying on VBA to find the last row used rather than hard-coding 200 into the loop.

Try this approach and let us know if that has worked.

Sub Insert_Text_File()
Application.ScreenUpdating = False

    Dim cell As Range

    ' loop each cell in column A 
    For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
         ' make sure the cell is NOT empty before doing any work
         If Not IsEmpty(cell) Then

            ' create and insert a new OleObject based on the path
            Dim ol As OLEObject
            ' ActiveWorkbook.path & "\" & cell & "-Live.txt" will make the filename
            Set ol = ActiveSheet.OLEObjects.Add( _
                                                Filename:=ActiveWorkbook.path & "\" & cell & "-Live.txt", _
                                                Link:=False, _
                                                DisplayAsIcon:=True, _
                                                Height:=10)
            ' align the OleObject with Column D - (0 rows, 3 columns to the right from column A)
            With ol
                .Top = cell.Offset(0, 3).Top
                .Left = cell.Offset(0, 3).Left
            End With
        End If
    Next
Application.ScreenUpdating = True
End Sub

这篇关于嵌入基于单元格的OLE对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！