如果工作表包含使用VBA的某个字符串，请参考单元格 [英] Reference a cell if the sheet contains a certain string using VBA

问题描述

下面的代码在我的活动工作表中复制ADXL364工作表，但如果包含XL364或364，我可以复制工作表

如果我把星号'C：\data [adxl364.xls] * ADXL364_QC'！A1在我的代码它不工作。

  Sub GetRange（）
 With Range（A：Z）
 .Formula == If（'C：\ data\ [adxl364.xls] ADXL364_QC'！A1> 0，'C：\data\ [adxl364.xls] ADXL364_QC'！A1，Text（，））
 .Formula = .Value 
以
结束End Sub 
  

将从用户获取文件的位置，然后复制包含 ADXL364 或 XL364

的工作表

  With ActiveWorkbook 
 Sheets.Add.Name =Flow_table
 Application.EnableEvents = False 
 
 TP_location = Left（TextBox1.Value，InStrRev .Value，\））
 TP_filename = Right（TextBox1.Value，Len（TextBox1.Value） -  InStrRev（TextBox1.Value，\））
 TP_filename =[& amp; ; TP_filename& ]
 TP_formula ='& TP_location& TP_filename& TextBox2.Value& '！A1
 
 getcellvalue == if（& TP_formula&> 0，& TP_formula&，&）
 
使用范围（A：Z）
 .Formula = getcellvalue 
 .Formula = .Value 
结束于
 
 Sheets.Add。 Name =Job_lists
 
结束于
卸载UserForm2 
结束子
  

<

但是，这是一个非常可怕的方法，更优雅的解决方案可能是使用ADO。例如，您可以运行两个查询：表模式中的第一个，它将在指定的文件中提供工作表名称，第二个在找到的工作表名称上。这将产生一个 RecordSet ，其中包含您关闭的工作表的数据，可以使用直接写入 Range c $ c> .CopyFromRecordset 方法。当然，您可以运行第一个查询来查找您的工作表名称，并按照您发布的代码继续操作。

下面的示例显示了两个查询。这是所有晚期绑定，所以你不需要引用ADO库，但我会离开这个决定给你。我把一些常数放在模块的顶部，这可能需要根据你所使用的Excel版本而改变。您还需要编写自己的错误处理（特别是关闭连接），但是，我会为您留下一个错误处理。

 选项显式
私有的Const SCHEMA_TABLES As Integer = 20 
私有的Const OPEN_FORWARD_ONLY As Integer = 0 
私有的LOCK_READ_ONLY作为Integer = 1 
私人的Const CMD_TEXT As Long = 1 
 Private Const PROVIDER As String =Microsoft.ACE.OLEDB.12.0
 Private Const XL_PROP As String =Excel 12.0; HDR = No
 Private Const SHEETS_FIELD_NAME String =TABLE_NAME
 
 public Sub AcquireData（）
 Dim fPath As String 
 Dim fName As String 
 Dim key As String 
 Dim addr As String 
 Dim oConn As Object 
 Dim oRS As Object 
 Dim connString As String 
 Dim sql As String 
 Dim发现为布尔
 Dim sheetField As String 
 
'定义路径和文件名
 fPath =C：\Users\User\Documents\StackOverflow
 fName =closed_book.xlsx
 
'定义搜索键
 key =XL364
 
'定义关闭的工作表的地址
'如果读取一个单元格，则使用[address：address]例如A1：A1
 addr =A1：E5
 
'晚绑定ADO对象
设置oConn = CreateObject（ADODB.Connection）
 set oRS = CreateObject（ADODB.Recordset）
 
'打开conection 
 connString =Provider =& PROVIDER& ; & _ 
Data Source =& fPath& \& fName& ; & _ 
Extended Properties =& XL_PROP& ; 
 
 oConn.Open connString 
 
'在表（即表单）中搜索包含您的键
'的工作表名称
 found = False 
 oRS.Open oConn.OpenSchema（SCHEMA_TABLES）
 Do While not oRS.EOF 
如果InStr（sheetField，key）> gt，则sheetField = oRS.Fields（SHEETS_FIELD_NAME）.Value 
。 0 Then 
 found = True 
退出Do 
结束如果
 oRS.MoveNext 
循环
 oRS.Close 
 
读取目标数据
如果找到则
 sql =SELECT * FROM [& _ 
 sheetField&加法器]; 
 
 oRS.Open sql，oConn，OPEN_FORWARD_ONLY，LOCK_READ_ONLY，CMD_TEXT 
 
'将数据写入工作表
如果不是oRS.EOF则
 ThisWorkbook .Worksheets（Sheet1）。范围（A1）_ 
 .CopyFromRecordset oRS 
结束如果
 
