使用Excel，试图从外部HTA找到真正的使用范围 [英] With Excel, trying to find genuine used range from external HTA

问题描述

我一直在使用这个命令：

  LastRow = ActiveSheet.UsedRange.Rows.Count 
  

但是UsedRange属性通常可能不准确。所以我在寻找一个替代方案。我发现一个很棒的提示解释这个方法：

  LastRow = Cells.Find（*，SearchOrder = = xlByRows，SearchDirection： = xlPrevious）.Row 
  

这可以在Excel中处理。不过，我正在运行一个HTA的代码，所以我需要转换这行，我很挣扎。这是我的问题---请问有人可以就如何将这个简单的代码转换成HTA兼容的代码提供指导？

出于兴趣，试图规避这个问题是，我以一种我明白的方式写了一个例程。结果工作，但速度非常慢。这里是笑柄：

  Set objExcel = GetObject（，Excel.Application）
 wb =test .xlsx
 objExcel.Workbooks（wb）.Activate 
 wbactiveSheet = objExcel.Workbooks（wb）.ActiveSheet.Name 
 
'确定第一个xls $范围内的行和列b $ b theNumRow = objExcel.Workbooks（wb）.Sheets（wbactiveSheet）.UsedRange.Rows.Count 
 theNumCol = objExcel.Workbooks（wb）.Sheets（wbactiveSheet）.UsedRange.Columns.Count 
 
'确定正确使用的行：
x = 1'从第一行开始
 blankRows = 0 
做'每行
y = 1'从第一列开始
 blankCells = 0 
 Do'each column 
如果objExcel.Workbooks（wb）.Sheets（wbactiveSheet）.Cells（x，y）.Value<> 然后
 theNumRowActual = x 
'找到非空白，跳到下一行
退出执行列循环
 Else 
 blankCells = blankCells + 1 
结束If 
y = y + 1 
循环直到（y  -  1）= theNumCol 
如果blankCells = theNumCol然后'空行
 blankRows = blankRows + 1 
如果blankRows = 50然后
退出Do 
结束如果
 Else 
 blankRows = 0 
结束如果
x = x + 1 
循环直到x = TheNumRow'ie直到测试区域与usedRange属性匹配
 
'确定正确使用的列：
y = 1'从第一列开始
 blankCols = 0 
做'每列
x = 1'从第一行开始
 blankCells = 0 
做'每行
如果objExcel.Workbooks（wb）.Sheets（wbactiveSheet）.Cells（x，y）.Value< ;> 然后
 theNumColActual = y 
'找到非空白，跳到下一列
退出执行行循环
 Else 
 blankCells = blankCells + 1 
结束如果
x = x + 1 
循环直到（x  -  1）= theNumRowActual 
如果blankCells = theNumRowActual然后'空白栏
 blankCols = blankCols + 1 
如果blankCols = 50然后
 Exit Do 
 End If 
 Else 
 blankCols = 0 
 End If 
y = y + 1 
 Loop Until（ y  -  1）= TheNumCol'ie直到测试区域与usedRange属性匹配
'bug 
 
 
 MsgBoxUSEDRANGEMETHOD：& vbNewLine& rows：& TheNumRow& vbNewLine& 列：& TheNumCol& vbNewLine& vbNewLine& NEWMETHOD：& vbNewLine& rows：& TheNumRowActual& vbNewLine& 列：& 
解决方案
看起来你已经有你的工作表打开并且活跃？在这种情况下：
  Const xlByRows = 1 
 Const xlPrevious = 2 
 
设置objExcel = GetObject（，Excel.Application）
 LastRow = objExcel.ActiveSheet.Cells.Find（*，，，，xlByRows，xlPrevious）.Row 
  pre> 
 
 
查看我的最近的帖子关于使用VBScript调用Excel函数的其他提示。
 
 
 
当您使用Excel中的VBA进行编程时， code>单元格是一个可以直接引用的本机对象。但是当您在HTC或WSH脚本中之外时，您的脚本不知道 Cells 所指的是什么。您唯一的Excel对象是您创建的对象。因此，您必须按照您的主Excel（应用程序）对象的方式工作，然后再到工作簿，然后才能在单元格属性。如果您使用相同的名称，Excel可能会更有意义：
 '定义与Excel相同的对象... 
设置应用程序= GetObject（，Excel.Application）
设置ThisWorkbook = Application.ActiveWorkbook 
 
'现在根据它的名字（Sheet1，在这个例子）
设置MySheet = ThisWorkbook.Sheets（Sheet1）
 
