Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Fieldlength 1

Status
Not open for further replies.
Lanceone

Lanceone

Programmer
May 2, 2003
9
0
0
DE
Hallo @all,

how can i get the Fieldlength from a Cell from Excel.
For example A1, the sheet1 called Name, there are Names with 30 sin and one with 15, others with 40 and so on, is there funktion or what ever who says the cell is 50 sin long.
Thanks for your help.
Lorenz
I use this code:
PUBLIC ARRAY myArray[26]
LOCAL x,y
x = 1
y = 0
lcExceldaten = GETFILE('xls', 'open excel-file ', 'open', 1, 'Find the excel-file to open')
o=Createobject("excel.application") &&Create an Excel instance
myDoc=o.Workbooks.Open(lcExceldaten) && Open the workbook
myDoc.Worksheets(1).Select && Make sheet 1 the active sheet
mySheet=myDoc.ActiveSheet && Make a variable of the active sheet
For i = 65 To 150 && Or whatever the highest instance could be
cLetter = CHR(i)
If !ISNULL(mySheet.Range(cLetter+"1").Value) && Check to see if there is an acutal value
myArray[x]=mySheet.Range(cLetter+"1").Value
x = x + 1
ELSE
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
y =y +1
lc = strtran((myArray[j])," ","_")
endif
ENDFOR
PUBLIC ARRAY myArray2[y]
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
myArray2[j]=myArray[j]
endif
ENDFOR
Exit
Endif
Next
 
Sort by date Sort by votes
Lanceone

Do you mean "how many characters there are in a given cell?"
If so this will do it:
Code: 
oExcel=CREATEOBJECT("excel.application")
oWorkBook=oExcel.Workbooks.Open("C:\BOOK1.XLS")
oSheet=oWorkbook.Sheets("sheet1")
oSheet.Cells(1,1).Select
myText=oSheet.Cells(1,1).Text
?LEN(myText)



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
great forum, great help,
thanks for your replay, mike
it works perfect for me

have a nice day
lorenz
 
Upvote 0 Downvote
Lanceone

great forum, great help,
thanks for your replay, mike
it works perfect for me

Glad to help.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
Hi mike,
question again, do you know how to check the hold length of the table A1, or B1 and so on

regards lorenz
 
Upvote 0 Downvote
lanceone

question again, do you know how to check the hold length of the table A1, or B1 and so on

I'm not sure I understand what you mean. How many records in a VFP table?
Like
Code: 
SELECT myTable
COUNT TO x
? x


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
Hi mike,

i mean in the exceltable, sorry, for my missunderstand.

How to check the hold length in the sheet() in A1 or B1 or C1.

sorry for my english :), iam a german guy

Best regards lorenz

 
Upvote 0 Downvote
Lanceone

How to check the hold length in the sheet() in A1 or B1 or C1.

Hummmm....I'm not sure what you want to check for, but the following will give you the lenght of the text value of each cells start at A to Z (it does not account fo the double letters), for all 65536 Rows!! You may want to adjust the function to suit your needs:
Code: 
oExcel=CREATEOBJECT("excel.application")
oWorkBook=oExcel.Workbooks.Open("C:\BOOK1.XLS")
oSheet=oWorkbook.Sheets("sheet1")
FOR i = 65 TO 90
  lcCellnum = CHR(i)
  FOR j = 1 TO 65536
     lcCellnum = lcCellnum +ALLTRIM(STR(j)) 
     charlen = LEN(oSheet.Range(lcCellnum).text)
  ENDFOR
ENDFOR




Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Upvote 0 Downvote
Mike,

I believe Lanceone might be asking how to determine the column width of a given Excel column, not the length of the data contained in a specific cell.

I don't know the answer, however. There's probably a column property for it but I don't know what it would be.



Mike Krausnick
 
Upvote 0 Downvote
.Columns(bodyrange).EntireColumn.AutoFit

VarWidth=.Columns(bodyrange).ColumnWidth
 
Upvote 0 Downvote
Hallo @all,

first thanks for help, mike knausrick has my answer
but how put this in my code do check the column width
of each
PUBLIC ARRAY myArray[26]
LOCAL x,y
x = 1
y = 0
lcExceldaten = GETFILE('xls', 'open excel-file ', 'open', 1, 'Find the excel-file to open')
o=Createobject("excel.application") &&Create an Excel instance
myDoc=o.Workbooks.Open(lcExceldaten) && Open the workbook
myDoc.Worksheets(1).Select && Make sheet 1 the active sheet
mySheet=myDoc.ActiveSheet && Make a variable of the active sheet
For i = 65 To 150 && Or whatever the highest instance could be
cLetter = CHR(i)
If !ISNULL(mySheet.Range(cLetter+"1").Value) && Check to see if there is an acutal value
myArray[x]=mySheet.Range(cLetter+"1").Value
x = x + 1
ELSE
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
y =y +1
lc = strtran((myArray[j])," ","_")
endif
ENDFOR
PUBLIC ARRAY myArray2[y]
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
myArray2[j]=myArray[j]
endif
ENDFOR
Exit
Endif
Next

Best regard Lorenz
 
Upvote 0 Downvote
Hallo @all,