结束如果
 
 
 '管家
 oRS.Close 
设置oRS =无
 oConn.Close 
设置oConn =无
 
结束子
  

the code below copies "ADXL364" sheet in my active worksheet, but is there way that I can copy the sheet if it contains "XL364" or "364"

if I put asterisk 'C:\data[adxl364.xls]*ADXL364_QC'!A1 in my code it does not work.

Sub GetRange()
    With Range("A:Z")
        .Formula = "=If('C:\data\[adxl364.xls]ADXL364_QC'!A1 > 0,'C:\data\[adxl364.xls]ADXL364_QC'!A1,Text(,))"
        .Formula = .Value
    End With
    End Sub

the long code will be getting the location of file from the user then copying a worksheet that contains ADXL364 or XL364

With ActiveWorkbook
    Sheets.Add.Name = "Flow_table"
    Application.EnableEvents = False

    TP_location = Left(TextBox1.Value, InStrRev(TextBox1.Value, "\"))
    TP_filename = Right(TextBox1.Value, Len(TextBox1.Value) - InStrRev(TextBox1.Value, "\"))
    TP_filename = "[" & TP_filename & "]"
    TP_formula = "'" & TP_location & TP_filename & TextBox2.Value & "'!A1"

    getcellvalue = "=if(" & TP_formula & ">0," & TP_formula & "," & """"")"

        With Range("A:Z")
        .Formula = getcellvalue
        .Formula = .Value
        End With

    Sheets.Add.Name = "Job_lists"

End With
Unload UserForm2
End Sub

解决方案

An ugly, but possible, way would be with a brute force error trapping technique.

However, a more elegant solution might be to use ADO. You could for example run two 'queries': the first on the table schema which would give you your sheet names in the specified file, and the second on the found sheet name. This would produce a RecordSet containing the data of your closed sheet which can be written directly to a Range using the .CopyFromRecordset method. Of course, you could just run the first query to find your sheet name and move on as you have in your posted code.

The example below shows the code for the two queries. It's all late bound so you needn't reference the ADO library but I'll leave that decision to you. I've put a few constants at the top of the module which might need changing depending on which version of Excel you have. You'll also need to write your own error handling (especially to close the connection) but, again, I'll leave that one for you.

Option Explicit
Private Const SCHEMA_TABLES As Integer = 20
Private Const OPEN_FORWARD_ONLY As Integer = 0
Private Const LOCK_READ_ONLY As Integer = 1
Private Const CMD_TEXT As Long = 1
Private Const PROVIDER As String = "Microsoft.ACE.OLEDB.12.0"
Private Const XL_PROP As String = """Excel 12.0;HDR=No"""
Private Const SHEETS_FIELD_NAME As String = "TABLE_NAME"

Public Sub AcquireData()
    Dim fPath As String
    Dim fName As String
    Dim key As String
    Dim addr As String
    Dim oConn As Object
    Dim oRS As Object
    Dim connString As String
    Dim sql As String
    Dim found As Boolean
    Dim sheetField As String

    'Define the path and file name
    fPath = "C:\Users\User\Documents\StackOverflow"
    fName = "closed_book.xlsx"

    'Define the search key
    key = "XL364"

    'Define the address of closed worksheet
    'If reading one cell then use [address:address], eg "A1:A1"
    addr = "A1:E5"

    'Late bind the ADO objects
    Set oConn = CreateObject("ADODB.Connection")
    Set oRS = CreateObject("ADODB.Recordset")

    'Open conection
    connString = "Provider=" & PROVIDER & ";" & _
                 "Data Source=" & fPath & "\" & fName & ";" & _
                 "Extended Properties=" & XL_PROP & ";"

    oConn.Open connString

    'Search for the sheet name containing your key
    'in the tables (ie sheets) schema
    found = False
    oRS.Open oConn.OpenSchema(SCHEMA_TABLES)
    Do While Not oRS.EOF
        sheetField = oRS.Fields(SHEETS_FIELD_NAME).Value
        If InStr(sheetField, key) > 0 Then
            found = True
            Exit Do
        End If
        oRS.MoveNext
    Loop
    oRS.Close

    'Read the target data
    If found Then
        sql = "SELECT * FROM [" & _
              sheetField & addr & "];"

        oRS.Open sql, oConn, OPEN_FORWARD_ONLY, LOCK_READ_ONLY, CMD_TEXT

        'Write the data to your worksheet
        If Not oRS.EOF Then
            ThisWorkbook.Worksheets("Sheet1").Range("A1") _
                .CopyFromRecordset oRS
        End If

    End If


    'Housekeeping
    oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing

End Sub

这篇关于如果工作表包含使用VBA的某个字符串，请参考单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！