Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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 The Moose wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Two Questions: R1C1 and stop text number converting to data
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 The Moose wrote: 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 The Moose wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|