Get data from closed file

[Kashyap]

Hi, as of now I am using formulas to get data from closed files. I was
looking if I can get data from a closed file with that help of macro?

Source file R3:R15
Destination file C3:O3 (Transpose)

 

[FSt1]

hi
see this site
http://www.rondebruin.nl/ado.htm

regards
FSt1

 

[Kashyap]

but nothing is mentioned regarding Transpose there..

 

[FSt1]

hi
after bringing in the data with ADO, you could then transpose it by adding a
line of code at the end.

Range("C3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

regards
FSt1

 

[Kashyap]

But can't we get it transposed while getting the same?

 

[Tim Williams]

Get what data from what file, and how ?

If you mean from a closed Excel file (though you don't say...) it would help
to specify the method you're currently using.

Tim

 

[gpmichal]

I use this line of code to access other Excel Workbooks. You can acutally
use similar code to access any file type:

Workbooks.Open fileName:="C:\yourFileName.xls"

Once it's opened, it will be activated and you can manipulate it anyway you
want to other lines of VBA code.

GP Michal

 

[Kashyap]

I have the below code.. But the range is fixed in the below case.. How to
make it dynamic?

i.e: if value in peter.xls Sheets("may") A2:AD2 matches value in current
sheet A2 then

it should copy values from row3 to row 21 from peter.xls Sheets("may") to
current sheet D1

Sub GetData_Example3()
GetData "D:\SavedFiles\CK\ZSL\May\\peter.xls", "May", _
"E3:E21", Sheets("may").Range("D1"), False, False

End Sub