如何检查工作簿中是否存在所需的工作表？ [英] How do I check the desired sheet is there in my workbook?

问题描述

我想要一个excel文件，其中列标题为员工ID，名称，区域，城市和州，不需要相同的顺序。我也有一些相同wbk的表单，上面有几个标题，但不是全部。我想找到这样的表，其中列具有所有提到的标题，而不管顺序如何。我尝试编码，但在行上出错：

对于工作簿中的每个ws（Trials.xslm）。工作表

我收到了<下标超出范围>的错误。

请点亮它。



我尝试了什么：

 私人  Sub  ValidButton_Click（）
  Dim  nColumn 正如  Double  
  Dim  ws 作为工作表
  Dim  i  As  整数 
  Dim  strA 作为  String ，strB 作为 字符串，strC 作为 字符串，strD 作为 字符串，strE 作为 字符串 
 strA =  员工ID 
 strB =  名称 
 strC =  区域 
 strD =  城市 
 strE = < span class =code-string>  State 
 
  For  每个 ws 在工作簿中（  Trials.xslm）。工作表
 对于 i =  1    UsedRange.Columns.Count 
 如果 Cells（ 1 ，i）.Value = strA 或 strB 或 strC 或 strD 或 strE 然后 
 MsgBox  True  
 其他 
 MsgBox < span class =code-keyword> False  
 结束 如果 
 下一步 i 
 下一步 
 
 结束  Sub  

解决方案

< blockquote class =quote>

引用：

我是错误<下标超出范围>。

这意味着 Trials.xslm 已关闭。你必须在开始做其他事情之前打开它。

  Dim  wbk 作为工作簿
 
 设置 wbk = Application.Workbooks，Open（< span class =code-string>  FullPathAndFilename.xlsx）
 
 对于 每个 ws 在 wbk.Worksheets 
 ' 您的代码 

I want an excel file in which there are column headers stated "Employee ID", "Name", "Area", "City" and "State", not necessary in same order. I also have some sheets in same wbk which have few of the above headers, but not all of them. I want to find such sheet where the column have all the mentioned headers, irrespective of the order. I tried coding it, but got an error on the line:

For each ws in Workbooks("Trials.xslm").Worksheets

I got an error of <Subscript out of range>.
Kindly throw a light on it.

What I have tried:

Private Sub ValidButton_Click()
    Dim nColumn As Double
    Dim ws As Worksheet
    Dim i As Integer
    Dim strA As String, strB As String, strC As String, strD As String, strE As String
    strA = "Employee ID"
    strB = "Name"
    strC = "Area"
    strD = "City"
    strE = "State"
    
    For Each ws In Workbooks("Trials.xslm").Worksheets
        For i = 1 To UsedRange.Columns.Count
            If Cells(1, i).Value = strA Or strB Or strC Or strD Or strE Then
                MsgBox True
            Else
                MsgBox False
            End If
        Next i
    Next
        
End Sub

解决方案

Quote:

I got an error of <Subscript out of range>.

This means that Trials.xslm is closed. You have to open it before you start doing anything else.

Dim wbk As Workbook

Set wbk = Application.Workbooks,Open("FullPathAndFilename.xlsx")

For Each ws In wbk.Worksheets
'your code here

这篇关于如何检查工作簿中是否存在所需的工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！