'并操作Cells集合... 
 MySheet.Cells.Find（...） 
  
作为快捷方式，Excel提供了一个 ActiveSheet 属性，您可以直接在应用程序对象上使用，以获取当前工作表，基本上绕过了工作簿图层。这是我在第一个代码段中使用的。
 
I've been using this command:
LastRow = ActiveSheet.UsedRange.Rows.Count
But the UsedRange property can often be inaccurate. So I'm looking for an alternative. I found a great tip explaining this method:
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
This works a treat in Excel. However, I'm running code from an HTA, so I need to convert this line, and I'm struggling. THIS is my question --- please can someone offer guidance on how to convert this simple code into an HTA-compatible one?

Out of interest, to try to circumvent this problem, I tried writing a routine in a way that I understand. The result works, but is very slow. Here it is for giggles:
    Set objExcel = GetObject(,"Excel.Application") 
    wb = "test.xlsx"
    objExcel.Workbooks(wb).Activate
    wbactiveSheet = objExcel.Workbooks(wb).ActiveSheet.Name

    'determine rows and columns in range of first xls
    theNumRow = objExcel.Workbooks(wb).Sheets(wbactiveSheet).UsedRange.Rows.Count
    theNumCol = objExcel.Workbooks(wb).Sheets(wbactiveSheet).UsedRange.Columns.Count

    'determine genuine used rows:
    x = 1 'start at first row
    blankRows = 0
    Do 'each row
        y = 1 'start at first column
        blankCells = 0
        Do 'each column
            If objExcel.Workbooks(wb).Sheets(wbactiveSheet).Cells(x, y).Value <> "" Then
                theNumRowActual = x
                'found non-blank, skip to next row
                Exit Do 'the columns loop
            Else
                blankCells = blankCells + 1
            End If
            y = y + 1
        Loop Until (y - 1) = theNumCol
        If blankCells = theNumCol Then 'blank row
            blankRows = blankRows + 1
            If blankRows = 50 Then
                Exit Do
            End If
        Else
            blankRows = 0
        End If
        x = x + 1
    Loop Until x = theNumRow 'i.e. until the testing area matches the usedRange property

    'determine genuine used columns:
    y = 1 'start at first column
    blankCols = 0
    Do 'each column
        x = 1 'start at first row
        blankCells = 0
        Do 'each row
            If objExcel.Workbooks(wb).Sheets(wbactiveSheet).Cells(x, y).Value <> "" Then
                theNumColActual = y
                'found non-blank, skip to next column
                Exit Do 'the rows loop
            Else
                blankCells = blankCells + 1
            End If
            x = x + 1
        Loop Until (x - 1) = theNumRowActual
        If blankCells = theNumRowActual Then 'blank column
            blankCols = blankCols + 1
            If blankCols = 50 Then
                Exit Do
            End If
        Else
            blankCols = 0
        End If
        y = y + 1
    Loop Until (y - 1) = theNumCol 'i.e. until the testing area matches the usedRange property
    'bug


    MsgBox "USEDRANGEMETHOD:" &vbNewLine & "rows: " & theNumRow & vbNewLine & "columns: " & theNumCol & vbNewLine & vbNewLine & "NEWMETHOD:" & vbNewLine & "rows: " & theNumRowActual & vbNewLine & "columns: " & theNumColActual
Thank you
解决方案
It looks like you already have your worksheet open and active? In that case:
Const xlByRows = 1
Const xlPrevious = 2

Set objExcel = GetObject(,"Excel.Application")
LastRow = objExcel.ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
See my recent post about using VBScript to call an Excel function for additional tips.

When you're programming using VBA within Excel then Cells is a native object that you can reference directly. But when you're outside of Excel -- in an HTA or WSH script -- your script has no idea what Cells refers to. The only Excel objects you have are the ones you create. So you must work your way down the chain, from your main Excel (Application) object to a workbook and then to a sheet before you can operate on the Cells property. It might make more sense if you use the same names Excel does:
' Define the same objects that Excel does...
Set Application = GetObject(, "Excel.Application")
Set ThisWorkbook = Application.ActiveWorkbook

' Now get a sheet based on its name ("Sheet1", in this example)..
Set MySheet = ThisWorkbook.Sheets("Sheet1")

' And operate on the Cells collection...
MySheet.Cells.Find(...)
As a shortcut, Excel provides an ActiveSheet property that you can use directly on the Application object to get the current sheet, essentially bypassing the workbook layer. That's what I've used in my first code snippet.

                        
这篇关于使用Excel，试图从外部HTA找到真正的使用范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！