Searching a range in Closed Workbook

T

TroyH

I have read, and read, and read, and I am still having problems

I have two workbooks, PA.xls and PA_DB.xl

I would like to press a button on PA.xls and do a search in PA_DB.xls within VBA

PA_DB.xls has only one sheet, Database. The Range is MRN_Data

If anyone can help me I would be forever grateful. I have spent way to much time on this one

Thanks
 
F

Frank Kabel

Hi
though this is possible I'd suggest you open the other workbook
(disable screenupdating to prevent showing this to the user), do your
search and close the workbook again. should be faster than accessing a
closed workbook
 
B

Bob Phillips

Troy,

here is one way

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range

sFile = "C:\myTest\volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

' Check to make sure we received data.
If Not oRS.EOF Then
Application.ScreenUpdating = False
Set oOrig = ActiveWorkbook.ActiveSheet
Worksheets.Add.Name = "_temp"
Set osh = ActiveSheet
osh.Range("A1").CopyFromRecordset oRS
On Error Resume Next
Set oCell = osh.Cells.Find("POOLE")
If oCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
oOrig.Activate
Application.DisplayAlerts = False
osh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
On Error GoTo 0
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ron de Bruin

Hi Bob

I have add a page with some examples last week to my site that use ADO.
http://www.rondebruin.nl/ado.htm

I like it this ADO stuff


--
Regards Ron de Bruin
http://www.rondebruin.nl


Bob Phillips said:
Troy,

here is one way

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range

sFile = "C:\myTest\volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

' Check to make sure we received data.
If Not oRS.EOF Then
Application.ScreenUpdating = False
Set oOrig = ActiveWorkbook.ActiveSheet
Worksheets.Add.Name = "_temp"
Set osh = ActiveSheet
osh.Range("A1").CopyFromRecordset oRS
On Error Resume Next
Set oCell = osh.Cells.Find("POOLE")
If oCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
oOrig.Activate
Application.DisplayAlerts = False
osh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
On Error GoTo 0
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

TroyH said:
I have read, and read, and read, and I am still having problems.

I have two workbooks, PA.xls and PA_DB.xls

I would like to press a button on PA.xls and do a search in PA_DB.xls within VBA.

PA_DB.xls has only one sheet, Database. The Range is MRN_Data.

If anyone can help me I would be forever grateful. I have spent way to much time on this one.

Thanks
Click to expand...
Click to expand...
 
B

Bob Phillips

Me too Ron,

That and FSO and APIs.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Ron de Bruin said:
Hi Bob

I have add a page with some examples last week to my site that use ADO.
http://www.rondebruin.nl/ado.htm

I like it this ADO stuff


--
Regards Ron de Bruin
http://www.rondebruin.nl


Troy,

here is one way

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range

sFile = "C:\myTest\volker1.xls"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

' Check to make sure we received data.
If Not oRS.EOF Then
Application.ScreenUpdating = False
Set oOrig = ActiveWorkbook.ActiveSheet
Worksheets.Add.Name = "_temp"
Set osh = ActiveSheet
osh.Range("A1").CopyFromRecordset oRS
On Error Resume Next
Set oCell = osh.Cells.Find("POOLE")
If oCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
oOrig.Activate
Application.DisplayAlerts = False
osh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
On Error GoTo 0
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

TroyH said:
I have read, and read, and read, and I am still having problems.

I have two workbooks, PA.xls and PA_DB.xls

I would like to press a button on PA.xls and do a search in PA_DB.xls within VBA.

PA_DB.xls has only one sheet, Database. The Range is MRN_Data.

If anyone can help me I would be forever grateful. I have spent way to much time on this one.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
T

TroyH

Before I forget. Thanks to everyone for helping out. It led me in the right direction.
 
Top