Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve data from external excel document
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve data from external excel document
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve data from external excel document
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 On Mar 9, 2:07 am, "Greg Glynn" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve data from external excel document
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I retrieve changes to an unsaved excel document | Excel Discussion (Misc queries) | |||
External data retrieve timescale | Links and Linking in Excel | |||
External data retrieve timescale | Excel Programming | |||
How do I retrieve changes to an unsaved excel document | New Users to Excel | |||
How can I retrieve an Excel document that I did not save? | Excel Discussion (Misc queries) |