Solved: find data in a closed workbook?

[JimmyTheHand]

Hi

I want to find the cell that contains a certain string, in a closed workbook. I have the following parameters ready:
Path, Workbook name, Sheet name, Column index, String expression to look for.

How do I go about this?
I can retrieve any specific, known range (e.g. "A4") from a closed workbook, so I can loop through the cells until I find the string. But that's very slow and no better than opening the workbook and using Find method. I'd like something more elegant and real fast.

Any ideas?

Thx,
Jimmy

[Bob Phillips]

JTH,

How about using ADO?

[vba]

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim rng As Range

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sales$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveWorkbook.Worksheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS
Set rng = Cells.Find("Jan")
If Not rng Is Nothing Then
MsgBox rng.Address
End If
Set rng = Nothing
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub
[/vba]

[johnske]

Or maybe Application.FileSearch? (discontinued in 2007 tho). This example searches all workbooks in the ActiveWorkbook path for the string "GraphicsExporter.xla" - maybe modify it to do what you're after? (probably best to just open the book tho )

[vba]
Option Explicit

Sub FindText()

Dim N As Integer, StringFound As String

Const SearchForWord As String = "GraphicsExporter.xla"
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeAllFiles
.SearchSubFolders = False
.TextOrProperty = SearchForWord
.Execute

For N = 1 To .FoundFiles.Count
StringFound = StringFound & vbNewLine & .FoundFiles(N)
Next N

MsgBox StringFound

End With

End Sub
[/vba]

[JimmyTheHand]

Hi Guyz,

Thanks for the tips, both of you.

I'll give out some details, to clear the picture.
In a certain folder we collect report files of a gas chromatographic analysis. Each sample has one report file to store the analysis results in. My goal is to retrieve some parts of the results for each sample without opening the files. There are a few thousand samples (i.e. report files) to process.
E.g. the report contains this line:
"Density (15?C) = 0.750"
I'm looking for "Density (15?C)", and when found, want to put the value (0.750) into a data table, in the row of the respective sample.


John,
I couldn't use your code in solving this particular problem. You see, I knew that all my files contained the line with the density data, so a list of those files wouldn't have helped much. You have misunderstood me, which is partly my fault, and I'm sorry for not being specific enough.

Bob,
your code, on the other hand, was a great start. I've never tried ADO so far, and had to research the subject a bit. Using a WHERE condition in the SQL string I managed to retrieve the only one row I needed, so didn't have to copy the whole sheet into ActiveWorkbook. But it took me a while to figure out how to put that WHERE clause into the SQL string.

Anyway, my code works now, and it's 2.5 times faster than opening all report files and using Find method. Also, I don't mind the absence of a flashing Windows tray, which seems to be unavoidable when opening and closing files in rapid succession.
Thank you for steering me onto the right path.

Jimmy

[Bob Phillips]

Bob,
your code, on the other hand, was a great start. I've never tried ADO so far, and had to research the subject a bit. Using a WHERE condition in the SQL string I managed to retrieve the only one row I needed, so didn't have to copy the whole sheet into ActiveWorkbook. But it took me a while to figure out how to put that WHERE clause into the SQL string.

You should have said, we could have helped, some of us are old SQL jocks

[JimmyTheHand]

You should have said, we could have helped, some of us are old SQL jocks

Well, you might still help, actually.
I can put together an ordinary SQL SELECT if I know the field names. The problem was that I didn't know what the field names were in an Excel worksheet. I tried listing them and found that cell values in row #1 were taken as field names. However, these values can be anything, varying from workbook to workbook. So I used this code (variables declarations aren't included):

[vba]sSQL1 = "SELECT * FROM [A:A]"
oRS.Open sSQL1, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText 'Open recordset
FldName = oRS.fields(0).Name 'learn field name
oRS.Close 'Close recordset
sSQL2 = sSQL1 & " WHERE [" & FldName & "] LIKE 'Density%'" 'recreate SQL using WHERE and fieldname
oRS.Open sSQL2, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText[/vba] 'reopen recordset
Do you know of a way of learning field names without opening the recordset? This double-opening is awkward and, I suspect, halves the macro speed.


