- SAP Managed Tags
Hello there.
Is it possible to work with ABAP2XLSX and XLSM (macro-enabled) files?
I’m trying to migrate some legacy developments that currently use OLE2 objects to ABAP2XLSX, but I’m running into issues.
We have a solution where a macro-enabled Excel template (stored on the application server) is downloaded by the program, filled in with case data, and then sent to a supplier to complete.
The template contains several VBA-based validations — for example, a cell must be filled if certain other cells are completed, verifying that an entered EAN code is valid, and so on.
I’m trying to achieve the same behavior using ABAP2XLSX, but I’m getting errors when opening the generated file.
I’ve created a simplified example of what I’m trying to do — Excel file includes a small macro, and with the ABAP code I’m attempting to fill it in and then download it again.
(For the Excel validations to work, you first need to fill in cell B2.)
Do you know if this is possible with ABAP2XLSX?
REPORT ztest.
PARAMETERS:
pa_banfn TYPE banfn OBLIGATORY.
START-OF-SELECTION.
DATA:
lv_rc TYPE i,
lv_filename TYPE char1024,
lv_filename_down TYPE string,
lt_filetable TYPE STANDARD TABLE OF file_table.
SELECT eban~matnr, maktx
INTO TABLE @DATA(lt_eban)
FROM eban
INNER JOIN makt
ON eban~matnr EQ makt~matnr
AND makt~spras EQ @SY-langu
WHERE banfn EQ @PA_banfn
AND loekz EQ @Space.
IF sy-subrc NE 0.
MESSAGE 'Expedient doesnt exists' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
* window_title = lv_titulo
file_filter = '(*.xlsm)|*.xlsm|'
initial_directory = 'U:\'
CHANGING
file_table = lt_filetable
rc = lv_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc <> 0.
RETURN.
ENDIF.
READ TABLE lt_filetable
INTO lv_filename
INDEX 1.
IF sy-subrc NE 0.
RETURN.
ENDIF.
CALL FUNCTION 'SO_SPLIT_FILE_AND_PATH'
EXPORTING
full_name = lv_filename
IMPORTING
* STRIPPED_NAME =
file_path = lv_filename_down
EXCEPTIONS
x_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
RETURN.
ENDIF.
CONCATENATE lv_filename_down sy-datum sy-uzeit '.xlsm' INTO lv_filename_down.
DATA(lo_reader) = CAST zif_excel_reader( NEW zcl_excel_reader_2007( ) ).
DATA(lo_excel) = lo_reader->load_file( lv_filename ).
DATA(lo_worksheet) = lo_excel->get_worksheet_by_index( 1 ).
TRY.
CALL METHOD lo_worksheet->set_table
EXPORTING
ip_table = lt_eban
* ip_hdr_style =
* ip_body_style =
ip_table_title = space
ip_top_left_column = 'A'
ip_top_left_row = 5
* ip_transpose =
ip_no_header = abap_true.
CATCH zcx_excel .
RETURN.
ENDTRY.
lo_worksheet->set_cell( ip_row = 1
ip_column = 'B'
ip_value = pa_banfn ).
*--------------------------------------------------------------------*
* Save file
*--------------------------------------------------------------------*
DATA:
lv_xdata TYPE xstring,
lt_rawdata TYPE solix_tab,
lv_bytecount TYPE i,
cl_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
lv_xdata = cl_writer->write_file( lo_excel ).
lt_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring = lv_xdata ).
lv_bytecount = xstrlen( lv_xdata ).
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
bin_filesize = lv_bytecount
filename = lv_filename_down
filetype = 'BIN'
CHANGING
data_tab = lt_rawdata
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
not_supported_by_gui = 22
error_no_gui = 23
OTHERS = 24.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
DISPLAY LIKE 'E'.
ELSE.
MESSAGE 'Done' TYPE 'S'.
ENDIF.
The excel file has this content:
And a macro with this code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim ean As String
Dim msg As String
Dim invalid As Boolean
Set ws = ThisWorkbook.Sheets("Prices")
nif = Trim(ws.Range("B2").Value)
If nif = "" Then Exit Sub
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
invalid = False
msg = ""
' Recorre desde la fila 5 en adelante
For i = 5 To lastRow
Dim mat As String, price As Variant
mat = Trim(ws.Cells(i, "A").Value)
price = ws.Cells(i, "C").Value
ean = Trim(ws.Cells(i, "D").Value)
' Si hay material, el precio debe estar informado
If mat <> "" And (price = "" Or Not IsNumeric(price)) Then
msg = msg & vbCrLf & "Fila " & i & ": Falta precio para el material """ & mat & """"
invalid = True
End If
' Si hay EAN, validar formato y dígito de control
If ean <> "" Then
If Not IsValidEAN(ean) Then
msg = msg & vbCrLf & "Fila " & i & ": Código EAN """ & ean & """ no válido"
invalid = True
End If
End If
Next i
If invalid Then
MsgBox "Se encontraron errores:" & vbCrLf & msg, vbExclamation, "Validación de datos"
Cancel = True ' Evita guardar si hay errores
Else
MsgBox "Validación correcta. Guardando archivo...", vbInformation, "Validación de datos"
End If
End Sub
Private Function IsValidEAN(ean As String) As Boolean
Dim sum As Long, checkDigit As Integer, i As Integer
Dim digits() As String
ean = Replace(ean, " ", "")
If Not ean Like String(Len(ean), "#") Then
IsValidEAN = False
Exit Function
End If
Select Case Len(ean)
Case 8, 13
ReDim digits(1 To Len(ean))
For i = 1 To Len(ean)
digits(i) = Mid(ean, i, 1)
Next i
sum = 0
For i = Len(ean) - 1 To 1 Step -1
If (Len(ean) - i) Mod 2 = IIf(Len(ean) = 13, 0, 1) Then
sum = sum + digits(i) * 3
Else
sum = sum + digits(i)
End If
Next i
checkDigit = (10 - (sum Mod 10)) Mod 10
IsValidEAN = (checkDigit = digits(Len(ean)))
Case Else
IsValidEAN = False
End Select
End FunctionThanks in advance
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.