TOleAuto () versus FW_ExcelToDBF()

[Marc Venken]

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.

[Rick Lipkin]

Marc

Here is a fairly complicated .dbf to excel file code ....

Code: Select all  Expand view

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()

Rick Lipkin

[Marc Venken]

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 ]) } )

[nageswaragunupudi]

FW_ExcelToDBF used TOleAuto()

[Marc Venken]

FW_ExcelToDBF used TOleAuto()

I tried most things I found, but the numbers stay numbers....

Can someone please use this simple function and see if the numbers in the opened exel are shown as char values by the browse ?

In my case all numbers have a .00 added and that is a problem for the colomns that are used as reference codes.

Code: Select all  Expand view

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

return

 

[Marc Venken]

I 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]

Do you want like this?

[Marc Venken]

Do you want like this?

Yes please...

[nageswaragunupudi]

I sent modified dbffunc2.prg to your email.
Please check and let us know.

[Marc Venken]

I sent modified dbffunc2.prg to your email.
Please check and let us know.

Yes !! I tested some files and they work as aspected.. Thank you very much. The newly added function gives me also the option to format more specific data when converting... Great.

For compatability (I have full and clean FW/updates) : is it better to rename the changed functions and put these insite my program, since it is used probably only for that program ?

[nageswaragunupudi]

I am thinking of making this change in the FWH next version.