Thanks, Harald.

(1) Thanks for the macro. I couldn't get it to work. When I start to
pull the data, I am in the local spreadsheet. The macro sees 'active'
as being the local spreadsheet rather than the remote spreadsheet. I
couldn't figure out where to position the macro to respond to the
remote spreadsheet. The pull macro is in #3 below.


(2) The product numbers that are coming across as dates are all
formatted as text. This is an example:


1-7635 -- gets converted to 1/01/7635 which displays as Jan-35


Apparently what's happening is, it's taking any data in that column
(which are all formatted as text and in this format):


yy-xxxx (yy is any number 1-12 and xxxx is any combination of
numbers which gets converted to a date using only the last two xx)


(3) The pull macro is:


Sub LIVEXXXXX()
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='http://www.xxxx.com/xxxx/[xxxx.xls]xxxx'!R2C1:R2000C10"
Sheets("Sheet2").Range("a2:j2000").Formula = "=web"
MyWait 1
Sheets("Sheet1").Range("k2:t2000").Value =
Sheets("Sheet2").Range("a2:j2000").Value
Sheets("Sheet2").Range("a2:j2000").Clear
ActiveWorkbook.Names("web").Delete
End Sub
Sub MyWait(PauseSeg As Double)
' Orlando Magalhães Filho
Dim Start
Start = Timer
Do While Timer < Start + PauseSeg
DoEvents
Loop
End Sub


Thanks.


Barb


Harald Staff wrote:
Hi Barb

You can have Excel detect the data range herself with CurrentRegion, like
this:

Sub test()
Dim PullRange As Range
Set PullRange = Cells(1, 1).CurrentRegion
'code here, instead of
MsgBox PullRange.Address(True, True, xlR1C1), , "Data is he"
End Sub

The date conversion is done by helpful Excel cells. Would need to see the
Pull code and the nature the data to suggest a workaround.

HTH. best wishes Harald

"The Moose" skrev i melding
oups.com...
Two questions:

(1) I am pulling an on-line excel spreadsheet into a local spreadsheet
by using a macro that contains R1C1 reference. Right now, I'm
specifying R1C1:R3000C10 as the range to pull in. This on-line
spreadsheet changes daily -- additions/deletions ... so the number of
columns is always the same, but, the number of rows varies. I'd like
the macro to pull in all available data rather than specifying
R1C1:R3000C10. How can I reference that in the macro??

The line in the macro that's specifying where to get the info is:
ActiveWorkbook.Names.Add Name:="web", RefersToR1C1:= _
"='URL[name of spreadsheet file.xls]products'!R1C1:R3000C10"

(2) When I pull in the info, some of the product numbers are converting
to dates. It seems to be only the ones that 'resemble' dates. For
example, product number 1-6645 is entered into the cell as 1/1/6645 and
displays as Jan-45. The cell in the online spreadsheet is formatted as
'text'. If I edit the cell in my own spreadsheet and put a
single-quote and delete the forward-slashes and delete the 'day' and
enter the dash in the appropriate place, it fixes it. Needless to say,
I don't want to waste my life-force doing that :GRIN: I have no
control over how the original data is formatted. How can I FORCE the
data to enter correctly once I import it??

Thanks.

Barb

PS: I'm using Excel 2000 on top of Windows ME.