Also, the next step in my project is to retrieve some data from an Oracle database. I tried the same concept you showed with MS Jet provider.
[vba]
Set oRS = CreateObject("ADODB.Recordset")
sConnect = "Provider=MSDAORA;Data Source=servername; User ID=***; Password=***"
sSQL1 = "SELECT * FROM TABLENAME"
oRS.Open sSQL1, sConnect
[/vba] It fails at the Open method (last line), but the Oracle error message cannot be imported.
I don't know what's wrong, and there are many independent variables. For example, I don't even know what I should use as a server name. There are at least 3 aliases, plus a network name for the server. The server connection is properly configured for Oracle client. Is that good enough for Excel? Or do I have to do something with that "Microsoft ODBC administrator"? I'm so green at this, I don't even know how to ask a sensible question.

Jimmy

[Bob Phillips]

Well, you might still help, actually.
I can put together an ordinary SQL SELECT if I know the field names. The problem was that I didn't know what the field names were in an Excel worksheet. I tried listing them and found that cell values in row #1 were taken as field names. However, these values can be anything, varying from workbook to workbook. So I used this code (variables declarations aren't included):

[vba]sSQL1 = "SELECT * FROM [A:A]"
oRS.Open sSQL1, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText 'Open recordset
FldName = oRS.fields(0).Name 'learn field name
oRS.Close 'Close recordset
sSQL2 = sSQL1 & " WHERE [" & FldName & "] LIKE 'Density%'" 'recreate SQL using WHERE and fieldname
oRS.Open sSQL2, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText[/vba] 'reopen recordset
Do you know of a way of learning field names without opening the recordset? This double-opening is awkward and, I suspect, halves the macro speed.

Jimmy,

One alternative is to pull back all the data into a recordset and then filter the recordset.

Here is an example using my data, which pulls back the whole sheet, then filters it to just the row where column 1 is Bob. You are smart enough to be adapt this to your situation

[vba]

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Family$]"

Set oRS = CreateObject("ADODB.Recordset")

oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
oRS.Filter = oRS.fields(0).Name & " = 'Bob'"
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub
[/vba]

Also, the next step in my project is to retrieve some data from an Oracle database. I tried the same concept you showed with MS Jet provider.
[vba]
Set oRS = CreateObject("ADODB.Recordset")
sConnect = "Provider=MSDAORA;Data Source=servername; User ID=***; Password=***"
sSQL1 = "SELECT * FROM TABLENAME"
oRS.Open sSQL1, sConnect
[/vba] It fails at the Open method (last line), but the Oracle error message cannot be imported.
I don't know what's wrong, and there are many independent variables. For example, I don't even know what I should use as a server name. There are at least 3 aliases, plus a network name for the server. The server connection is properly configured for Oracle client. Is that good enough for Excel? Or do I have to do something with that "Microsoft ODBC administrator"? I'm so green at this, I don't even know how to ask a sensible question.

I see that you are using the Microsoft OLEDB provider. Are you sure that is what your company uses, not the Oracle one? The connection strings for these can be found here.

As to the server name,. contact your IT guys, specifically the DB admin, and ask them what is the servername used when connecting to your Oracle database. If they (he/she) doesn't know, you have a problem. There may be a way of finding out, but not being an Oracle guy, I am afraid I don't know it. There should be some utility to see the OLEDB provider, but that is outside my knoiwledge. Maybe the ODBC applet on the control panel has the server defined to it that will tell you.

[JimmyTheHand]

I've been able to apply the filter, this is a great method, indeed. Thanks. Gained another 30% macro speed

I'm still having problem with connecting to the Oracle Database. It was my understanding that you can use either Microsoft or Oracle provider, both should be able to get the job done. For the moment, Oracle provider is not installed on my comp, which is strange, because Oracle client is.

Or maybe Oracle provider is installed after all, but I don't see the Oracle driver listed in the Microsoft ODBC Administrator, and so I cannot create a Datasource. Then how do I use it?

Jimmy

[Bob Phillips]

It is quite possible that the MS provider is the only one. Personally I would install the Oracle DB provider, but that's just me.

As to other point, I can't help, I am not an Oracle bod.