It seems that i ran into some limitations using the FW_ExcelToDBF function adn that I better move to TOle with exel
Is there a sample that opens a exel, and copy all koloms into a dbf ? In that situation I can change the values from the source exel to ALL text values. with the OLE function i think.
cFile := _ExcelFileName() // insert your filename here
Try
oExcel := CREATEOBJECT( "Excel.Application" )
CATCH
Msginfo( "For Some reason EXCEL could not be Started .. Aborting" )
CLose Databases
Ferase( xVOL+"\DBTMP\"+REQDBF+".DBF" )
oDLg:ENd()
Return(.f.)
ENd Try
oExcel:WorkBooks:Add()
oSheet := oExcel:ActiveSheet
oSheet:Range( "N:O" ):Set( "NumberFormat", '0.00' )
oSheet:Range( "Q:R" ):Set( "NumberFormat", '0.00' )
oSheet:Range( "T:W" ):Set( "NumberFormat", '0.00' )
oSheet:Range( "Z:AD" ):Set( "NumberFormat", '0.00' )
oSheet:Range( "BC:BD" ):Set( "NumberFormat", '0.00' )
oSheet:Range( "BF:BG" ):Set( "NumberFormat", '0.00' )
oSheet:Cells( 1, 1 ):value := "PERSNO"
oSheet:Cells( 1, 2 ):value := "EMPLOYEE"
oSheet:Cells( 1, 3 ):value := "STARTDT"
oSheet:Cells( 1, 4 ):value := "ENDDT"
oSheet:Cells( 1, 5 ):value := "PREVAPPR"
oSheet:Cells( 1, 6 ):value := "DESIGN"
oSheet:Cells( 1, 7 ):value := "INSTATE"
oSheet:Cells( 1, 8 ):value := "OUTSTATE"
oSheet:Cells( 1, 9 ):value := "SPONSOR"
oSheet:Cells( 1, 10 ):value := "EVENT"
oSheet:Cells( 1, 11 ):value := "LOCATION"
oSheet:Cells( 1, 12 ):value := "BENEFITS"
oSheet:Cells( 1, 13):value := "BREAK"
oSheet:Cells( 1, 14) :value := "BREAKC"
oSheet:Cells( 1, 15):value := "BREAKT"
oSheet:Cells( 1, 16):value := "LUNCH"
oSheet:Cells( 1, 17):value := "LUNCHC"
oSheet:Cells( 1, 18):value := "LUNCHT"
oSheet:Cells( 1, 19 ):value := "DINNER"
oSheet:Cells( 1, 20):value := "DINNERC"
oSheet:Cells( 1, 21):value := "DINNERT"
oSheet:Cells( 1, 22):value := "TMEALS"
oSheet:Cells( 1, 23):value := "THOTEL"
// new
oSheet:Cells( 1, 24):value := "NUMMILES"
oSheet:Cells( 1, 25):value := "MRATE"
oSheet:Cells( 1, 26):value := "TMILEAGE"
oSheet:Cells( 1, 27):value := "REGIST"
oSheet:Cells( 1, 28):value := "AIRFARE"
oSheet:Cells( 1, 29):value := "MISCCOST"
oSheet:Cells( 1, 30):value := "GRANDTOT"
oSheet:Cells( 1, 31):value := "CONTEDCR"
oSheet:Cells( 1, 32):value := "HOMANY"
oSheet:Cells( 1, 33):value := "SPEECH"
oSheet:Cells( 1, 34):value := "TT1"
oSheet:Cells( 1, 35):value := "TRAINTP1"
oSheet:Cells( 1, 36):value := "TT2"
oSheet:Cells( 1, 37):value := "TRAINTP2"
oSheet:Cells( 1, 38):value := "TT3"
oSheet:Cells( 1, 39):value := "TRAINTP3"
oSheet:Cells( 1, 40):value := "TT4"
oSheet:Cells( 1, 41):value := "TRAINTP4"
oSheet:Cells( 1, 42):value := "TT5"
oSheet:Cells( 1, 43):value := "TRAINTP5"
oSheet:Cells( 1, 44):value := "TT6"
oSheet:Cells( 1, 45):value := "TRAINTP6"
oSheet:Cells( 1, 46):value := "KT1"
oSheet:Cells( 1, 47):value := "KTRAN1"
oSheet:Cells( 1, 48):value := "KT2"
oSheet:Cells( 1, 49):value := "KTRAN2"
oSheet:Cells( 1, 50):value := "KT3"
oSheet:Cells( 1, 51):value := "KTRAN3"
oSheet:Cells( 1, 52):value := "KT4"
oSheet:Cells( 1, 53):value := "KTRAN4"
oSheet:Cells( 1, 54):value := "CURRFY"
oSheet:Cells( 1, 55):value := "CURRCONT"
oSheet:Cells( 1, 56):value := "CURRHRS"
oSheet:Cells( 1, 57):value := "PREVFY"
oSheet:Cells( 1, 58):value := "PREVCONT"
oSheet:Cells( 1, 59):value := "PREVHRS"
oSheet:Cells( 1, 60):value := "MGR1USER"
oSheet:Cells( 1, 61):value := "MGR1"
oSheet:Cells( 1, 62):value := "MGR1DATE"
oSheet:Cells( 1, 63):value := "MGR1YES"
oSheet:Cells( 1, 64):value := "MGR2USER"
oSheet:Cells( 1, 65):value := "MGR2"
oSheet:Cells( 1, 66):value := "MGR2DATE"
oSheet:Cells( 1, 67):value := "MGR2YES"
oSheet:Cells( 1, 68):value := "MGR3USER"
oSheet:Cells( 1, 69):value := "MGR3"
oSheet:Cells( 1, 70):value := "MGR3DATE"
oSheet:Cells( 1, 71):value := "MGR3YES"
oSheet:Cells( 1, 72):value := "MGR4USER"
oSheet:Cells( 1, 73):value := "MGR4"
oSheet:Cells( 1, 74):value := "MGR4DATE"
oSheet:Cells( 1, 75):value := "MGR4YES"
oSheet:Cells( 1, 76):value := "FANSWER"
oSheet:Cells( 1, 77):value := "FDATE"
oSheet:Cells( 1, 78):value := "REJECTCM"
oSheet:Cells( 1, 79):value := "RSBY"
oSheet:Cells( 1, 80):value := "RSDATE"
oSheet:Cells( 1, 81):value := "FISCALYR"
oSheet:Cells( 1, 82):value := "DOCS"
nROW := 2
nREC := 0
cSay4 := 'Generating Excel File '+STR(nREC)
oSay4:ReFresh()
SysReFresh()
Select 1
Go Top
DO While .not. EOf()
nRec++
cSay4 := 'Generating Excel File '+STR(nREC)
oSay4:ReFresh()
SysReFresh()
oSheet:Cells( nRow, 1 ):Value := a->Persno
oSheet:Cells( nRow, 2 ):Value := a->Employee
oSheet:Cells( nRow, 3 ):Value := a->StartDt
oSheet:Cells( nRow, 4 ):Value := a->EndDt
oSheet:Cells( nRow, 5 ):Value := a->Prevappr
oSheet:Cells( nRow, 6 ):Value := a->Design
oSheet:Cells( nRow, 7 ):Value := a->Instate
oSheet:Cells( nRow, 8 ):Value := a->OutState
oSheet:Cells( nRow, 9 ):Value := a->sponsor
oSheet:Cells( nRow, 10 ):Value := a->Event
oSheet:Cells( nRow, 11):Value := a->Location
oSheet:Cells( nRow, 12 ):Value := a->Benefits
oSheet:Cells( nRow, 13 ):Value := a->Break
oSheet:Cells( nRow, 14 ):Value := a->BreakC
oSheet:Cells( nRow, 15):Value := a->BreakT
oSheet:Cells( nRow, 16 ):Value := a->Lunch
oSheet:Cells( nRow, 17 ):Value := a->LunchC
oSheet:Cells( nRow, 18):Value := a->LunchT
oSheet:Cells( nRow, 19 ):Value := a->Dinner
oSheet:Cells( nRow, 20 ):Value := a->DinnerC
oSheet:Cells( nRow, 21 ):Value := a->DinnerT
oSheet:Cells( nRow, 22 ):Value := a->TMeals
oSheet:Cells( nRow, 23 ):Value := a->THotel
oSheet:Cells( nRow, 24 ):Value := a->NumMiles
oSheet:Cells( nRow, 25 ):Value := a->Mrate
oSheet:Cells( nRow, 26 ):Value := a->TMileage
oSheet:Cells( nRow, 27 ):Value := a->Regist
oSheet:Cells( nRow, 28 ):Value := a->Airfare
oSheet:Cells( nRow, 29 ):Value := a->MiscCost
oSheet:Cells( nRow, 30 ):Value := a->GrandTot
oSheet:Cells( nRow, 31 ):Value := a->contedcr
oSheet:Cells( nRow, 32 ):Value := a->HowMany
oSheet:Cells( nRow, 33 ):Value := a->Speech
oSheet:Cells( nRow, 34):Value := a->TT1
oSheet:Cells( nRow, 35):Value := a->TrainTp1
oSheet:Cells( nRow, 36):Value := a->TT2
oSheet:Cells( nRow, 37):Value := a->TrainTp2
oSheet:Cells( nRow, 38):Value := a->TT3
oSheet:Cells( nRow, 39):Value := a->TrainTp3
oSheet:Cells( nRow, 40):Value := a->TT4
oSheet:Cells( nRow, 41):Value := a->TrainTp4
oSheet:Cells( nRow, 42):Value := a->TT5
oSheet:Cells( nRow, 43):Value := a->TrainTp5
oSheet:Cells( nRow, 44):Value := a->TT6
oSheet:Cells( nRow, 45):Value := a->TrainTp6
oSheet:Cells( nRow, 46):Value := a->KT1
oSheet:Cells( nRow, 47):Value := a->Ktran1
oSheet:Cells( nRow, 48):Value := a->KT2
oSheet:Cells( nRow, 49):Value := a->Ktran2
oSheet:Cells( nRow, 50):Value := a->KT3
oSheet:Cells( nRow, 51):Value := a->Ktran3
oSheet:Cells( nRow, 52):Value := a->KT4
oSheet:Cells( nRow, 53):Value := a->Ktran4
oSheet:Cells( nRow, 54):Value := a->CurrFy
oSheet:Cells( nRow, 55):Value := a->CurrCont
oSheet:Cells( nRow, 56):Value := a->CurrHrs
oSheet:Cells( nRow, 57):Value := a->PrevFy
oSheet:Cells( nRow, 58):Value := a->PrevCont
oSheet:Cells( nRow, 59):Value := a->PrevHrs
oSheet:Cells( nRow, 60):Value := a->Mgr1User
oSheet:Cells( nRow, 61):Value := a->Mgr1
oSheet:Cells( nRow, 62):Value := a->Mgr1Date
oSheet:Cells( nRow, 63):Value := a->Mgr1Yes
oSheet:Cells( nRow, 64):Value := a->Mgr2User
oSheet:Cells( nRow, 65):Value := a->Mgr2
oSheet:Cells( nRow, 66):Value := a->Mgr2Date
oSheet:Cells( nRow, 67):Value := a->Mgr2Yes
oSheet:Cells( nRow, 68):Value := a->Mgr3User
oSheet:Cells( nRow, 69):Value := a->Mgr3
oSheet:Cells( nRow, 70):Value := a->Mgr3Date
oSheet:Cells( nRow, 71):Value := a->Mgr3Yes
oSheet:Cells( nRow, 72):Value := a->Mgr4User
oSheet:Cells( nRow, 73):Value := a->Mgr4
oSheet:Cells( nRow, 74):Value := a->Mgr4Date
oSheet:Cells( nRow, 75):Value := a->Mgr4Yes
oSheet:Cells( nRow, 76):Value := a->Fanswer
oSheet:Cells( nRow, 77):Value := a->Fdate
oSheet:Cells( nRow, 78):Value := a->RejectCM
oSheet:Cells( nRow, 79):Value := a->Rsby
oSheet:Cells( nRow, 80):Value := a->RsDate
oSheet:Cells( nRow, 81):Value := a->FiscalYr
oSheet:Cells( nRow, 82):Value := a->Docs
nRow++
nREC++
cSay4 := 'Generating Excel File '+STR(nREC)
oSay4:ReFresh()
SysReFresh()
Select 1
Skip
ENddo
oSheet:Columns( "A:CD" ):AutoFit()
Try
oSheet:SaveAs(cFILE )
Catch
Saying := "Sorry .. you are trying to save the file "+chr(10)
Saying += cFile+chr(10)
Saying += "to a file that may already be OPEN"+chr(10)
Saying += "or you many NOT have the Proper Network Rights"+chr(10)
Saying += "Please check to see if that is the case and re-run this report"+chr(10)
Msginfo( Saying )
oExcel:Quit()
CLOSE DATABASES
oDlg:ENd()
Return(.f.)
ENd Try
oExcel:Quit()Thanks Rick,
The code looks ok, (to be done for my level)
I will convert van exel to dbf in my case
FW_Exeltodbf will convert numbers to numbers, but there are times that numbers are (product codes) and need to be Char.
With OLE and functions like Set( "NumberFormat", '0.00' ) I can do this... looking for more samples at the moment.....
I found small samples like this, but they look more complex... We will see
AEval( DbStruct(), { |a| cFieldList += "," + if(a[2]= "C","Alltrim("+a[ 1 ]+")",a[ 1 ]) } )
FW_ExcelToDBF used TOleAuto()
nageswaragunupudi wrote:FW_ExcelToDBF used TOleAuto()
function test()
local oRange, cCurfile
cp := HB_SETCODEPAGE( "UTF8" ) // Depends on region ?
cCurFile := cGetFile( "Exel file| *.xl*| ", "Please select a file" )
oRange := GetExcelRange( cCurfile , NIL ) // Sheet1 Tab named and used insite the exel file
//msginfo( oRange:Rows( 1 ):Value )
//oRange:NumberFormat:="@"
//oRange:Cells( 5, 5 ):Value= str( Cells( 5, 5 ):Value)
// oRange:Cells( 5, 5 ):Value= cDateValue // do not use DTOC
//oRange:Columns( 1 ):NumberFormat := "@"
//oRange:Columns( 5 ):NumberFormat := "@"
//FW_ExcelToDBF( oRange, aConvert , .t. )
//oRange:Columns(5):NumberFormat:="@"
//oRange:Columns(1):NumberFormat:="@"
//oRange:Range ('E'+LTRIM(STR(1))+':E65536'):NumberFormat = '##,##,##.00'
//oRange:Range( "1:1" ):NumberFormat:= "@" // field mask
//oRange: Columns ( 1 ): Set (" NumberFormat ", "@")
//oRange:Cells( 15, 5 ):Value = "Test"
//oRange:Range( "5:100" ):NumberFormat:= "@" // field mask
//oRange:Columns( 1 ):Set( "NumberFormat", "@" ) // Text format
oRange:NumberFormat:="@"
xbrowser(oRange)
oRange:WorkSheet:Parent:Close() // close exel link
returnI think that I'm on the wrong route here ....
I can't change the data of oRange, because oRange has allready all the data when getexcelrange = used
In exel I have
Code -> become into oRange
511.53.00 511.53.00 // ok
300346 300346.00 // nok
400 400.00 // nok
My idea was that at the moment of reading the excel file that the getexcell function could make ALLWAYS a char value of any kind of data that is inside the excel. But once
oRange = created, it will convert numbers to numbers and txt to txt. I don't see a solution insite the getexcel function to change that and make a new function for this project.
I think I best look for a solution to change the excel file and make all colums as text. Tested it manualy and will work, but i have so many excel's with lots of colums that i wanted a automatic solution.
nageswaragunupudi wrote:Do you want like this?
I sent modified dbffunc2.prg to your email.
Please check and let us know.
nageswaragunupudi wrote:I sent modified dbffunc2.prg to your email.
Please check and let us know.
I am thinking of making this change in the FWH next version.