RE: Foxpro and Excel question
Did you look at that link, Michael? In Office 2010, 1024 characters will display in the cell itself but 32K will display in the formula bar. I'm sure there are similar numbers in earlier versions of Office. As to why you would want to store and manipulate 32K of text data in a cell in Excel, I dunno... :) -- rk -Original Message- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Michael Madigan Sent: Wednesday, January 25, 2012 3:26 PM To: profoxt...@leafe.com Subject: Re: Foxpro and Excel question I'm confused, however. You can display 1024 but can store much much more. But what is the point of storing it if you can't display it? ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D4A7D5B1F@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Foxpro and Excel question
I'm confused, however. You can display 1024 but can store much much more. But what is the point of storing it if you can't display it? From: Richard Kaye To: "profox@leafe.com" Sent: Wednesday, January 25, 2012 2:39 PM Subject: RE: Foxpro and Excel question I don't usually print this kind of stuff from Excel so I'd have to try it out. The point I was trying to make is that you can store significantly more than 255 characters in a cell in Excel. -- rk -Original Message- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Michael Madigan Sent: Wednesday, January 25, 2012 2:20 PM To: profoxt...@leafe.com Subject: Re: Foxpro and Excel question There's a display limit, but can you print it? From: Richard Kaye To: "profox@leafe.com" Sent: Wednesday, January 25, 2012 2:12 PM Subject: RE: Foxpro and Excel question Hmm... Not exactly. Don't confuse display with capacity. See the link below. (And it is different depending on what version of Excel you are using.) I dump large memo files into Excel cells on a regular basis. Finally, you do need to do this via automation as opposed to using xBase commands if you want to avoid data truncation. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx -- rk ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D4A7D5B01@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1327523141.50706.yahoomail...@web125603.mail.ne1.yahoo.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Foxpro and Excel question
I don't usually print this kind of stuff from Excel so I'd have to try it out. The point I was trying to make is that you can store significantly more than 255 characters in a cell in Excel. -- rk -Original Message- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Michael Madigan Sent: Wednesday, January 25, 2012 2:20 PM To: profoxt...@leafe.com Subject: Re: Foxpro and Excel question There's a display limit, but can you print it? From: Richard Kaye To: "profox@leafe.com" Sent: Wednesday, January 25, 2012 2:12 PM Subject: RE: Foxpro and Excel question Hmm... Not exactly. Don't confuse display with capacity. See the link below. (And it is different depending on what version of Excel you are using.) I dump large memo files into Excel cells on a regular basis. Finally, you do need to do this via automation as opposed to using xBase commands if you want to avoid data truncation. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx -- rk ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D4A7D5B01@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Foxpro and Excel question
There's a display limit, but can you print it? From: Richard Kaye To: "profox@leafe.com" Sent: Wednesday, January 25, 2012 2:12 PM Subject: RE: Foxpro and Excel question Hmm... Not exactly. Don't confuse display with capacity. See the link below. (And it is different depending on what version of Excel you are using.) I dump large memo files into Excel cells on a regular basis. Finally, you do need to do this via automation as opposed to using xBase commands if you want to avoid data truncation. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx -- rk -Original Message- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Michael Madigan Sent: Wednesday, January 25, 2012 1:58 PM To: profoxt...@leafe.com Subject: Re: Foxpro and Excel question I think it may be up to 1024 now, but you're right. We wound up scrapping this because our memo fields are bigger than that. i wound up just creating an Excel sheet with a line for each memo line. From: Richard A. Mertl To: profox@leafe.com; profoxt...@leafe.com Sent: Wednesday, January 25, 2012 1:05 PM Subject: RE: Foxpro and Excel question Keep in mind that the character limit in Excel is 255. You might end up loosing data. Richard A. Mertl, C.A. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D4A7D5AF7@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1327519198.60573.yahoomail...@web125601.mail.ne1.yahoo.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Foxpro and Excel question
Hmm... Not exactly. Don't confuse display with capacity. See the link below. (And it is different depending on what version of Excel you are using.) I dump large memo files into Excel cells on a regular basis. Finally, you do need to do this via automation as opposed to using xBase commands if you want to avoid data truncation. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx -- rk -Original Message- From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Michael Madigan Sent: Wednesday, January 25, 2012 1:58 PM To: profoxt...@leafe.com Subject: Re: Foxpro and Excel question I think it may be up to 1024 now, but you're right. We wound up scrapping this because our memo fields are bigger than that. i wound up just creating an Excel sheet with a line for each memo line. From: Richard A. Mertl To: profox@leafe.com; profoxt...@leafe.com Sent: Wednesday, January 25, 2012 1:05 PM Subject: RE: Foxpro and Excel question Keep in mind that the character limit in Excel is 255. You might end up loosing data. Richard A. Mertl, C.A. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D4A7D5AF7@ACKBWDDQH1.artfact.local ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Foxpro and Excel question
I think it may be up to 1024 now, but you're right. We wound up scrapping this
because our memo fields are bigger than that. i wound up just creating an
Excel sheet with a line for each memo line.
From: Richard A. Mertl
To: profox@leafe.com; profoxt...@leafe.com
Sent: Wednesday, January 25, 2012 1:05 PM
Subject: RE: Foxpro and Excel question
Keep in mind that the character limit in Excel is 255. You might end up
loosing data.
Richard A. Mertl, C.A.
T 514 885 4257
F 514 481 6196
E rame...@richardamertl.ca
W www.richardamertl.ca
Member of L'Ordre des comptable agrees du Quebec
Member of The Ontario Institute of Chartered Accountants
Member of The Association of Certified Fraud Examiners
Member of The Canadian Tax Foundation
Certfied Quickbooks ProAdvisor
-Original Message-
From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On
Behalf Of Michael Madigan
Sent: January 24, 2012 12:51 AM
To: profoxt...@leafe.com
Subject: Re: Foxpro and Excel question
Thanks Fred.
From: Fred Taylor
To: ProFox Email List
Sent: Monday, January 23, 2012 8:15 PM
Subject: Re: Foxpro and Excel question
You need to set the VerticalAlignment for all cells or even columns to be:
#DEFINE xlTop -4160
.VerticalAlignment = xlTop
Fred
On Mon, Jan 23, 2012 at 6:06 PM, Michael Madigan
wrote:
> I got this code from the internet to extract memo fields from foxpro into
> excel
> cTabla = GETFILE("dbf")
> oExcel = Createobject("Excel.Application")
> oExcel.WorkBooks.Add
> sele &cTabla
> Set Talk Off
> nNumRegExp = Reccount() && Total records
> For i = 1 To nNumRegExp
> WAIT WINDOW "Exporting data: "+STR(i) nowait
> IF i =1 && fields' titles
> FOR J = 1 To Fcount()
> oExcel.ActiveSheet.cells(i,J).Value= Field(J)
> NEXT
> ENDIF
> For J = 1 To Fcount() && Content
> oExcel.ActiveSheet.cells(i+1,J).Value =
> IIF(TYPE(Field(J))=="G",CHR(34)+FIELD(J)+CHR(34),IIF(
EMPTY(Eval(Field(J)))
> ,"", EVALUATE(FIELD(J)) ) )
> Next
> Skip
> Next
> lnombre = "C:\Result.xls"
> ERASE (lnombre)
> oExcel.ActiveWorkBook.SaveAs(lnombre)
> oExcel.ActiveWorkBook.Close
> oExcel.Quit
>
>
> The problem is that if I turn on word wrap, the cells to the left appear
> at the bottom of the cell instead of the top. How do i change it so that
> the one-line cells stay at the top of the cell when the memo cells are
> using word wrap and may be 20 or 30 lines long?
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---[excessive quoting removed by server]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/1327517877.90741.yahoomail...@web125605.mail.ne1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
RE: Foxpro and Excel question
Keep in mind that the character limit in Excel is 255. You might end up
loosing data.
Richard A. Mertl, C.A.
T 514 885 4257
F 514 481 6196
E rame...@richardamertl.ca
W www.richardamertl.ca
Member of L'Ordre des comptable agrees du Quebec
Member of The Ontario Institute of Chartered Accountants
Member of The Association of Certified Fraud Examiners
Member of The Canadian Tax Foundation
Certfied Quickbooks ProAdvisor
-Original Message-
From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On
Behalf Of Michael Madigan
Sent: January 24, 2012 12:51 AM
To: profoxt...@leafe.com
Subject: Re: Foxpro and Excel question
Thanks Fred.
From: Fred Taylor
To: ProFox Email List
Sent: Monday, January 23, 2012 8:15 PM
Subject: Re: Foxpro and Excel question
You need to set the VerticalAlignment for all cells or even columns to be:
#DEFINE xlTop -4160
.VerticalAlignment = xlTop
Fred
On Mon, Jan 23, 2012 at 6:06 PM, Michael Madigan
wrote:
> I got this code from the internet to extract memo fields from foxpro into
> excel
> cTabla = GETFILE("dbf")
> oExcel = Createobject("Excel.Application")
> oExcel.WorkBooks.Add
> sele &cTabla
> Set Talk Off
> nNumRegExp = Reccount() && Total records
> For i = 1 To nNumRegExp
> WAIT WINDOW "Exporting data: "+STR(i) nowait
> IF i =1 && fields' titles
> FOR J = 1 To Fcount()
> oExcel.ActiveSheet.cells(i,J).Value= Field(J)
> NEXT
> ENDIF
> For J = 1 To Fcount() && Content
> oExcel.ActiveSheet.cells(i+1,J).Value =
> IIF(TYPE(Field(J))=="G",CHR(34)+FIELD(J)+CHR(34),IIF(
EMPTY(Eval(Field(J)))
> ,"", EVALUATE(FIELD(J)) ) )
> Next
> Skip
> Next
> lnombre = "C:\Result.xls"
> ERASE (lnombre)
> oExcel.ActiveWorkBook.SaveAs(lnombre)
> oExcel.ActiveWorkBook.Close
> oExcel.Quit
>
>
> The problem is that if I turn on word wrap, the cells to the left appear
> at the bottom of the cell instead of the top. How do i change it so that
> the one-line cells stay at the top of the cell when the memo cells are
> using word wrap and may be 20 or 30 lines long?
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/blu0-smtp393c6c24dc761c2b801368c0...@phx.gbl
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Foxpro and Excel question
Thanks Fred.
From: Fred Taylor
To: ProFox Email List
Sent: Monday, January 23, 2012 8:15 PM
Subject: Re: Foxpro and Excel question
You need to set the VerticalAlignment for all cells or even columns to be:
#DEFINE xlTop -4160
.VerticalAlignment = xlTop
Fred
On Mon, Jan 23, 2012 at 6:06 PM, Michael Madigan wrote:
> I got this code from the internet to extract memo fields from foxpro into
> excel
> cTabla = GETFILE("dbf")
> oExcel = Createobject("Excel.Application")
> oExcel.WorkBooks.Add
> sele &cTabla
> Set Talk Off
> nNumRegExp = Reccount() && Total records
> For i = 1 To nNumRegExp
> WAIT WINDOW "Exporting data: "+STR(i) nowait
> IF i =1 && fields' titles
> FOR J = 1 To Fcount()
> oExcel.ActiveSheet.cells(i,J).Value= Field(J)
> NEXT
> ENDIF
> For J = 1 To Fcount() && Content
> oExcel.ActiveSheet.cells(i+1,J).Value =
> IIF(TYPE(Field(J))=="G",CHR(34)+FIELD(J)+CHR(34),IIF( EMPTY(Eval(Field(J)))
> ,"", EVALUATE(FIELD(J)) ) )
> Next
> Skip
> Next
> lnombre = "C:\Result.xls"
> ERASE (lnombre)
> oExcel.ActiveWorkBook.SaveAs(lnombre)
> oExcel.ActiveWorkBook.Close
> oExcel.Quit
>
>
> The problem is that if I turn on word wrap, the cells to the left appear
> at the bottom of the cell instead of the top. How do i change it so that
> the one-line cells stay at the top of the cell when the memo cells are
> using word wrap and may be 20 or 30 lines long?
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/CAJCBksqDPDxG0QqHRoQek8vNW=wakdctdvos28dwtpwhv8w...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/1327384262.3628.yahoomail...@web125606.mail.ne1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Foxpro and Excel question
You need to set the VerticalAlignment for all cells or even columns to be:
#DEFINE xlTop -4160
.VerticalAlignment = xlTop
Fred
On Mon, Jan 23, 2012 at 6:06 PM, Michael Madigan wrote:
> I got this code from the internet to extract memo fields from foxpro into
> excel
> cTabla = GETFILE("dbf")
> oExcel = Createobject("Excel.Application")
> oExcel.WorkBooks.Add
> sele &cTabla
> Set Talk Off
> nNumRegExp = Reccount() && Total records
> For i = 1 To nNumRegExp
> WAIT WINDOW "Exporting data: "+STR(i) nowait
> IF i =1 && fields' titles
>FOR J = 1 To Fcount()
>oExcel.ActiveSheet.cells(i,J).Value= Field(J)
>NEXT
> ENDIF
>For J = 1 To Fcount() && Content
>oExcel.ActiveSheet.cells(i+1,J).Value =
> IIF(TYPE(Field(J))=="G",CHR(34)+FIELD(J)+CHR(34),IIF( EMPTY(Eval(Field(J)))
> ,"", EVALUATE(FIELD(J)) ) )
>Next
>Skip
> Next
> lnombre = "C:\Result.xls"
> ERASE (lnombre)
> oExcel.ActiveWorkBook.SaveAs(lnombre)
> oExcel.ActiveWorkBook.Close
> oExcel.Quit
>
>
> The problem is that if I turn on word wrap, the cells to the left appear
> at the bottom of the cell instead of the top. How do i change it so that
> the one-line cells stay at the top of the cell when the memo cells are
> using word wrap and may be 20 or 30 lines long?
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/CAJCBksqDPDxG0QqHRoQek8vNW=wakdctdvos28dwtpwhv8w...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Foxpro and Excel question
I got this code from the internet to extract memo fields from foxpro into excel
cTabla = GETFILE("dbf")
oExcel = Createobject("Excel.Application")
oExcel.WorkBooks.Add
sele &cTabla
Set Talk Off
nNumRegExp = Reccount() && Total records
For i = 1 To nNumRegExp
WAIT WINDOW "Exporting data: "+STR(i) nowait
IF i =1 && fields' titles
FOR J = 1 To Fcount()
oExcel.ActiveSheet.cells(i,J).Value= Field(J)
NEXT
ENDIF
For J = 1 To Fcount() && Content
oExcel.ActiveSheet.cells(i+1,J).Value =
IIF(TYPE(Field(J))=="G",CHR(34)+FIELD(J)+CHR(34),IIF( EMPTY(Eval(Field(J)))
,"", EVALUATE(FIELD(J)) ) )
Next
Skip
Next
lnombre = "C:\Result.xls"
ERASE (lnombre)
oExcel.ActiveWorkBook.SaveAs(lnombre)
oExcel.ActiveWorkBook.Close
oExcel.Quit
The problem is that if I turn on word wrap, the cells to the left appear at the
bottom of the cell instead of the top. How do i change it so that the
one-line cells stay at the top of the cell when the memo cells are using word
wrap and may be 20 or 30 lines long?
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/1327367162.82851.yahoomail...@web125602.mail.ne1.yahoo.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
How about using the right function, such as: =RIGHT(E18,3) + 2 Bryant Minard Medford Software House 25 Ark Rd Medford, NJ 08055 Tel. (609) 654-5443Fax. (609) 654-5686 sa...@medfordsh.com > > > > > > > Paul Hill wrote: > > > > On Tue, Jun 8, 2010 at 7:47 PM, Jeff Johnson wrote: > > > I have a spreadsheet that has numbers entered in cells as '325. I want > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > j...@san-dc.com > > > www.san-dc.com > > > > > --- StripMime Report -- processed MIME parts --- > text/html (html body -- converted) > --- > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/911acd39-1064-4b42-a4dd-da3b3895f...@me.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: [NF] Excel Question
try =VALUE(Cell with '325). IE Cell A1 = '325, Cell A2 =VALUE(A1), THE RESULT A number 325 is in cell A2 Jim -Original Message- From: profox-boun...@leafe.com [mailto:profox-boun...@leafe.com] On Behalf Of Jeff Johnson Sent: Tuesday, June 08, 2010 5:12 PM To: profox@leafe.com Subject: Re: [NF] Excel Question Jeff Johnson wrote: > http://www.ambience.sk/old/open-office-text-number-format-conversion > > It uses search and replace with regular expressions .* and &. > > Not bad. > > Yikes! I can do this in Open Office but not in Excel. I am giving instructions to a client. They don't have Open Office. -- Jeff --- Jeff Johnson j...@san-dc.com (623) 582-0323 www.san-dc.com [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/01cb07ca$6931a300$3b94e9...@net ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
On 08/06/10 16:05, Paul Hill wrote: > On Tue, Jun 8, 2010 at 7:47 PM, Jeff Johnson wrote: > >> I have a spreadsheet that has numbers entered in cells as '325. I want >> to convert them to 325 so I can operate on them. I can't figure out how >> to do this. If I convert the cell format to a number it doesn't work >> because it is text because of the single quote. >> > In the next row enter a formula like "=A1+0" > > e.g. > > A B > 1 '325 =A1+0 > 2 '100 =A2+0 > 3 '201 =A3+0 > > You can now work on the B column. > > Or copy values from B to A, delete B and then you've repaired your worksheet. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0ee6fe.9080...@gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
Jeff Johnson wrote: > http://www.ambience.sk/old/open-office-text-number-format-conversion > > It uses search and replace with regular expressions .* and &. > > Not bad. > > Yikes! I can do this in Open Office but not in Excel. I am giving instructions to a client. They don't have Open Office. -- Jeff --- Jeff Johnson j...@san-dc.com (623) 582-0323 www.san-dc.com ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0eb211.6070...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
Stephen Russell wrote: On Tue, Jun 8, 2010 at 2:43 PM, Jeff Johnson wrote: http://www.ambience.sk/old/open-office-text-number-format-conversion - -- j...@san-dc.com www.san-dc.com --- StripMime Report -- processed MIME parts --- text/html (html body -- converted) --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0eab30.8050...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
On Tue, Jun 8, 2010 at 2:43 PM, Jeff Johnson wrote: > http://www.ambience.sk/old/open-office-text-number-format-conversion > > It uses search and replace with regular expressions .* and &. - In OO can you use the spreadsheet to access outside data like ODBC source? I was trying to do that and could not find the hook to get access to the data. Or is it not there and that is the reason I couldn't find it? -- Stephen Russell Sr. Production Systems Programmer CIMSgts 901.246-0159 cell ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/aanlktinmy3-t7vhbriuxcg5evgxu_cbhnx4odafar...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
http://www.ambience.sk/old/open-office-text-number-format-conversion It uses search and replace with regular expressions .* and &. Not bad. -- Jeff --- Jeff Johnson j...@san-dc.com (623) 582-0323 www.san-dc.com ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0e9d67.5090...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
Paul Hill wrote: On Tue, Jun 8, 2010 at 7:47 PM, Jeff Johnson wrote: I have a spreadsheet that has numbers entered in cells as '325. I want -- j...@san-dc.com www.san-dc.com --- StripMime Report -- processed MIME parts --- text/html (html body -- converted) --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0e9523.2090...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
On Tue, Jun 8, 2010 at 7:47 PM, Jeff Johnson wrote: > I have a spreadsheet that has numbers entered in cells as '325. I want > to convert them to 325 so I can operate on them. I can't figure out how > to do this. If I convert the cell format to a number it doesn't work > because it is text because of the single quote. In the next row enter a formula like "=A1+0" e.g. A B 1 '325 =A1+0 2 '100 =A2+0 3 '201 =A3+0 You can now work on the B column. -- Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/aanlktin0f98lbauqnf-b8rjhlzm5tdhh1zqnjqfa6...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
Jeff Johnson wrote: > > > > > Jarvis, Matthew wrote: > > > > > -Original Message- > profoxtech-boun...@leafe.com > > > [mailto:profoxtech-boun...@leafe.com] > > > On Behalf Of Jeff Johnson > > profoxt...@leafe.com > > > > > > want > > > to convert them to 325 so I can operate on them. I can't figure out > > > how > > > to do this. If I convert the cell format to a number it doesn't work > > > Excel 2003 does the same thing. -- Jeff --- Jeff Johnson j...@san-dc.com (623) 582-0323 www.san-dc.com ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0e9480.6040...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Excel Question
Jarvis, Matthew wrote: -Original Message- profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On Behalf Of Jeff Johnson profoxt...@leafe.com want to convert them to 325 so I can operate on them. I can't figure out how to do this. If I convert the cell format to a number it doesn't work ProFox@leafe.com http://leafe.com/mailman/listinfo/profox http://leafe.com/mailman/listinfo/profoxtech http://leafe.com/archives/search/profox http://leafe.com/archives/byMID/profox/69f310c05dd83c48a84ba3769ce1ecf8034ad...@tntriexevs02.triadhospitals.net http://leafe.com/reportAbuse/69f310c05dd83c48a84ba3769ce1ecf8034ad...@tntriexevs02.triadhospitals.net -- j...@san-dc.com www.san-dc.com --- StripMime Report -- processed MIME parts --- text/html (html body -- converted) --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0e9288.8090...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: [NF] Excel Question
> -Original Message- > From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] > On Behalf Of Jeff Johnson > Sent: Tuesday, June 08, 2010 11:48 AM > To: profoxt...@leafe.com > Subject: [NF] Excel Question > > I have a spreadsheet that has numbers entered in cells as '325. I want > to convert them to 325 so I can operate on them. I can't figure out how > to do this. If I convert the cell format to a number it doesn't work > because it is text because of the single quote. > > TIA > > -- > Jeff What version of Excel are you using? I've got 2003 and it seems to do math on the cells (summation, division, etc.) just fine... Maybe it's a setting somewhere in Options rather than a formatting thing? Thanks, Matthew Jarvis || Business Systems Analyst IT Department McKenzie-Willamette Medical Center 1460 G Street, Springfield, OR 97477 || Ph: 541-744-6092 || Fax: 541-744-6145 -- Disclaimer: This electronic message may contain information that is Proprietary, Confidential, or legally privileged or protected. It is intended only for the use of the individual(s) and entity named in the message. If you are not an intended recipient of this message, please notify the sender immediately and delete the material from your computer. Do not deliver, distribute or copy this message and do not disclose its contents or take any action in reliance on the information it contains. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/69f310c05dd83c48a84ba3769ce1ecf8034ad...@tntriexevs02.triadhospitals.net ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
[NF] Excel Question
I have a spreadsheet that has numbers entered in cells as '325. I want to convert them to 325 so I can operate on them. I can't figure out how to do this. If I convert the cell format to a number it doesn't work because it is text because of the single quote. TIA -- Jeff --- Jeff Johnson j...@san-dc.com (623) 582-0323 www.san-dc.com ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/4c0e9052.3060...@san-dc.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Francis (net) wrote: > Ricardo, > > Here is some code I use to bypass the 16k limitation. Hope this > helps. > > * > ** > > * > -- > * Program.: ISEXCEL.PRG > * Version.: 1.0 > * Author..: Francis I. Coppage, Jr. Thanks Francis. I'll look at it as soon as I'm out of bed (ill, not sleeping). Cheers. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Ricardo,
Here is some code I use to bypass the 16k limitation. Hope this
helps.
*
**
*
--
* Program.: ISEXCEL.PRG
* Version.: 1.0
* Author..: Francis I. Coppage, Jr.
* Date: May 4, 2001
* Notice..: Copyright (c) 2001, All Rights Reserved.
* Compiler: Visual FoxPro 06.00.8961.00 for Windows
* Abstract: Simply Opens a dbf into excel and saves it as an
active workbook,
* : thus bypassing the limitation of VFP's 16k
records.
* Changes.: 21MAY01: Added autoformating.
* Notes...:
*
--
#INCLUDE registry.h
LPARAMETERS tcInFile, tcOutFile, tnRows, tnColumns
IF !oApp.IsExcel THEN
RETURN .F.
ENDIF
LOCAL llAutoFormat, lnRows, lcColumns
llAutoFormat = .T.
IF VARTYPE(tnRows) != "N" THEN
llAutoFormat = .F.
ELSE
*$* FIC: Actual Rows in Excel will include the header
information
lnRows = tnRows + 1
ENDIF
IF VARTYPE(tnColumns) != "N" THEN
llAutoFormat = .F.
ELSE
*$* FIC: Ok, the Columns in Excel are letters, and if that
ain't
*$* bad enough, we have to see if there are more than 26
columns ... argh!
lcColumns = GetColumn( tnColumns )
ENDIF
*!* Open up EXCEL ... We'll do the rest of our work there
OleApp = CREATEOBJECT("Excel.Application")
tcInFile = SYS(5) + CURDIR() + FORCEEXT( tcInFile, "DBF" )
COPY TO (tcInFile) TYPE FOX2X
WITH OleApp
*!* dev stuff, So we could see it open
.Visible = .T.
.UserControl = .T.
*!* Loads our new worksheet into EXCEL
.Workbooks.Open( tcInFile )
.Range( "A1:" + lcColumns + ALLTRIM(STR(lnRows)) ).Select
.Selection.Columns.AutoFit
.Selection.AutoFilter
lcXFileName = tcOutFile
IF FILE( lcXFileName ) THEN
ERASE (lcXFileName)
ENDIF
*!* After all this work ... ... we save it.
.ActiveWorkbook.SaveAs( lcXFileName )
.ActiveWorkbook.Saved = .T.
*!* Quit EXCEL
*.QUIT
ENDWITH
*!* Release the pointer from memory.
OleApp = NULL
ERASE (tcInFile)
RETURN
ENDPROC
*
--
* Function: GetColumn
* Author..: Francis I. Coppage, Jr.
* Date: 30 November 2001, Friday
* Called by...:
*
* Abstract:
*
* Returns.:
*
* Parameters..:
*
* Notes...:
*
--
FUNCTION GetColumn( tnColumns )
LOCAL lcColumn, lcColumn2, lnBase, lnPower
DO CASE
CASE tnColumns <= 26
*$* FIC: 'A' = 65
lcColumn = CHR( 64 + tnColumns )
OTHERWISE
*$* FIC: We know that we are going to at least have an 'A'
prefix
*$* i.e. there are greater than 26 columns
lnBase = INT(tnColumns/26)
*$* FIC: The next 26 are the second character.
lnPower = MOD(tnColumns,26)
*$* FIC: Get them!
lcColumn = CHR( 64 + lnBase )
lcColumn2 = CHR( 64 + lnPower )
*$* FIC: Build them!
lcColumn = lcColumn + lcColumn2
ENDCASE
RETURN lcColumn
ENDFUNC
*
***
Best,
Francis I. Coppage, Jr.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Ricardo Araoz
Sent: Wednesday, August 15, 2007 6:39 PM
To: [EMAIL PROTECTED]
Subject: Excel question
Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop
slacking)
mails to be read, so I hope this is not yet a subject.
My reports generate Excel files, if the report is too long (more
than
64000 lines) I use different pages in the same file. I have two
ways to
do it, either I export up to say 15000 lines with a copy to
command to a
temporary excel file, then open it (as an excel object), copy
it's
contents to the clipboard (through excel command) and then copy
the
clipboard to a new page in my multi page excel file. The pro of
this
method is speed, the con is that if the customer uses the
clipboard he
interferes with the report.
The other way is to insert the data cell by cell, but if the file
is too
large it gets slow.
Does any of you know of some other way to optimize this?
TIA
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance:
http://leafe.com/mailman/listinfo/profox
OT-free version of this list:
http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the
opinions of the author, and do not constitute legal or medical
advice. Th
Re: Excel question
Nicholas Geti wrote: > Holy Cow is an old expression long before Batman used it. > > My time is precious. I am getting old and have so much to do before I get > feeble and check out. > Ok. I'll try not to do it again. > > - Original Message - > From: "Ricardo Aráoz" <[EMAIL PROTECTED]> > To: "ProFox Email List" > Sent: Friday, August 24, 2007 1:05 PM > Subject: Re: Excel question > > >> Nicholas Geti wrote: >>> Ricardo, >>> >>> Holy Cow! You repeated the entire message string to say Thank You. Why >>> did >>> you waste our time? >>> >> I'm sorry Nick, didn't imagine you would take the time to read the whole >> message once again. My fault, I humbly apologize for making you waste so >> much of your precious time. >> >> >> P:S: it's 'Holy Cow, Batman' (were you being ironic over OT posts in a >> list thread?). >> >> ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Holy Cow is an old expression long before Batman used it. My time is precious. I am getting old and have so much to do before I get feeble and check out. - Original Message - From: "Ricardo Aráoz" <[EMAIL PROTECTED]> To: "ProFox Email List" Sent: Friday, August 24, 2007 1:05 PM Subject: Re: Excel question > Nicholas Geti wrote: >> Ricardo, >> >> Holy Cow! You repeated the entire message string to say Thank You. Why >> did >> you waste our time? >> > > I'm sorry Nick, didn't imagine you would take the time to read the whole > message once again. My fault, I humbly apologize for making you waste so > much of your precious time. > > > P:S: it's 'Holy Cow, Batman' (were you being ironic over OT posts in a > list thread?). > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Nicholas Geti wrote: > Ricardo, > > Holy Cow! You repeated the entire message string to say Thank You. Why did > you waste our time? > I'm sorry Nick, didn't imagine you would take the time to read the whole message once again. My fault, I humbly apologize for making you waste so much of your precious time. P:S: it's 'Holy Cow, Batman' (were you being ironic over OT posts in a list thread?). ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Ricardo, Holy Cow! You repeated the entire message string to say Thank You. Why did you waste our time? > > Saludos, > Jaime Vasquez > > Muchas gracias Jaime. Me ha venido muy bien tu ayuda. Saludos. [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Just pedestrians Dave. Softer. Funny thing is I have more problems going back to the uk. Which is odd as I spent most of my life driving on correct side of the road :) Allen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Crozier Sent: 23 August 2007 10:45 To: [EMAIL PROTECTED] Subject: RE: Excel question Al, Does this somehow relate to you hitting lamp posts with your wing mirrors? You really should get the hang of this driving on the right hand side. Dave Crozier No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Al, Does this somehow relate to you hitting lamp posts with your wing mirrors? You really should get the hang of this driving on the right hand side. Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Allen Sent: 23 August 2007 08:49 To: profox@leafe.com Subject: RE: Excel question Please learn to clip posts. Allen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ricardo Aráoz Sent: 23 August 2007 00:21 To: [EMAIL PROTECTED] Subject: Re: Excel question No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
On 8/23/07, Allen <[EMAIL PROTECTED]> wrote: > Please learn to clip posts. > Allen Come on Al, Ed likes a big DB with loads of junk A+ jml ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Please learn to clip posts. Allen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ricardo Aráoz Sent: 23 August 2007 00:21 To: [EMAIL PROTECTED] Subject: Re: Excel question No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Jaime Vasquez wrote:
> Ricardo Aráoz wrote:
>> Jaime, it works! And it solves my problem.
>> Only glitch is that the second 'CopyFromRecordset(loRs, 150) && copy
>> next 150' does not copy the next 150 but copies the first 150 records
>> again. Not a problem, I can copy my chunks of records to a cursor and
>> then do a cursor at a time (rs.Delete(150) does not work either).
>>
>> Anyway if you can see a more elegant solution I'll appreciate it.
>> Thanks for your help.
>>
>> Here is my test code :
>> ---
>> loWorkBook = loExcel.WorkBooks.Add
>> loWorkBook.ActiveSheet.name = 'PrimeraHoja'
>>
>> loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
>>
>> loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
>> loWorkBook.ActiveSheet.Name = 'SegundaHoja'
>>
>> loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
>>
>> loWorkBook.Application.Visible = .T.
>>
>>
>> and the same first 20 records are copied in both pages.
>> So I do :
>> -
>> go top in MyCursor
>> lors = dbf2rs('MyCursor', 'Next 20')
>>
>> loWorkBook = loExcel.WorkBooks.Add
>> loWorkBook.ActiveSheet.name = 'PrimeraHoja'
>>
>> loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
>>
>> go 21 in MyCursor
>> lors = dbf2rs('MyCursor', 'Next 20')
>>
>> loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
>> loWorkBook.ActiveSheet.Name = 'SegundaHoja'
>>
>> loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
>>
>> loWorkBook.Application.Visible = .T.
>> -
>>
>
> Ricardo,
>
> This behavior is because the recordset is a "client recordset", you
> can't delete records, and everytime you copy records it will start from
> the beginning.
>
> With a server recordset you can use the method I suggested of copy
> records by chunks, or delete them.
>
> You can create a server recordset converting your cursor to a temporary
> phisical table and next read it with ADO.
>
> I thing the solution you found is the best, create a recordset with the
> number of records you want to copy in every page.
>
> I just have 2 observations:
> If the recordset has the number of records you want, there's no need for
> the sencond parameter in copyfromrecordset method.
>
> The copy will be a bit faster if you set excel visible = .t. before copy
> the recordset.
>
>
>
> HTH
>
>
>
> Saludos,
> Jaime Vasquez
>
>
Muchas gracias Jaime. Me ha venido muy bien tu ayuda.
Saludos.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Ricardo Aráoz wrote:
> Jaime, it works! And it solves my problem.
> Only glitch is that the second 'CopyFromRecordset(loRs, 150) && copy
> next 150' does not copy the next 150 but copies the first 150 records
> again. Not a problem, I can copy my chunks of records to a cursor and
> then do a cursor at a time (rs.Delete(150) does not work either).
>
> Anyway if you can see a more elegant solution I'll appreciate it.
> Thanks for your help.
>
> Here is my test code :
> ---
> loWorkBook = loExcel.WorkBooks.Add
> loWorkBook.ActiveSheet.name = 'PrimeraHoja'
>
> loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
>
> loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
> loWorkBook.ActiveSheet.Name = 'SegundaHoja'
>
> loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
>
> loWorkBook.Application.Visible = .T.
>
>
> and the same first 20 records are copied in both pages.
> So I do :
> -
> go top in MyCursor
> lors = dbf2rs('MyCursor', 'Next 20')
>
> loWorkBook = loExcel.WorkBooks.Add
> loWorkBook.ActiveSheet.name = 'PrimeraHoja'
>
> loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
>
> go 21 in MyCursor
> lors = dbf2rs('MyCursor', 'Next 20')
>
> loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
> loWorkBook.ActiveSheet.Name = 'SegundaHoja'
>
> loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
>
> loWorkBook.Application.Visible = .T.
> -
>
Ricardo,
This behavior is because the recordset is a "client recordset", you
can't delete records, and everytime you copy records it will start from
the beginning.
With a server recordset you can use the method I suggested of copy
records by chunks, or delete them.
You can create a server recordset converting your cursor to a temporary
phisical table and next read it with ADO.
I thing the solution you found is the best, create a recordset with the
number of records you want to copy in every page.
I just have 2 observations:
If the recordset has the number of records you want, there's no need for
the sencond parameter in copyfromrecordset method.
The copy will be a bit faster if you set excel visible = .t. before copy
the recordset.
HTH
Saludos,
Jaime Vasquez
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Jaime Vasquez wrote:
> Ricardo Aráoz wrote:
>> Jaime Vasquez wrote:
>>
>>> Ricardo Aráoz wrote:
>>>
My reports generate Excel files, if the report is too long (more than
64000 lines) I use different pages in the same file. I have two ways to
do it, either I export up to say 15000 lines with a copy to command to a
temporary excel file, then open it (as an excel object), copy it's
contents to the clipboard (through excel command) and then copy the
clipboard to a new page in my multi page excel file. The pro of this
method is speed, the con is that if the customer uses the clipboard he
interferes with the report.
The other way is to insert the data cell by cell, but if the file is too
large it gets slow.
Does any of you know of some other way to optimize this?
>>>
>>> Ricardo,
>>>
>>> The easiest and fastews way, imo, is to read the data with ado, next use
>>> the copyfromrecordset method to copy to excel.
>>>
>>> It will be fast and you don't have to deal with the volatile clipboard.
>>>
>>> *Create ado connection and recordset object
>>> loConn = NewObject('ADODB.Connection')
>>> loRs = NewObject('adodb.recordset')
>>> loconn.ConnectionString = "Provider=vfpoledb.1;Data
>>> Source=C:\myvfpdbfolder\;Collating Sequence=general;"
>>> loconn.Open
>>> lors.Open("select * from credidt.dbf", LoConn)
>>>
>>> *excel object:
>>> loExcel = NewObject("Excel.application")
>>> loWorkBook = loexcel.Workbooks.Add
>>> loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
>>> loExcel.visible = .T.
>>> loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
>>>
>>>
>>
>> Te agradezco mucho Jaime. The problem is I process my data in the local
>> app, I take data from two different engines and combine and process it
>> so I am actually dealing with a VFP cursor which I have to insert into a
>> workbook in chunks of 15 to 64000 records per worksheet. I can see that
>> if I do something like "loworksheet.ActivePage.CopyFromRecordSet(lors)"
>> I might get the file into a given page, but I would still have two
>> problems, first how to divide the file in chunks (doable), and second I
>> have my data in a cursor. Is there a way of converting a local VFP
>> cursor into a record set?
>>
>> Thanks again
>>
>
>
> Yes, a VFP cursor can be converted to an ADO recordset, see:
> http://support.microsoft.com/kb/192762
>
>
> If you want to copy just a number of records, use the sencond parameter
> of the CopyFromRecordSet method:
>
> *get a reference to the range where you want the results:
> oWorkSheet = oWorkBook.Worksheets("Page1")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy first 150
> records
>
> * next page
> oWorkSheet = oWorkBook.Worksheets("Page2")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy next 150
> records.
>
> And so on...
>
>
>
>
> HTH
>
>
>
> Saludos,
> Jaime Vasquez
> Guatemala C.A.
>
Jaime, it works! And it solves my problem.
Only glitch is that the second 'CopyFromRecordset(loRs, 150) && copy
next 150' does not copy the next 150 but copies the first 150 records
again. Not a problem, I can copy my chunks of records to a cursor and
then do a cursor at a time (rs.Delete(150) does not work either).
Anyway if you can see a more elegant solution I'll appreciate it.
Thanks for your help.
Here is my test code :
---
loWorkBook = loExcel.WorkBooks.Add
loWorkBook.ActiveSheet.name = 'PrimeraHoja'
loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
loWorkBook.ActiveSheet.Name = 'SegundaHoja'
loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.Application.Visible = .T.
and the same first 20 records are copied in both pages.
So I do :
-
go top in MyCursor
lors = dbf2rs('MyCursor', 'Next 20')
loWorkBook = loExcel.WorkBooks.Add
loWorkBook.ActiveSheet.name = 'PrimeraHoja'
loWorkBook.Worksheets("PrimeraHoja").Range('a2').CopyFromRecordSet(lors,20)
go 21 in MyCursor
lors = dbf2rs('MyCursor', 'Next 20')
loWorkBook.WorkSheets.Add(, loWorkBook.ActiveSheet)
loWorkBook.ActiveSheet.Name = 'SegundaHoja'
loWorkBook.WorkSheets("SegundaHoja").Range('a2').CopyFromRecordSet(lors,20)
loWorkBook.Application.Visible = .T.
-
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or me
Re: Excel question
Jaime Vasquez wrote:
> Ricardo Aráoz wrote:
>> Jaime Vasquez wrote:
>>
>>> Ricardo Aráoz wrote:
>>>
My reports generate Excel files, if the report is too long (more than
64000 lines) I use different pages in the same file. I have two ways to
do it, either I export up to say 15000 lines with a copy to command to a
temporary excel file, then open it (as an excel object), copy it's
contents to the clipboard (through excel command) and then copy the
clipboard to a new page in my multi page excel file. The pro of this
method is speed, the con is that if the customer uses the clipboard he
interferes with the report.
The other way is to insert the data cell by cell, but if the file is too
large it gets slow.
Does any of you know of some other way to optimize this?
>>>
>>> Ricardo,
>>>
>>> The easiest and fastews way, imo, is to read the data with ado, next use
>>> the copyfromrecordset method to copy to excel.
>>>
>>> It will be fast and you don't have to deal with the volatile clipboard.
>>>
>>> *Create ado connection and recordset object
>>> loConn = NewObject('ADODB.Connection')
>>> loRs = NewObject('adodb.recordset')
>>> loconn.ConnectionString = "Provider=vfpoledb.1;Data
>>> Source=C:\myvfpdbfolder\;Collating Sequence=general;"
>>> loconn.Open
>>> lors.Open("select * from credidt.dbf", LoConn)
>>>
>>> *excel object:
>>> loExcel = NewObject("Excel.application")
>>> loWorkBook = loexcel.Workbooks.Add
>>> loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
>>> loExcel.visible = .T.
>>> loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
>>>
>>>
>>
>> Te agradezco mucho Jaime. The problem is I process my data in the local
>> app, I take data from two different engines and combine and process it
>> so I am actually dealing with a VFP cursor which I have to insert into a
>> workbook in chunks of 15 to 64000 records per worksheet. I can see that
>> if I do something like "loworksheet.ActivePage.CopyFromRecordSet(lors)"
>> I might get the file into a given page, but I would still have two
>> problems, first how to divide the file in chunks (doable), and second I
>> have my data in a cursor. Is there a way of converting a local VFP
>> cursor into a record set?
>>
>> Thanks again
>>
>
>
> Yes, a VFP cursor can be converted to an ADO recordset, see:
> http://support.microsoft.com/kb/192762
>
>
> If you want to copy just a number of records, use the sencond parameter
> of the CopyFromRecordSet method:
>
> *get a reference to the range where you want the results:
> oWorkSheet = oWorkBook.Worksheets("Page1")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy first 150
> records
>
> * next page
> oWorkSheet = oWorkBook.Worksheets("Page2")
> oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy next 150
> records.
>
> And so on...
>
>
Thanks a lot Jaime. This will be really useful.
>
>
> HTH
>
>
>
> Saludos,
> Jaime Vasquez
> Guatemala C.A.
>
>
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Andy Davies wrote:
>>Using Jaime's method doesn't involve any export/import of physical
>>data files.
>>Dave Crozier
>
>
> Dave,
> does rs.Open("select * from credidt.dbf",...) do less under the hood than
> use credidt.dbf then?
>
> .. and does anyone know if the excel .CopyFromRecordset method also
> truncates cells to 256 characters?
>
No, it's limited to the limit of the cell, 32767 characters. Only 1024
display in cell, all 32767 display in the formula bar.
HTH
Saludos,
Jaime Vasquez
Guatemala C.A.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Ricardo Aráoz wrote:
> Jaime Vasquez wrote:
>
>>Ricardo Aráoz wrote:
>>
>>>My reports generate Excel files, if the report is too long (more than
>>>64000 lines) I use different pages in the same file. I have two ways to
>>>do it, either I export up to say 15000 lines with a copy to command to a
>>>temporary excel file, then open it (as an excel object), copy it's
>>>contents to the clipboard (through excel command) and then copy the
>>>clipboard to a new page in my multi page excel file. The pro of this
>>>method is speed, the con is that if the customer uses the clipboard he
>>>interferes with the report.
>>>The other way is to insert the data cell by cell, but if the file is too
>>>large it gets slow.
>>>Does any of you know of some other way to optimize this?
>>
>>
>>Ricardo,
>>
>>The easiest and fastews way, imo, is to read the data with ado, next use
>>the copyfromrecordset method to copy to excel.
>>
>>It will be fast and you don't have to deal with the volatile clipboard.
>>
>>*Create ado connection and recordset object
>>loConn = NewObject('ADODB.Connection')
>>loRs = NewObject('adodb.recordset')
>>loconn.ConnectionString = "Provider=vfpoledb.1;Data
>>Source=C:\myvfpdbfolder\;Collating Sequence=general;"
>>loconn.Open
>>lors.Open("select * from credidt.dbf", LoConn)
>>
>>*excel object:
>>loExcel = NewObject("Excel.application")
>>loWorkBook = loexcel.Workbooks.Add
>>loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
>>loExcel.visible = .T.
>>loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
>>
>>
>
>
> Te agradezco mucho Jaime. The problem is I process my data in the local
> app, I take data from two different engines and combine and process it
> so I am actually dealing with a VFP cursor which I have to insert into a
> workbook in chunks of 15 to 64000 records per worksheet. I can see that
> if I do something like "loworksheet.ActivePage.CopyFromRecordSet(lors)"
> I might get the file into a given page, but I would still have two
> problems, first how to divide the file in chunks (doable), and second I
> have my data in a cursor. Is there a way of converting a local VFP
> cursor into a record set?
>
> Thanks again
>
Yes, a VFP cursor can be converted to an ADO recordset, see:
http://support.microsoft.com/kb/192762
If you want to copy just a number of records, use the sencond parameter
of the CopyFromRecordSet method:
*get a reference to the range where you want the results:
oWorkSheet = oWorkBook.Worksheets("Page1")
oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy first 150
records
* next page
oWorkSheet = oWorkBook.Worksheets("Page2")
oworksheet.Range("a1").CopyFromRecordset(loRs, 150) && copy next 150
records.
And so on...
HTH
Saludos,
Jaime Vasquez
Guatemala C.A.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Paul Newton wrote: > Dave > > IIRC the 65536 limit applies to worksheets not workbooks? > > So one should be able to use this method multiple times (with chunks of > 65536 records) and put each chunk in a different worksheet in the same > workbook > Yes, the limit is for Excel, not ADO, although Excel 2007 has now a limit of 1048576 rows. HTH Saludos, Jaime Vasquez Guatemala C.A. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Andy Davies wrote: >>The easiest and fastews way, imo, is to read the data with ado, next use >>the copyfromrecordset method to copy to excel. > > > Jaime, > what advantages does ado have over e.g. 'copy to xxx type xl5' ? > Andy, Advantages, imo: More control, with copy to... , you have to createa a file for any worksheet and later marge each file to a workbook, wich tends to more erros and larger programming. With ADO, you only have to get a referencie to the range where you want to copy the results, that can be any cell in any worksheet. Speed, no need to create phisical files, everything is done in memory. No need to deal with odd codepage conversions. ADO is the method Excel underestands better. Remember use the right tool for the job. :) HTH Saludos, Jaime Vasquez Guatemala C.A. ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Dave Crozier wrote:
> Ricardo,
> How about using:
>
> Application.DataToClip(<>,99,3)
>
> Then pasting the data into Excel using Automation. That way it is really
> quick.
>
> Dave Crozier
>
Hi Dave,
I'm not using DataToClip, I'm actually copying my records type XLS to an
excel file and then :
oExcel2 = CreateObject("Excel.Application")
oWkbk2 = oExcel2.Workbooks.Open(lcArchXLS)
oExcel2.ActiveSheet.Rows(1).currentregion.select
oExcel2.Selection.copy
oExcel.ActiveSheet.paste
_CLIPTEXT = ""
oExcel2.Quit()
oExcel2 = null
oWkbk2 = null
Your way will certainly be faster, but my problem with both these
methods in not speed but the fact that if the user types -C while
I'm working he messes the page up.
OTOH inserting every cell using automation (which solves the -C
problem) is too slow.
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
> Of Ricardo Aráoz
> Sent: 16 August 2007 00:39
> To: ProFox Email List
> Subject: Excel question
>
> Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking)
> mails to be read, so I hope this is not yet a subject.
>
> My reports generate Excel files, if the report is too long (more than
> 64000 lines) I use different pages in the same file. I have two ways to
> do it, either I export up to say 15000 lines with a copy to command to a
> temporary excel file, then open it (as an excel object), copy it's
> contents to the clipboard (through excel command) and then copy the
> clipboard to a new page in my multi page excel file. The pro of this
> method is speed, the con is that if the customer uses the clipboard he
> interferes with the report.
> The other way is to insert the data cell by cell, but if the file is too
> large it gets slow.
> Does any of you know of some other way to optimize this?
> TIA
>
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Re: Excel question
Paul Newton wrote: > Ricardo Aráoz wrote: >> Problem is I don't find any property that allows to open (or import) a >> file in a specific worksheet. So if I am to use several worksheets I'm >> stuck with either inserting cell by cell, or opening a file (csv) in a >> second (auxiliary) workbook and then cut and paste (which can be >> vulnerable through the clipboard). >> > Ricardo > > I think you are trying to rely too much on Excel and not enough on VFP. > > First of all I assume you are trying to get a large number of rows of > data from VFP into Excel. > > 1. Different versions of Excel have different (row) limits, IIRC ca > 16K/64K depending on version > 2. Again IIRC EXPORT/COPY TO ...TYPE ... respects the > (appropriate/corresponding) Excel limits - no that's not right - check > the (VFP) help - I know that VFP has similar limits (16K/64K rows) for > this/these commands. > > So > > Use (Dave C's suggestion) of DATATOCLIP but do it 16K/64K rows at a time. > > From VFP, create/open an Excel workbook - add as many worksheets as you > need (in advance or as needed) > > For each DATATOCLIP, set the active cell in Excel to the corresponding > cell (A1) and worksheet and paste the data. > > Does that help ? > > Paul Newton > > Thanks Paul, that's approximately what I'm doing (when the tables are big, else I copy a cell at a time through automation). The problem is it interferes with the clipboard, and if the user is working, say in word and cutting and pasting then both works will suffer. Here most people keep working on other stuff while reports are generated so now and then that problem arises. What I was looking for is a way to import a csv file or another xls to a specific worksheet without using the clipboard. Cheers ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Jaime Vasquez wrote:
> Ricardo Aráoz wrote:
>> My reports generate Excel files, if the report is too long (more than
>> 64000 lines) I use different pages in the same file. I have two ways to
>> do it, either I export up to say 15000 lines with a copy to command to a
>> temporary excel file, then open it (as an excel object), copy it's
>> contents to the clipboard (through excel command) and then copy the
>> clipboard to a new page in my multi page excel file. The pro of this
>> method is speed, the con is that if the customer uses the clipboard he
>> interferes with the report.
>> The other way is to insert the data cell by cell, but if the file is too
>> large it gets slow.
>> Does any of you know of some other way to optimize this?
>
>
> Ricardo,
>
> The easiest and fastews way, imo, is to read the data with ado, next use
> the copyfromrecordset method to copy to excel.
>
> It will be fast and you don't have to deal with the volatile clipboard.
>
> *Create ado connection and recordset object
> loConn = NewObject('ADODB.Connection')
> loRs = NewObject('adodb.recordset')
> loconn.ConnectionString = "Provider=vfpoledb.1;Data
> Source=C:\myvfpdbfolder\;Collating Sequence=general;"
> loconn.Open
> lors.Open("select * from credidt.dbf", LoConn)
>
> *excel object:
> loExcel = NewObject("Excel.application")
> loWorkBook = loexcel.Workbooks.Add
> loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
> loExcel.visible = .T.
> loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
>
>
Te agradezco mucho Jaime. The problem is I process my data in the local
app, I take data from two different engines and combine and process it
so I am actually dealing with a VFP cursor which I have to insert into a
workbook in chunks of 15 to 64000 records per worksheet. I can see that
if I do something like "loworksheet.ActivePage.CopyFromRecordSet(lors)"
I might get the file into a given page, but I would still have two
problems, first how to divide the file in chunks (doable), and second I
have my data in a cursor. Is there a way of converting a local VFP
cursor into a record set?
Thanks again
>
> HTH
>
>
>
>
> Saludos,
> Jaime Vasquez
> Guatemala C.A.
>
>
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Jean, I guess that they are of limited value but personally I have used them on a few occasions. For example, when exporting invoice information out for presentation in an Excel "Invoice" when the descriptive field can be of indeterminate length. Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jean Laeremans Sent: 17 August 2007 11:36 To: ProFox Email List Subject: Re: Excel question On 8/17/07, Dave Crozier <[EMAIL PROTECTED]> wrote: > Andy, > Memo data seems to go in no problem with no restriction on field length. I > just tried a memo field in excess of 1000 characters. > > Dave Crozier What's the use of a memo field in a spreadsheet ? A+ jml [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
>Andy, >Memo data seems to go in no problem with no restriction on field length. I >just tried a memo field in excess of 1000 characters. > >Dave Crozier yes, sorry - senior moment: it's importing into SQL Server from Excel that truncates to 256 - I *think* it does the same from .dbf memo fields Andrew Davies MBCS CITP - AndyD 8-)# ** Manchester City Council supports Smokefree England - 1 July 2007 This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. Please contact [EMAIL PROTECTED] with any queries. ** ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
On 8/17/07, Dave Crozier <[EMAIL PROTECTED]> wrote: > Andy, > Memo data seems to go in no problem with no restriction on field length. I > just tried a memo field in excess of 1000 characters. > > Dave Crozier What's the use of a memo field in a spreadsheet ? A+ jml ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Paul, Yes, I've just done an import into multiple sheets in the same workbook with no problem Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Newton Sent: 17 August 2007 11:08 To: profox@leafe.com Subject: Re: Excel question Dave IIRC the 65536 limit applies to worksheets not workbooks? So one should be able to use this method multiple times (with chunks of 65536 records) and put each chunk in a different worksheet in the same workbook Paul Dave Crozier wrote: > Paul, > I've just done some tests and imported a .dbf file of 7Mb so there is no > problem there in terms of file size. > > However, the limit of 65536 records is still there though and anything after > that is ignored and its quick to import. > > Dave Crozier > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Paul Newton > Sent: 17 August 2007 10:39 > To: profox@leafe.com > Subject: Re: Excel question > > Dave Crozier wrote: > >> Andy, >> Using Jaime's method doesn't involve any export/import of physical data >> files. >> >> > Dave > > With that method will one still have to create multiple (16K/64K) > recordsets ? > > Paul > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Andy,
Memo data seems to go in no problem with no restriction on field length. I
just tried a memo field in excess of 1000 characters.
Dave Crozier
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Andy Davies
Sent: 17 August 2007 10:56
To: profox@leafe.com
Subject: RE: Excel question
> Using Jaime's method doesn't involve any export/import of physical
> data files.
> Dave Crozier
Dave,
does rs.Open("select * from credidt.dbf",...) do less under the hood than
use credidt.dbf then?
.. and does anyone know if the excel .CopyFromRecordset method also
truncates cells to 256 characters?
Andrew Davies MBCS CITP
- AndyD 8-)#
**
Manchester City Council supports Smokefree England - 1 July 2007
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
Please contact [EMAIL PROTECTED] with any queries.
**
[excessive quoting removed by server]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Dave IIRC the 65536 limit applies to worksheets not workbooks? So one should be able to use this method multiple times (with chunks of 65536 records) and put each chunk in a different worksheet in the same workbook Paul Dave Crozier wrote: > Paul, > I've just done some tests and imported a .dbf file of 7Mb so there is no > problem there in terms of file size. > > However, the limit of 65536 records is still there though and anything after > that is ignored and its quick to import. > > Dave Crozier > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Paul Newton > Sent: 17 August 2007 10:39 > To: profox@leafe.com > Subject: Re: Excel question > > Dave Crozier wrote: > >> Andy, >> Using Jaime's method doesn't involve any export/import of physical data >> files. >> >> > Dave > > With that method will one still have to create multiple (16K/64K) > recordsets ? > > Paul > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Paul, I've just done some tests and imported a .dbf file of 7Mb so there is no problem there in terms of file size. However, the limit of 65536 records is still there though and anything after that is ignored and its quick to import. Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Newton Sent: 17 August 2007 10:39 To: profox@leafe.com Subject: Re: Excel question Dave Crozier wrote: > Andy, > Using Jaime's method doesn't involve any export/import of physical data > files. > Dave With that method will one still have to create multiple (16K/64K) recordsets ? Paul [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
> Using Jaime's method doesn't involve any export/import of physical
> data files.
> Dave Crozier
Dave,
does rs.Open("select * from credidt.dbf",...) do less under the hood than
use credidt.dbf then?
.. and does anyone know if the excel .CopyFromRecordset method also
truncates cells to 256 characters?
Andrew Davies MBCS CITP
- AndyD 8-)#
**
Manchester City Council supports Smokefree England - 1 July 2007
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed. If
you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
Please contact [EMAIL PROTECTED] with any queries.
**
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Dave Crozier wrote: > Andy, > Using Jaime's method doesn't involve any export/import of physical data > files. > Dave With that method will one still have to create multiple (16K/64K) recordsets ? Paul ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Andy, Using Jaime's method doesn't involve any export/import of physical data files. Nice one Jaime, I'll put that one into the book of "must have snippets". Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Davies Sent: 17 August 2007 09:51 To: profox@leafe.com Subject: Re: Excel question > The easiest and fastews way, imo, is to read the data with ado, next use > the copyfromrecordset method to copy to excel. Jaime, what advantages does ado have over e.g. 'copy to xxx type xl5' ? Andrew Davies MBCS CITP - AndyD 8-)# ** Manchester City Council supports Smokefree England - 1 July 2007 This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. Please contact [EMAIL PROTECTED] with any queries. ** [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
> The easiest and fastews way, imo, is to read the data with ado, next use > the copyfromrecordset method to copy to excel. Jaime, what advantages does ado have over e.g. 'copy to xxx type xl5' ? Andrew Davies MBCS CITP - AndyD 8-)# ** Manchester City Council supports Smokefree England - 1 July 2007 This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. Please contact [EMAIL PROTECTED] with any queries. ** ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Re: Excel question
suggestion: in vfp select your data 16/64K rows at a time into a cursor (... where recno() > nLo and recno() <= nHi && or any other way you prefer) copy to ... type xl5 && etc. In Excel you can bring worksheets together into one workbook fairly easily. - or - there is an Excel odbc driver but I haven't used it for years - others may know its limitations. Andrew Davies MBCS CITP - AndyD 8-)# ** Manchester City Council supports Smokefree England - 1 July 2007 This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. Please contact [EMAIL PROTECTED] with any queries. ** ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Re: Excel question
Ricardo Aráoz wrote: > Problem is I don't find any property that allows to open (or import) a > file in a specific worksheet. So if I am to use several worksheets I'm > stuck with either inserting cell by cell, or opening a file (csv) in a > second (auxiliary) workbook and then cut and paste (which can be > vulnerable through the clipboard). > Ricardo I think you are trying to rely too much on Excel and not enough on VFP. First of all I assume you are trying to get a large number of rows of data from VFP into Excel. 1. Different versions of Excel have different (row) limits, IIRC ca 16K/64K depending on version 2. Again IIRC EXPORT/COPY TO ...TYPE ... respects the (appropriate/corresponding) Excel limits - no that's not right - check the (VFP) help - I know that VFP has similar limits (16K/64K rows) for this/these commands. So Use (Dave C's suggestion) of DATATOCLIP but do it 16K/64K rows at a time. From VFP, create/open an Excel workbook - add as many worksheets as you need (in advance or as needed) For each DATATOCLIP, set the active cell in Excel to the corresponding cell (A1) and worksheet and paste the data. Does that help ? Paul Newton ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Re: Excel question
Helio W. wrote: > Not manually, of course. > > On 8/15/07, Helio W. <[EMAIL PROTECTED]> wrote: >> Can't you just move worksheet by worksheet and do an Data/Import External >> Data? >> Problem is I don't find any property that allows to open (or import) a file in a specific worksheet. So if I am to use several worksheets I'm stuck with either inserting cell by cell, or opening a file (csv) in a second (auxiliary) workbook and then cut and paste (which can be vulnerable through the clipboard). >> >> On 8/15/07, Ricardo Aráoz < [EMAIL PROTECTED]> wrote: >>> Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking) >>> mails to be read, so I hope this is not yet a subject. >> >> >> > ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: [NF] Re: Excel question
Not manually, of course. On 8/15/07, Helio W. <[EMAIL PROTECTED]> wrote: > > Can't you just move worksheet by worksheet and do an Data/Import External > Data? > > > On 8/15/07, Ricardo Aráoz < [EMAIL PROTECTED]> wrote: > > > > Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking) > > mails to be read, so I hope this is not yet a subject. > > > > --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Re: Excel question
Ricardo Aráoz wrote:
>
> My reports generate Excel files, if the report is too long (more than
> 64000 lines) I use different pages in the same file. I have two ways to
> do it, either I export up to say 15000 lines with a copy to command to a
> temporary excel file, then open it (as an excel object), copy it's
> contents to the clipboard (through excel command) and then copy the
> clipboard to a new page in my multi page excel file. The pro of this
> method is speed, the con is that if the customer uses the clipboard he
> interferes with the report.
> The other way is to insert the data cell by cell, but if the file is too
> large it gets slow.
> Does any of you know of some other way to optimize this?
Ricardo,
The easiest and fastews way, imo, is to read the data with ado, next use
the copyfromrecordset method to copy to excel.
It will be fast and you don't have to deal with the volatile clipboard.
*Create ado connection and recordset object
loConn = NewObject('ADODB.Connection')
loRs = NewObject('adodb.recordset')
loconn.ConnectionString = "Provider=vfpoledb.1;Data
Source=C:\myvfpdbfolder\;Collating Sequence=general;"
loconn.Open
lors.Open("select * from credidt.dbf", LoConn)
*excel object:
loExcel = NewObject("Excel.application")
loWorkBook = loexcel.Workbooks.Add
loWorkSheet = loexcel.ActiveWorkbook.Worksheets.Add
loExcel.visible = .T.
loworksheet.range("A1").CopyFromRecordset(lors) && copy data to excel
HTH
Saludos,
Jaime Vasquez
Guatemala C.A.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
RE: Excel question
Ricardo, How about using: Application.DataToClip(<>,99,3) Then pasting the data into Excel using Automation. That way it is really quick. Dave Crozier -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ricardo Aráoz Sent: 16 August 2007 00:39 To: ProFox Email List Subject: Excel question Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking) mails to be read, so I hope this is not yet a subject. My reports generate Excel files, if the report is too long (more than 64000 lines) I use different pages in the same file. I have two ways to do it, either I export up to say 15000 lines with a copy to command to a temporary excel file, then open it (as an excel object), copy it's contents to the clipboard (through excel command) and then copy the clipboard to a new page in my multi page excel file. The pro of this method is speed, the con is that if the customer uses the clipboard he interferes with the report. The other way is to insert the data cell by cell, but if the file is too large it gets slow. Does any of you know of some other way to optimize this? TIA [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
[NF] Re: Excel question
Can't you just move worksheet by worksheet and do an Data/Import External Data? On 8/15/07, Ricardo Aráoz <[EMAIL PROTECTED]> wrote: > > Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking) > mails to be read, so I hope this is not yet a subject. --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Excel question
Ok, got 135 ProFox (224 Python, 63 Python Tutor, have to stop slacking) mails to be read, so I hope this is not yet a subject. My reports generate Excel files, if the report is too long (more than 64000 lines) I use different pages in the same file. I have two ways to do it, either I export up to say 15000 lines with a copy to command to a temporary excel file, then open it (as an excel object), copy it's contents to the clipboard (through excel command) and then copy the clipboard to a new page in my multi page excel file. The pro of this method is speed, the con is that if the customer uses the clipboard he interferes with the report. The other way is to insert the data cell by cell, but if the file is too large it gets slow. Does any of you know of some other way to optimize this? TIA ___ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
