Two Questions: R1C1 and stop text number converting to data

[The Moose]

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.

 

[Harald Staff]

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

 

[~Barb~]

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

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

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.

 

[The Moose]

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

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

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.

 

[The Moose]

Ooh-ooh -- I've discovered that if I format the receiving spreadsheet
colum as "text" before I pull the internet spreadsheet that the numbers
that were coming across incorrectly as dates stay in the correct
numeric/text format.

I did a little further reading and supposely, the excel 'helpers'
attempt to put the data in the 'correct' format. For numbers, if the
numbers contain dashes, the software attempts to format the number as a
date. Workarounds are to put either a space or a single quote in front
of the number before entering it. Since I have no control over the
format of the internet spreadsheet AND they are formatting that one
problem column correctly as "text", I can't really tell them to do it
any differently.

Basically, I just need to remember to reformat that one particular
column before I pull the data.

Thanks.

Barb

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

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

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.

 

[The Moose]

I've discovered that if I use this procedure, rather than the pull
macro mentioned above, that I can get entire active range of the
spreadsheet pulled down:

1. Open Excel.
2. Design the document as you wish it to appear.
3. Select the cell where you want the information to begin.
4. Click Data | Get External Data | New Web Query.
5. In the first field ("Enter the address..."), enter the URL of the
internet spreadsheet.
6. In the second field ("Choose the part of the web page..."), select
the option button for "Entire Page" -- your option might be different;
this is the option that works for me.
7. In the third field ("Choose how much formatting..."), select the
option button for "None." Click OK.
8. In the "Returning External Data to Microsoft Excel" dialog box,
confirm the location where you want the information to start appearing.
Click "Properties." Maintain the selections for "Save query definition"
and "Enable background refresh," and select "Refresh data on file
open." Deselect "Adjust column width" and "Preserve cell formatting."
Finally, select the option for "Overwrite existing cells with new data,
clear unused cells." Click on OK.
In the "Returning External Data to Microsoft Excel" dialog box, confirm
the cell where you wish to begin inputting the information. Click OK.
9. Once the information has been imported, you'll need to delete just a
few cells containing unnecessary data.
10. Save the spreadsheet and you're good to go. The next time the file
is opened, the most current information will be placed where you
indicated.

When I use this procedure, I do not have a problem with the product
numbers coming across as dates, because this procedure does not pull
down any formatting from the internet spreadsheet.

Barb

Ooh-ooh -- I've discovered that if I format the receiving spreadsheet
colum as "text" before I pull the internet spreadsheet that the numbers
that were coming across incorrectly as dates stay in the correct
numeric/text format.

I did a little further reading and supposely, the excel 'helpers'
attempt to put the data in the 'correct' format. For numbers, if the
numbers contain dashes, the software attempts to format the number as a
date. Workarounds are to put either a space or a single quote in front
of the number before entering it. Since I have no control over the
format of the internet spreadsheet AND they are formatting that one
problem column correctly as "text", I can't really tell them to do it
any differently.

Basically, I just need to remember to reformat that one particular
column before I pull the data.

Thanks.

Barb

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

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 here:"
End Sub

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

HTH. best wishes Harald

"The Moose" <[email protected]> skrev i melding
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/6645and
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.