Hi @ll,
still working with TD 3.1 and would like to read an xlsx sheet into a table.
The sheet has a known range of 4 columns filled with strings.
All Excel samples under https://samples.tdcommunity.net/ made to create, write or modify data to excel but not the other way.
Looking for a working sample based on "Microsoft Excel 12.0 Object Library.apl"
Thanks in advance,
Mina
Excel Data into Table
-
Harald
- Posts: 150
- Joined: 30 Mar 2017, 06:16
- Location: Oberhaching/Munich, Germany
Re: Excel Data into Table
Hi Mina,
i have implemented a function which imports an excel file into an order some years ago. I quickly translated some messages, please remove all my file specific stuff (my file was a little bit more complex, with nSektion in this example it is possible to import a header, an address and the details) and try:
Kind regards
Harald
i have implemented a function which imports an excel file into an order some years ago. I quickly translated some messages, please remove all my file specific stuff (my file was a little bit more complex, with nSektion in this example it is possible to import a header, an address and the details) and try:
Code: Select all
!!CB!! 278
Function: fnImportEONFromExcel
Description:
Returns
Boolean:
Parameters
Number: nOption
Static Variables
Local variables
!
ComProxyVar: iExcelApplication
Class: Excel_Application
FunctionalVar: iExcelWorkBooks
Class: Excel_Workbooks
ComProxyVar: iExcelWorkBook
Class: Excel_Workbook
ComProxyVar: iExcelWorkSheet
Class: Excel_Worksheet
FunctionalVar: iExcelColumns
Class: Excel_Range
FunctionalVar: iExcelRange
Class: Excel_Range
!
FunctionalVar: vVariant
Class: Variant
FunctionalVar: vFileName
Class: Variant
FunctionalVar: vFileFormat
Class: Variant
FunctionalVar: vNULL
Class: Variant
FunctionalVar: vFALSE
Class: Variant
FunctionalVar: vSaveChanges
Class: Variant
FunctionalVar: vSave
Class: Variant
FunctionalVar: vOptional
Class: Variant
FunctionalVar: vColumns
Class: Variant
FunctionalVar: vCol
Class: Variant
FunctionalVar: vRow
Class: Variant
FunctionalVar: vValue
Class: Variant
!
FunctionalVar: vUpdateLinks
Class: Variant
FunctionalVar: vReadOnly
Class: Variant
FunctionalVar: vFormat
Class: Variant
FunctionalVar: vPassword
Class: Variant
FunctionalVar: vWriteResPassword
Class: Variant
FunctionalVar: vIgnoreReadOnlyRecommended
Class: Variant
FunctionalVar: vOrigin
Class: Variant
FunctionalVar: vDelimiter
Class: Variant
FunctionalVar: vEditable
Class: Variant
FunctionalVar: vNotify
Class: Variant
FunctionalVar: vConverter
Class: Variant
FunctionalVar: vAddToMru
Class: Variant
FunctionalVar: vLocal
Class: Variant
FunctionalVar: vCorruptLoad
Class: Variant
FunctionalVar: vRouteWorkbook
Class: Variant
!
Long String: lsBuchungstext
!
String: sColumns[140]
String: sArray[*]
String: sFileName
String: sString
String: sPath
!
String: sBestellReferenz
String: sKontakt
String: sKontaktTelefon
String: sKontaktTelefax
String: sKontaktEMail
String: sOnsiteStockLocation
String: sAddressLine1
String: sAddressLine2
String: sSTAZipCode
String: sSTACity
String: sSTACountry
String: sSTAContactName
String: sSTAContactPhoneNr
String: sSUPCompanyName
String: sSUPAddressLine1
String: sSUPAddressLine2
String: sSUPZipCode
String: sSUPCity
String: sSUPCountry
String: sSUPContactName
String: sEUCompanyName
String: sEUAddressLine1
String: sEUAddressLine2
String: sEUZipCode
String: sEUCity
String: sEUCountry
String: sEUContactName
String: sAdditionalComments
String: sDeliveryTerm
String: sLArtNr
String: sPositionsTyp
String: sArtikelbezeichnung
String: sSBONr
String: sVKSperre
!
Number: nMenge
Number: nArtNr
Number: nSAPNr
Number: nHersteller
Number: nLiefNr
Number: nKST
Number: nPG
Number: nListPreis
Number: nUplift
Number: nPreis
Number: nRabatt
Number: nEinheiten
!
Date/Time: dBestellDatum
Date/Time: dRequestedDeliveryDate
Date/Time: dVertragsbeginn
Date/Time: dVertragsende
Date/Time: dVKSperrDatum
!
Number: nSektion
Number: nIndex
Number: nZeile
Number: nSpalte
Number: nSpalte2
Number: nSpalteOffset
Number: nSpalteItemNr
Number: nSpalteMenge
Number: nSpalteLArtNr
Number: nNextPosNr
Number: nAuftragsart
Number: nAnzahl
Number: nArrayLen
Number: nID
Number: nErr
!
String: sMessages
!
Boolean: bReturn
Boolean: bBTO
Boolean: bTextKonserve
Boolean: bFileOpened
Boolean: bHeaderTakenOver
!
Actions
!
If nBerechtigungSalesOrg & cAUFBerAUFErfassen = 0
!
Return FALSE
!
!
Call SalListSelectString( cmbAuftragsart, -1, 'ZZOR' )
!
If SalIsNull( cmbAuftragsart )
!
Call AdvSalMBFehler( 'Please select a order type first!' )
!
Call SalSetFocus( cmbAuftragsart )
!
Return FALSE
!
!
If cmbAuftragsart = 'ZZMR'
Set nAuftragsart = 0
Else If cmbAuftragsart = 'ZZSB'
Set nAuftragsart = 1
Else
Set nAuftragsart = 2
!
If nRecId = 0
!
If fnAskForSaveHeader( ) = FALSE
!
Return FALSE
!
!
Else
!
If bPositionIsModified = TRUE
!
Call fnSavePosition( FALSE )
!
!
!
If fnGetExcelFileName( hWndForm, sPath, sFileName ) = FALSE
!
Return FALSE
!
!
! Set sFileName = 'D:\\E-SD009020591.xls'
!
Call vFileName.SetString( sFileName )
Call vSaveChanges.SetNumber( 0, VT_I2 )
!
Call SalActiveXAutoErrorMode( TRUE )
!
Call SalWaitCursor( TRUE )
!
If iExcelApplication = OBJ_Null
!
Set iExcelApplication = new Excel__Application
!
!
If NOT SalActiveXGetActiveObject( iExcelApplication, 'Excel.Application' )
!
If NOT iExcelApplication.Create( )
!
Call SalWaitCursor( FALSE )
!
Call AdvSalMBWarnung( 'Excel could not be started!' )
!
Return FALSE
!
!
!
Set bDummy = TRUE
!
If TRUE
!
If iExcelApplication.PropGetWorkbooks( iExcelWorkBooks ) = FALSE
!
If NOT iExcelApplication.Create( )
!
Call SalWaitCursor( FALSE )
!
Call AdvSalMBWarnung( 'Excel could not be started!' )
!
Return FALSE
!
!
Call iExcelApplication.PropGetWorkbooks( iExcelWorkBooks )
!
!
Call vUpdateLinks.MakeOptional( )
Call vReadOnly.MakeOptional( )
Call vFormat.MakeOptional( )
Call vPassword.MakeOptional( )
Call vWriteResPassword.MakeOptional( )
Call vIgnoreReadOnlyRecommended.MakeOptional( )
Call vOrigin.MakeOptional( )
Call vDelimiter.MakeOptional( )
Call vEditable.MakeOptional( )
Call vNotify.MakeOptional( )
Call vConverter.MakeOptional( )
Call vAddToMru.MakeOptional( )
Call vLocal.MakeOptional( )
Call vCorruptLoad.MakeOptional( )
!
If iExcelWorkBooks.Open( sFileName, vUpdateLinks, vReadOnly, vFormat, vPassword, vWriteResPassword, vIgnoreReadOnlyRecommended, vOrigin, vDelimiter, vEditable, vNotify, vConverter, vAddToMru, vLocal, vCorruptLoad, iExcelWorkBook )
!
Set bFileOpened = TRUE
!
!
!
Call SalWaitCursor( FALSE )
!
Set rbVWWare = TRUE
!
If bFileOpened = TRUE
!
If NOT iExcelApplication.PropSetVisible( TRUE )
!
! If NOT iExcelApplication.Create( )
!
Call AdvSalMBWarnung( 'Excel could not be started!' )
!
Return FALSE
!
! If NOT iExcelApplication.PropSetVisible( TRUE )
!
Return FALSE
!
!
!
If iExcelWorkBook.PropGetActiveSheet( iExcelWorkSheet )
!
If iExcelWorkSheet.PropGetCells( iExcelRange )
!
Set nZeile = 1
!
Set nSpalteItemNr = 2
Set nSpalteMenge = 3
Set nSpalteLArtNr = 4
!
If nRecId = 0
!
Call fnSaveHeader( )
!
!
While nZeile < 10000 AND nSektion < 99
!
Call vRow.SetNumber( nZeile, VT_I4 )
!
Set nSpalte = 1
!
If nSektion < 5
!
While nSpalte < 17
!
Call vCol.SetNumber( nSpalte, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If sString != ''
!
Set sString = SalStrTrimX( sString )
!
If nSektion = 0
!
If sString = 'HP Enterprise Service Shipping Request for Stock Hardware'
!
Set nSektion = 1
!
!
Else If nSektion = 1
!
If sString = 'BLN Ref Number'
!
Call iExcelRange.PropGetMergeCells( vValue )
!
Set bDummy = vValue.GetNumber( nDummy )
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sBestellReferenz = sString
!
If fnCheckBestellreferenz()
!
Call AdvSalMBWarnung( 'Diese Bestellreferenz ' || sBestellReferenz || ' gibt es bereits bei dem Kunden!' )
!
!
!
!
Else If sString = 'Request Date'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set dBestellDatum = SalStrToDate( sString )
!
!
!
Else If sString = 'Contact'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sKontakt = sString
!
!
!
Else If sString = 'Telephone'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sKontaktTelefon = sString
!
!
!
Else If sString = 'Telefax:'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sKontaktTelefax = sString
!
!
!
Else If sString = 'e-Mail:'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sKontaktEMail = sString
!
!
!
Else If sString = 'Ship to Address'
!
Set nSektion = 2
!
!
Else If nSektion = 2
!
If sString = 'Onsite Stock location'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sOnsiteStockLocation = sString
!
!
!
Else If sString = 'Address Line 1'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sAddressLine1 = sString
!
!
!
Else If sString = 'Address Line 2'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sAddressLine2 = sString
!
!
!
Else If sString = 'Zip Code'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sSTAZipCode = sString
!
!
!
Else If sString = 'City'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sSTACity = sString
!
!
!
Else If sString = 'Country'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sSTACountry = sString
!
!
!
Else If sString = 'Contact Name'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sSTAContactName = sString
!
!
!
Else If sString = 'Contact Phone#'
!
Call vCol.SetNumber( nSpalte + 2, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sSTAContactPhoneNr = sString
!
!
!
Else If sString = 'Supplier'
!
Set nSektion = 3
!
!
Else If nSektion = 3
!
If nSpalte < nSpalte2
!
Set nSpalteOffset = 3
!
Else
!
Set nSpalteOffset = 3
!
!
If sString = 'End User Data'
!
Set nSpalte2 = nSpalte
!
Else If sString = 'Company Name'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPCompanyName = sString
!
Else
!
Set sEUCompanyName = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Address Line 1'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPAddressLine1 = sString
!
Else
!
Set sEUAddressLine1 = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Address Line 2'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPAddressLine2 = sString
!
Else
!
Set sEUAddressLine2 = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Zip Code'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPZipCode = sString
!
Else
!
Set sEUZipCode = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'City'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPCity = sString
!
Else
!
Set sEUCity = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Country'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPCountry = sString
!
Else
!
Set sEUCountry = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Contact Name'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If nSpalte < nSpalte2
!
Set sSUPContactName = sString
!
Else
!
Set sEUContactName = sString
!
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Requested Delivery Date'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set dRequestedDeliveryDate = SalStrToDate( sString )
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Delivery Term'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sDeliveryTerm = sString
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Additional Comments'
!
Call vCol.SetNumber( nSpalte + nSpalteOffset, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
Set sAdditionalComments = sString
!
Set nSpalte = nSpalte + nSpalteOffset
!
!
!
Else If sString = 'Item'
!
Set nSpalteItemNr = nSpalte
!
Set nSektion = 4
!
!
Else If nSektion = 4
!
If sString = 'Qty'
!
Set nSpalteMenge = nSpalte
!
Else If sString = 'Product Number'
!
Set nSpalteLArtNr = nSpalte
!
Set nZeile = nZeile + 1
!
Set nSektion = 5
!
!
!
!
!
!
Set nSpalte = nSpalte + 1
!
!
Else
!
If bHeaderTakenOver = FALSE
!
If dBestellDatum = DATETIME_Null
!
Set dBestellDatum = SalDateCurrent( )
!
If dRequestedDeliveryDate = DATETIME_Null OR dRequestedDeliveryDate < SalDateCurrent( ) + 1
!
Set dRequestedDeliveryDate = SalDateCurrent( ) + 1
!
!
Set dfBestellreferenz = sBestellReferenz
Set dfBestellername = sKontakt
Set dfEMailBesteller = sKontaktEMail
Set dfBestelldatum = dBestellDatum
Set dfWunschlieferdatum = dRequestedDeliveryDate
Set dfAuslieferDatum = dRequestedDeliveryDate
!
If sSTAContactPhoneNr != ''
!
Set sSTAContactName = sSTAContactName || ' ' || sSTAContactPhoneNr
!
!
Set dfWEName1 = sAddressLine1
Set dfWEName2 = sSTAContactName
Set dfWEName3 = sBestellReferenz
Set dfWEPLZ = sSTAZipCode
Set dfWEOrt = sSTACity
Set dfWEStrasse = sAddressLine2
!
If sSTACountry != 'Germany'
!
Call SalListSelectString( cmbWELand, 0, SalStrLeftX( sSTACountry, 2 ) )
!
!
Set bHeaderTakenOver = TRUE
!
If nNumberOfPositions = 0
!
Set nNextPosNr = nSCBuchungsschritte
!
Else
!
Set nSelectedPosition = nNumberOfPositions - 1
!
Set nNextPosNr = SalStrToNumber( lvPositionen.GetItemText( nSelectedPosition, nLVPOSPosition ) ) + nSCBuchungsschritte
!
!
!
Set sPositionsTyp = 'ZFOC'
!
!
Call vCol.SetNumber( nSpalteItemNr, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If sString != ''
!
!
Else
!
Set nSektion = 99
!
Break
!
!
!
!
Call vCol.SetNumber( nSpalteMenge, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If sString != ''
!
Set nMenge = SalStrToNumber( sString )
!
Call vCol.SetNumber( nSpalteLArtNr, VT_I4 )
!
If iExcelRange.PropGetItem( vRow, vCol, vValue )
!
If vValue.GetString( sString ) = TRUE
!
If sString != ''
!
Set sLArtNr = sString
!
Call SqlPrepareAndExecute( hSqlA,
'SELECT a.ArtNr, a.SAPNr, a.StdLiefNr, a.HerstellerId, a.Artikelbezeichnung, a.KST, a.PG, a.VKSperre, a.VKSperrDatum, w.LP, w.HEK
FROM db2inst1.Art_Artikel a, TABLE(db2inst1.GetProductInformation(a.Artnr, a.SAPNr)) AS w
WHERE LArtNr = :sLArtNr WITH UR FOR READ ONLY
INTO :nArtNr, :nSAPNr, :nLiefNr, :nHersteller, :sArtikelbezeichnung, :nKST, :nPG, :sVKSperre, :dVKSperrDatum, :nListPreis, :nPreis ' )
!
If SqlFetchNext( hSqlA, nFetchErr ) = TRUE
!
If ( sVKSperre = '98' OR sVKSperre = '99' ) AND dVKSperrDatum >= AdvSalDateTruncate( SalDateCurrent( ) )
!
Set sMessages = sMessages || 'Der Artikel ' || itos( nSAPNr ) || ' ' || sLArtNr || ' ist verkaufsseitig ' || sVKSperre || ' gesperrt!' || cCRLF
!
!
If nListPreis <= 0.01
!
Select Case nAuftragsart
Case 0
Set sPositionsTyp = 'ZFCM'
Break
Case 1
Set sPositionsTyp = 'ZFCB'
Break
Case 2
Set sPositionsTyp = 'ZFOC'
Break
!
Else
!
Select Case nAuftragsart
Case 0
Set sPositionsTyp = 'ZZMR'
Break
Case 1
Set sPositionsTyp = 'ZZSB'
Break
Case 2
Set sPositionsTyp = 'ZSTD'
Break
!
!
Set nRabatt = AdvSalCalcRabatt( nListPreis, nPreis )
!
Set nSelectedPosRecId = 0
! Set nSelectedPosition = -1
!
Call __fnSavePosition( nNextPosNr, 0, sPositionsTyp, 0, 0, nArtNr, nSAPNr, nHersteller, nLiefNr, sLArtNr, sArtikelbezeichnung, nKST, nPG,
nMenge, nListPreis, nUplift, nPreis, nRabatt, nPreis, 0, 0, 0, 0, nEinheiten, dVertragsbeginn, dVertragsende, NUMBER_Null,
sSBONr, FALSE, STRING_Null, STRING_Null, NUMBER_Null, NUMBER_Null, NUMBER_Null, NUMBER_Null, NUMBER_Null, NUMBER_Null )
!
Set nNextPosNr = nNextPosNr + nSCBuchungsschritte
!
Else
!
Set sMessages = sMessages || 'Der Artikel ' || sLArtNr || ' konnte nicht gefunden werden!' || cCRLF
!
!
!
!
!
!
!
!
!
Set nZeile = nZeile + 1
!
!
Call fnErmittleAuftragsWert( )
!
!
Else
!
Call AdvSalMBHinweis( 'PropGetActiveSheet does not work' )
!
!
Call fnSaveHeader( )
!
Call SalBringWindowToTop( USR_AUFAuftragserfassung )
!
If sMessages != ''
Call AdvSalMBWarnung( 'Following errors occured:
' || sMessages )
!
Else
!
Call AdvSalMBWarnung( 'Excel could not be started!' )
!
!
Return TRUE
!
Harald
-
mina
- Posts: 14
- Joined: 21 Apr 2018, 07:36
- Location: Finland
Re: Excel Data into Table
Harald,
That's really an extensive piece of code!
You use nSektion to choose parts from the sheet - right? As from an invoice Sender Data, Recipient Data, Invoice No, Order Positions etc.
But how did you define sections in an xlsx?
BR
Mina
That's really an extensive piece of code!
You use nSektion to choose parts from the sheet - right? As from an invoice Sender Data, Recipient Data, Invoice No, Order Positions etc.
But how did you define sections in an xlsx?
BR
Mina
-
Harald
- Posts: 150
- Joined: 30 Mar 2017, 06:16
- Location: Oberhaching/Munich, Germany
Re: Excel Data into Table
Hello Mina,
yes, that's correct. The sections are defined by keywords. If the scan starts, nSektion is 0. If any cell in a row contains 'HP Enterprise Service Shipping Request for Stock Hardware', the function knows that the order header (nSektion = 1) will follow in the next rows. In the order header, the function searches for a cell with 'BLN Ref Number', and if this text is found, it's expected that this reference number is in the second cell on the right, and so on.
To make it easier for you, I created a small code snipet which shows you how to read the cells from an excel sheet:
Kind regards
Harald
yes, that's correct. The sections are defined by keywords. If the scan starts, nSektion is 0. If any cell in a row contains 'HP Enterprise Service Shipping Request for Stock Hardware', the function knows that the order header (nSektion = 1) will follow in the next rows. In the order header, the function searches for a cell with 'BLN Ref Number', and if this text is found, it's expected that this reference number is in the second cell on the right, and so on.
To make it easier for you, I created a small code snipet which shows you how to read the cells from an excel sheet:
Code: Select all
If fnGetExcelFileName( hWndForm, sPath, sFileName ) = FALSE
Return FALSE
! Set sFileName = 'D:\\E-SD009020591.xls'
Call vFileName.SetString( sFileName )
Call vSaveChanges.SetNumber( 0, VT_I2 )
Call SalActiveXAutoErrorMode( TRUE )
If iExcelApplication = OBJ_Null
Set iExcelApplication = new Excel__Application
If NOT SalActiveXGetActiveObject( iExcelApplication, 'Excel.Application' )
If NOT iExcelApplication.Create( )
Call AdvSalMBWarnung( 'Excel could not be started!' )
Return FALSE
If iExcelApplication.PropGetWorkbooks( iExcelWorkBooks ) = FALSE
If NOT iExcelApplication.Create( )
Call AdvSalMBWarnung( 'Excel could not be started!' )
Return FALSE
Call iExcelApplication.PropGetWorkbooks( iExcelWorkBooks )
Call vUpdateLinks.MakeOptional( )
Call vReadOnly.MakeOptional( )
Call vFormat.MakeOptional( )
Call vPassword.MakeOptional( )
Call vWriteResPassword.MakeOptional( )
Call vIgnoreReadOnlyRecommended.MakeOptional( )
Call vOrigin.MakeOptional( )
Call vDelimiter.MakeOptional( )
Call vEditable.MakeOptional( )
Call vNotify.MakeOptional( )
Call vConverter.MakeOptional( )
Call vAddToMru.MakeOptional( )
Call vLocal.MakeOptional( )
Call vCorruptLoad.MakeOptional( )
If iExcelWorkBooks.Open( sFileName, vUpdateLinks, vReadOnly, vFormat, vPassword, vWriteResPassword, vIgnoreReadOnlyRecommended, vOrigin, vDelimiter, vEditable, vNotify, vConverter, vAddToMru, vLocal, vCorruptLoad, iExcelWorkBook )
Set bFileOpened = TRUE
If NOT iExcelApplication.PropSetVisible( TRUE )
Call AdvSalMBWarnung( 'Excel could not be started!' )
...
If iExcelWorkBook.PropGetActiveSheet( iExcelWorkSheet )
If iExcelWorkSheet.PropGetCells( iExcelRange )
While nRow < 10000
Call vRow.SetNumber( nRow , VT_I4 )
While nColumn < 100
Call vCol.SetNumber( nColumn , VT_I4 )
If iExcelRange.PropGetItem( vRow, vCol, vValue )
If vValue.GetString( sString ) = TRUE
Set sCell = sString
Set nColumn = nColumn + 1
Set nRow = nRow + 1
! Excel will still be open
Return TRUE
Harald
-
mina
- Posts: 14
- Joined: 21 Apr 2018, 07:36
- Location: Finland
Re: Excel Data into Table
Dear Harald,
your code worked like a charm!
I had to initialize nRow and nColumn in the loop as shown below The next step will be a SalTblInsertRow and the "Read from Excel" can be completed.
Just wondering if there is a better way than PropGetUsedRange/PropGetRows to get the real count of filled rows
because the above functions counts also deleted entries.
Thank you, once again for your great support and assistance!
Regards,
Mina
your code worked like a charm!
I had to initialize nRow and nColumn in the loop as shown below The next step will be a SalTblInsertRow and the "Read from Excel" can be completed.
Just wondering if there is a better way than PropGetUsedRange/PropGetRows to get the real count of filled rows
because the above functions counts also deleted entries.
Thank you, once again for your great support and assistance!
Regards,
Mina
You do not have the required permissions to view the files attached to this post.
Who is online
Users browsing this forum: [Ccbot] and 0 guests