i have think about it and fix that stuff here is my code
thanks again for all help have a nice day
I hope somebody can use this code too
clear
PUBLIC ARRAY myArray[26]
LOCAL x,y
x = 1
y = 0
a = 0 &&cellline
b = 0 &&cellline
lcExceldaten = GETFILE('xls', 'open excel-file ', 'open', 1, 'Find the excel-file to open')
oExcel=Createobject("excel.application") &&Create an Excel instance
myDoc=oExcel.Workbooks.Open(lcExceldaten) && Open the workbook
ml = oExcel.ActiveSheet()

myDoc.Worksheets(1).Select && Make sheet 1 the active sheet
mySheet=myDoc.ActiveSheet && Make a variable of the active sheet


For i = 65 To 150 && Or whatever the highest instance could be
cLetter = CHR(i)
If !ISNULL(mySheet.Range(cLetter+"1").Value) && Check to see if there is an acutal value
myArray[x]=mySheet.Range(cLetter+"1").Value
VarWidth= round(ml.Columns(cLetter).ColumnWidth,0)
? VarWidth
x = x + 1
ELSE
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
y =y +1
lc = strtran((myArray[j])," ","_")
* ? Y
* ?? " ",lc
endif
ENDFOR
PUBLIC ARRAY myArray2[y]
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
myArray2[j]=myArray[j]
* ? myArray2[j]
endif
ENDFOR
Exit
Endif
Next
*----end Excel
oExcel.quit
Release oExcel
:)
 
Upvote 0 Downvote
once again,
i use a free table so if there a double word i fixed with this code to use for free. Enjoy :)
PUBLIC ARRAY myArray[26]
LOCAL x,y, lcDatenbank , lcTablename , lnCountBuchstaben
x = 1
y = 0
lnCountBuchstaben = 0
lcExceldaten = GETFILE('xls', 'open excel-file ', 'open', 1, 'Find the excel-file to open')
oExcel=Createobject("excel.application") &&Create an Excel instance
myDoc=oExcel.Workbooks.Open(lcExceldaten) && Open the workbook
mylenght = oExcel.ActiveSheet()
myDoc.Worksheets(1).Select && Make sheet 1 the active sheet
mySheet=myDoc.ActiveSheet && Make a variable of the active sheet
lcDatenbank = ""
lcTablename = ""
For i = 65 To 150 && Or whatever the highest instance could be
cLetter = CHR(i)
If !ISNULL(mySheet.Range(cLetter+"1").Value) && Check to see if there is an acutal value
myArray[x]=mySheet.Range(cLetter+"1").Value
lcTablename = left(strtran((myArray[x])," ","_"),10)
VarWidth= round(mylenght.Columns(cLetter).ColumnWidth,0)
x = x + 1
* ? VarWidth, " ", x
* ?? lcTablename
&& ab hier mache ich dann meine neue Tabelle rein
if file("&lcDatenbank") = .T.
if .not. used("XNeueTabelleX")
ALTER TABLE XNeueTabelleX ADD COLUMN ("&lcTablename") c(VarWidth)ERROR "Feldname bereits Vorhanden"
else
&& damit keine doppelten Namen da sind, sollte es hiermit klappen
&& if more then one word like the other word
llstop = .F.
select XNeueTabelleX
gnFieldcount = AFIELDS(gaMyArray) && Create array
FOR nCount = 1 TO gnFieldcount
lcname = gaMyArray(nCount,1)
if lcname = lcTablename
* ? gaMyArray(nCount,1) && Display field names
lnCountBuchstaben = lnCountBuchstaben + 1
lcTablenameneu = left((lcname),9)+ alltrim(str(lnCountBuchstaben))
* ?? lcTablenameneu
ALTER TABLE ("&lcDatenbank") ADD COLUMN ("&lcTablenameneu") c(VarWidth)
llstop = .T.
endif
endfor
if llstop = .F.
* ? lcTablename
ALTER TABLE ("&lcDatenbank") ADD COLUMN ("&lcTablename") c(VarWidth)
endif
endif
else
lcDatenbank = GETFILE('dbf', 'Neuer Name', 'Speichern', 0, 'Abspeichern der neuen Tabelle')
CREATE TABLE ("&lcDatenbank") ;
(("&lcTablename") c(VarWidth))
close tables
use ("&lcDatenbank") in 0 exclusive alias XNeueTabelleX
endif
&& und Ende der Abfrage
&& end of check
ELSE
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
y =y +1
lc = strtran((myArray[j])," ","_")
endif
ENDFOR
PUBLIC ARRAY myArray2[y]
FOR j = 1 TO ALEN(myArray)
IF !EMPTY(myArray[j])
myArray2[j]=myArray[j]
endif
ENDFOR
Exit
Endif
Next
*----end Excel
close tables
oExcel.quit && must be closed or you get a problem to open more
Release oExcel

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ravicoder
  • Question
excel automation printing to pdf error
Replies
8
Views
250
combo
combo
ravicoder
  • Locked
  • Question
Generating graphs using excel automation
Replies
1
Views
86
TamarGranor
TamarGranor
ravicoder
  • Locked
  • Question
Using the Union Method of excel automation
Replies
3
Views
88
Mike Lewis
Mike Lewis
igorsandler
  • Locked
  • Question
Excel automation in FoxPro 7
Replies
1
Views
90
TamarGranor
TamarGranor
ravicoder
  • Locked
  • Question
Adding a worksheet after existing worksheet in excel automation
Replies
5
Views
134
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top