get data from other xls

[polilop]

I need to get data from one xls document (the data is in a txt box) into
another xls document.
How do i open one xls and look into it for the txt box from another?

 

[Mircea Pleteriu]

1. I suppose you have a reference to an Excel application object.
Using this reference you can open a workbook targeting the other xls file.

or

2. Create a new reference to an Excel application object and open a workbook
which targets the xls file.

 

[Damien McBain]

I need to get data from one xls document (the data is in a txt box) into
another xls document.
How do i open one xls and look into it for the txt box from another?

I do this by (in code) opening the other xls, copying what I want, pasting
values into the one I'm working with then closing the source file. There's
probably a more technically correct way but this works for me. Here's one I
think I posted here recently:

Sub GetDataFromFile()
Application.ScreenUpdating = False
Worksheets("Data").Range("A2:J2000").ClearContents
Worksheets("Data").Activate
Range("A1").Select

If Not CBool(Len(Dir("I:\Accounting\Daily Tonnes\DataFiles\" &
Range("Date") & " ALL.xls"))) Then
MsgBox "There is no Data File called:" & Chr(13) & Range("Date") & "
ALL.xls" & Chr(13) & "Check that you have typed the date correctly above," &
Chr(13) & "and that the file has been saved in the correct location.", ,
"Wanker Trap!"
Worksheets("Main").Select
Range("date").Select

Exit Sub

Else
If Not CBool(Len(Dir("I:\Accounting\Daily Tonnes\DataFiles\" &
Range("Date") & " HMC.xls"))) Then
MsgBox "There is no Data File called:" & Chr(13) & Range("Date") & "
HMC.xls" & Chr(13) & "Check that you have typed the date correctly above," &
Chr(13) & "and that the file has been saved in the correct location.", ,
"Wanker Trap!"
Worksheets("Main").Select
Range("date").Select

Exit Sub

Else


Workbooks.Open ("I:\Accounting\Daily Tonnes\DataFiles\" & Range("date") & "
ALL")
Workbooks(Workbooks.Count).Activate

ActiveSheet.UsedRange.Copy
Workbooks("Daily Tonnes Model.xls").Activate
Range("PasteSpot").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Workbooks(Workbooks.Count).Close
Workbooks("Daily Tonnes Model.xls").Activate

Workbooks.Open ("I:\Accounting\Daily Tonnes\DataFiles\" & Range("date") & "
HMC")
Workbooks(Workbooks.Count).Activate

ActiveSheet.UsedRange.Copy
Workbooks("Daily Tonnes Model.xls").Activate
Range("HMCPasteSpot").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Workbooks(Workbooks.Count).Close

Workbooks("Daily Tonnes Model.xls").Activate
Sheets("Main").Select
Range("Date").Select

Application.ScreenUpdating = True
MsgBox "Got it", , "Daily Tonnes"

End If
End If

End Sub