Excel Data into Table

Post your tools and samples or ask for them.
mina
Finland
Posts: 14
Joined: 21 Apr 2018, 07:36
Location: Finland

Excel Data into Table

Post by mina » 26 Nov 2020, 08:31

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

Harald
Germany
Posts: 150
Joined: 30 Mar 2017, 06:16
Location: Oberhaching/Munich, Germany

Re: Excel Data into Table

Post by Harald » 26 Nov 2020, 09:10

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:

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
		!
Kind regards
Harald

mina
Finland
Posts: 14
Joined: 21 Apr 2018, 07:36
Location: Finland

Re: Excel Data into Table

Post by mina » 26 Nov 2020, 15:29

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

Harald
Germany
Posts: 150
Joined: 30 Mar 2017, 06:16
Location: Oberhaching/Munich, Germany

Re: Excel Data into Table

Post by Harald » 26 Nov 2020, 16:52

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:

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
Kind regards
Harald

mina
Finland
Posts: 14
Joined: 21 Apr 2018, 07:36
Location: Finland

Re: Excel Data into Table

Post by mina » 27 Nov 2020, 14:00

Dear Harald,
your code worked like a charm!

I had to initialize nRow and nColumn in the loop as shown below
code.jpg
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.

Return to “Tools & Samples”

Who is online

Users browsing this forum: [Ccbot] and 0 guests