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)

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

regards
FSt1

"Kashyap" wrote:

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)

but nothing is mentioned regarding Transpose there..

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" wrote:

but nothing is mentioned regarding Transpose there..

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

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

"Kashyap" wrote in message
...
But can't we get it transposed while getting the same?

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

"FSt1" wrote:

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

regards
FSt1

"Kashyap" wrote:

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)

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