polilop said:
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