Retrieve data from external excel document

[Khurram]

Hi all,
My aim is to retrieve a data value from an external Excel document
using VBA. I have an external document available at C:\src.xls. This
contains a cell with the text "PM Ckpt to CRF finish " and this text
is unique. I need the number value 4 rows down and 2 columns across
from that cell. This data can then be assigned to a long variable
"complete". I have no issues with hard coding the location of the
file src.xls. Can somone give me any direction on how this can be
done?

Many thanks in advance

Khurram

 

[Greg Glynn]

Khurram,

You need to (1) Open the external XLS, (2) Extract the value to a
variable (call it MyVal) then (3) close the external database.

Try this:

Sub GetData()
Workbooks.open Filename:="C:\src.xls"
MyVal = Workbooks("src.xls").Sheets("Sheets1").Range("B4").Value
Workbooks("src.xls").close SaveChanges:=False
End sub

 

[Khurram]

Thank you Greg, I will test this now. A quick question please. If I
wanted to retrieve the data from a file with a filename that ended
with words "....src data.xls". There would only be one file with such
a name but the begining of the filename changes on a monthly basis.
I'm guessing I would use a wildcard (* perhaps) in the filename
somewhere but am unaware of the syntax.

Thank you
Khurram

 

[Greg Glynn]

You might use the FileSearch Method. It will find ALL files that
match the criteria, which should be OK if there is just one.

With Application.FileSearch
'Standard FileSearch Stuff
.NewSearch
.LookIn = "c:\my folder"
.Filename = "*src data.XLS"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
'Find the name of the workbook without the directory info
WbFileName = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1,
200)
workbooks.Open .FoundFiles(i)

'Do your processing here ...

'then close the workbook
Workbooks(WbFileName).Close

Next i
End with