在后台工作簿中查找值

问题描述

我正在努力创建一个代码来在我正在访问的工作簿中搜索一个值，而不必将它带到前台。我使用下面的代码来访问该文件为xl0：

$ p $ 'DATABASE ACCESS
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Dim db_directory As String
db_directory =R：\ New Quality Management System\xls\Supplier Non-Conformance\Supplier Non -Conformance Database.xlsm
Set xlw = xl0.Workbooks.Open（db_directory）


我已经成功地将这个工作添加到xl0电子表格的记录，但是我也需要创建一个修改记录宏来查找一个索引值并提取行号。我尝试了几个变化（下面的两个例子），没有成功，任何人都可以提供意见吗？

例如1

<$ p激活
Cells.Find（What：=2015-16-46，After：= ActiveCell，LookIn：= xlFormulas，_
LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，_
MatchCase：= False，SearchFormat：= False）.Activate

$ b
$ b

  xl0.Worksheets（ SNCR Log）。Range（B：B）。Find（What：=2015-16-46，
 After：= ActiveCell，LookIn：= xlFormulas，_ 
 LookAt：= xlPart ，SearchOrder：= xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）.Activate 
  

在此先感谢，

Dan

解决方案

（顺便说一下，激活是非常贪婪的资源，所以尽量避免它！） > Application.ScreenUpdating = False
$ b $ DATABASE ACCESS
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Dim db_directory As String

xl0.Visible = False

db_directory =R：\新质量管理系统\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\' .Open（db_directory）

MsgBox xlw.Sheets（1）.Cells.Find（What：=2015-16-46，LookIn：= xlFormulas，_
LookAt：= xlPart ，SearchOrder：= xlByRows，SearchDirection：= xlNext，_
MatchCase：= False，SearchFormat：= False）.Row

$ b Application.ScreenUpdating = True


I am struggling to create a code to search for a value within a workbook that I am accessing without bringing it to the foreground. I am using the following code to access the file as "xl0":

'DATABASE ACCESS
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Dim db_directory As String
db_directory = "R:\New Quality Management System\xls\Supplier Non-Conformance\Supplier Non-Conformance Database.xlsm"
Set xlw = xl0.Workbooks.Open(db_directory)

I have successfully had this working to add records to the xl0 spreadsheet however I also need to create a modify record macro to "find" an index value and extract the row number. I tried several variations (2 examples below) with no success, can anyone advise?

Eg 1.

Windows("Supplier Non-Conformance Database.xlsm").Activate
Cells.Find(What:="2015-16-46", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Eg 2.

xl0.Worksheets("SNCR Log").Range("B:B").Find(What:="2015-16-46",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Thanks in advance,

Dan

解决方案

Here is a start :(btw, the activation is really greedy in resources, so try to avoid it as much as possible!)

Application.ScreenUpdating=False

'DATABASE ACCESS
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Dim db_directory As String

xl0.Visible=False

db_directory = "R:\New Quality Management System\xls\Supplier Non-Conformance\Supplier Non-Conformance Database.xlsm"
Set xlw = xl0.Workbooks.Open(db_directory)

MsgBox xlw.Sheets(1).Cells.Find(What:="2015-16-46", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row


Application.ScreenUpdating=True

这篇关于在后台工作簿中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！