WS_ or GUI_ download will not do the job that you want to do. If you want to display the spreadsheet as it is created from SAP you need to use OLE to do this.
There are two methods that you can use. The first downloads the data to the spreadsheet, and then the user has to open the spreadsheet themselves and this goes something like:
Code:
Form Upload_Table using pu_file like rlgrap-filename
changing pc_uploaded.
*
Constants c_start_col type i value 3. " Start column for data
" in the spread sheet.
Data: t_worksheets type standard table of Worksheet_Names
with header line
Initial Size 5,
t_fieldNames Type standard table of FieldNames,
t_fieldvals Type standard table of Cell_Values,
*
w_fieldname Type Cell_Values,
w_value Type Cell_Values,
w_lastcol Type Cell_Values,
Excel Type Ole2_Object,
W_Book Type Ole2_Object,
w_Sheet Type Ole2_Object,
w_row Type I,
w_column Type I,
w_maxcols Type I,
w_text Type t100-Text,
w_text1 Type t100-Text.
Move False to pc_uploaded.
*
* Work sheets to upload.
*
Move 'Stockable MM01a' to t_worksheets-SheetName.
Append t_worksheets.
* Move 'Purchased Services MM01b' to t_worksheets-SheetName.
* Append t_worksheets.
Move 'Sold Services MM01b' to t_worksheets-SheetName.
Append t_worksheets.
* Move 'Consumables MM01c' to t_worksheets-SheetName.
* Append t_worksheets.
* Move 'RRB MM01e' to t_worksheets-SheetName.
* Append t_worksheets.
*
* Open the workbook
*
Create Object Excel 'EXCEL.APPLICATION'.
Call Method Of Excel 'WORKBOOKS' = W_Book.
Call Method Of W_Book 'OPEN' EXPORTING #1 = Pu_File.
If sy-subrc = 0.
*
* Select the 5 work books in turn
*
Loop at t_worksheets.
Call Method Of Excel
'Worksheets' = w_Sheet
Exporting #1 = t_worksheets-SheetName.
Call Method Of W_sheet 'Activate'.
If sy-subrc = 0.
*
* Get the field names in this sheet.
*
Move 4 to w_row.
Move c_start_col to w_column.
Move '***' to w_lastcol.
Zap t_fieldnames.
Concatenate 'Getting field names for '
t_worksheets-Sheetname
into w_text separated by ' '.
Call Function 'SAPGUI_PROGRESS_INDICATOR'
Exporting
Percentage = 0
Text = w_text.
*
* Go along the columns until we have two blank
* columns side by side.
*
Do.
Perform GetCellValues using Excel w_row w_column
changing w_fieldname.
To find additional functions, record what you want to do in the Excel macro record. That gives you the relevant methods and properties to use.
The second method you can use is to display the Excel spreadsheet inside a container in an SAP window. This gives a seamless feel to the process but can cause some major headaches - one of which is that there has to be a spreadsheet template - you cannot create the sheet from scratch.
The process below is for 4.0b.
In transaction SMW0, Select Binary data and then click search. Enter developement class SOFFICEINTEGRATION.
Select Settings->Maintain Mimetypes. If there is no mime type for 'application/ms-excel', create one with a file extention of *.xls.
Save that and then create a spreadsheet template. The easiest way I have found is to create an empty spreadsheet with a series of macros.
For example, the code below is a macro from one of my sheets that formats a 'header' for a spreadsheet:
Code:
ub Format_Header(Company As String, Date_Start As Variant, Date_End As Variant, Business_Areas As String)
'*
ReDim Ba_List(0) As String
Dim Ba_Temp As String
Dim Ba_Count As Integer
Dim Pos As Integer
Dim Last_Col As String
Dim Title_Range As String
'*
Call Set_Columns
'*
'* Calculate the last column to be used by counting the number of business
'* areas passed to the routine
'*
Ba_Temp = Business_Areas
'*
'* A list seperated with exclamation marks
'*
While Ba_Temp <> ""
Pos = InStr(Ba_Temp, "!")
If Pos > 0 Then
Ba_List(Ba_Count) = Left(Ba_Temp, Pos - 1)
Ba_Temp = Mid(Ba_Temp, Pos + 1)
Ba_Count = Ba_Count + 1
ReDim Preserve Ba_List(Ba_Count) As String
Else
Ba_List(Ba_Count) = Ba_Temp
Ba_Temp = ""
End If
Wend
'*
'* Spreadsheet width is Column d+Columns(2*Ba_Count+1)
'* This assumes that the columns do not go past column Z
'*
Last_Col = Chr$(Asc("D") + (2 * (Ba_Count + 1)))
'*
'* Company Name
'*
Title_Range = "B2:" & Last_Col & "2"
Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
Range("B2").Value = Company
'*
'* Title.....
'*
Title_Range = "B4:" & Last_Col & "4"
Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
Range("B4").Value = "DRAFT MANAGEMENT ACCOUNTS FOR THE PERIOD " & Date_Start & " TO " & Date_End
'*
'* Business area titles. These start at column C with the totals
'*
Call Format_Cell(Range("C6"), C_Text_Format, White, Black, xlCenter)
Range("C6").Value = "Total"
Call Format_Cell(Range("D6"), C_Text_Format, White, Black, xlCenter)
Range("D6").Value = "0/0"
For Ba_Count = 0 To UBound(Ba_List)
Title_Range = Chr$(Asc("E") + (2 * Ba_Count)) & "6"
Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
Range(Title_Range).Value = Ba_List(Ba_Count)
Title_Range = Chr$(Asc("E") + ((2 * Ba_Count) + 1)) & "6"
Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
Range(Title_Range).Value = "0/0"
Next Ba_Count
End Sub
This does the work of formatting and entering data passed by SAP into a blank spreadsheet.
Then, in your ABAP, you must create the container and call the various macros:
Code:
Form Create_Spreadsheet.
*
Data: w_retcode type t_oi_ret_string,
w_doc_type type soi_document_type,
w_url(256) type c.
*
* Create the spreadsheet
*
Set Screen 0110.
If g_Excel is Initial.
Perform Create_Container Using 'Y_DRAFT_ACCOUNTS' '0110' 0
Changing g_Excel w_RetCode.
If w_RetCode <> c_oi_errors=>Ret_Ok.
Message I000 with Text-069.
Else.
*
* Set the document type for the container
*
Call Method g_Excel->Get_Document_Proxy
Exporting Document_Type = 'Excel.Sheet.8'
Importing Document_proxy = g_document
RetCode = w_RetCode.
If w_RetCode <> c_oi_errors=>Ret_Ok.
Message I000 with Text-071.
EndIf.
EndIf.
EndIf.
*
* Continue ??
*
If w_RetCode = c_oi_errors=>Ret_Ok.
Call function 'SAP_OI_SEND_MIME_DATA'
exporting
object_id = 'DRAFTS'
changing
url = w_url
exceptions
object_not_found = 1
internal_error = 2
dp_invalid_parameter = 3
dp_error_put_table = 4
dp_error_general = 5
others = 6.
Call method g_document->open_document
exporting document_url = w_url
open_inplace = 'X'
receiving retcode = w_retcode.
Call method g_document->update_document_links
receiving retcode = w_retcode.
Perform Excel_Header changing w_retcode.
EndIf.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Create_Container
*
* Purpose: Creates an Office automation container
* object.
*
* Entry: Program id window to show control in
* Entry: Dynpro number of window
* Entry: Window Top position
*
* Exit: Container Object.
* Return Code
* If window details provided, window
* is opened.
*
* Called By: Perform Create_contai using w_doc_type
* changing w_object.
* Calls:
*
* Modification History:
*
Form Create_Container using pu_repid like sy-repid
pu_dynpro_nr like sy-dynnr
pu_window_top type i
changing pc_object type ref to
i_oi_ole_container_control
pc_RetCode type t_oi_ret_string.
*
Data: w_retcode type t_oi_ret_string,
w_Shell_Style type I,
w_Align type I,
w_Inplace_Mode type I.
*
Move c_oi_errors=>Ret_Ok to w_RetCode.
Call Function 'CONTROL_INIT' Exceptions Control_Init_Error = 1
Others = 2.
If Sy-subrc = 0.
Call Method C_Oi_Ole_Control_Creator=>Get_Ole_Container_Control
Importing Control = pc_Object
Retcode = w_Retcode.
If w_RetCode = c_oi_errors=>Ret_Ok.
w_Shell_style = ws_visible + ws_child + ws_border
+ ws_clipchildren.
w_Inplace_Mode = Pc_Object->Inplace_Mode_Enabled +
Pc_Object->Inplace_Mode_Scroll.
Call Method Pc_Object->Init_Control
Exporting R3_Application_Name = 'R/3 Basis'
Inplace_Mode = w_Inplace_Mode
Shell_Style = w_Shell_Style
Register_On_Close_Event = 'X'
Register_On_Custom_Event = 'X'
Receiving Retcode = w_Retcode.
Endif.
If w_RetCode = c_oi_errors=>Ret_Ok.
w_Align = Align_At_Right + Align_At_Bottom.
Call Method Pc_Object->Set_Window_Properties
Exporting Top = pu_Window_Top
Align = w_Align.
EndIf.
If w_RetCode = c_oi_errors=>Ret_Ok.
If pu_RepId <> ''.
Call Method Pc_Object->Link_Control
Exporting Rep_Id = pu_Repid
Dynpro_nr = pu_dynpro_nr
Receiving RetCode = w_Retcode.
EndIf.
EndIf.
If w_RetCode = c_oi_errors=>Ret_Ok.
Call method pc_object->get_link_server
importing link_server = g_link_server
retcode = w_retcode.
EndIf.
If w_RetCode = c_oi_errors=>Ret_Ok.
Call method g_link_server->start_link_server
receiving retcode = w_retcode.
EndIf.
EndIf.
pc_retcode = w_Retcode.
EndForm.
*&---------------------------------------------------------------------*
*& Module STATUS_0110 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
Module Status_0110 Output.
Set PF-status 'MAIN0110'.
Set TitleBar '001'.
EndModule.
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0110 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
Module USER_COMMAND_0110 Input.
*
Data: w_Retcode type t_oi_ret_string.
*
Case sy-ucomm.
When 'EXIT'.
Perform Exit_Spreadsheet changing w_retcode.
EndCase.
EndModule.
*Eject
**********************************************************************
*
* Procedure: Excel_Header
*
* Purpose: Formats argument to excel macro
* format_header and then calls the
* macro to format the spreadsheet
*
* Entry: Global Objects
*
* Exit: Return Code
*
* Called By: Perform Excel_Header
* changing w_status.
* Calls:
*
* Modification History:
*
Form Excel_Header Changing pc_retcode type t_oi_ret_string.
*
Data: w_butxt like t001-butxt, " Company name
w_start_poper like t009b-poper, " Start period
w_end_poper like t009b-poper, " End Period
w_start_date like sy-datum, " Corresponding
w_end_date like sy-datum, " dates
w_start_excel(10) type c, " Excel format date
w_end_excel(10) type c, " and end
Begin of w_date, " Struc for
year(4) type n, " date conversikon
Mnth(2) type n,
Day(2) type n,
End of w_date,
w_gtexts(1000) type c, " Business areas
w_macro_string(1000) type c.
*
* Company Name
*
Read Table sd_bukrs index 1.
*
Select single butxt
into w_butxt
from t001
where bukrs = sd_bukrs-low.
*
* Change the SAP Periods and year to Excel Dates.
*
Read Table b-Monate index 1.
If sy-subrc = 0.
*
* Check if this is an open range
*
If b-Monate-low is initial.
Move 1 to w_start_poper.
Else.
Move b-Monate-Low to w_start_poper.
EndIf.
If b-Monate-high is initial.
Move 16 to w_end_poper.
Else.
Move b-Monate-high to w_end_poper.
EndIf.
Else.
Move 1 to w_start_poper.
Move 16 to w_end_poper.
EndIf.
*
Call Function 'FIRST_DAY_IN_PERIOD_GET'
Exporting
I_Gjahr = BilbJahr
I_Periv = 'P9'
I_Poper = b-monate-low
Importing
E_Date = w_start_date
Exceptions
Input_False = 1
T009_Notfound = 2
T009b_Notfound = 3
Others = 4.
Call Function 'LAST_DAY_IN_PERIOD_GET'
Exporting
I_Gjahr = BilbJahr
I_Periv = 'P9'
I_Poper = b-monate-high
Importing
E_Date = w_end_date
Exceptions
Input_False = 1
T009_Notfound = 2
T009b_Notfound = 3
Others = 4.
*
* And then to Excel format
*
Move w_start_date to w_date.
Concatenate w_date-day '/' w_date-mnth '/' w_date-year
into w_start_excel.
Move w_end_date to w_date.
Concatenate w_date-day '/' w_date-mnth '/' w_date-year
into w_end_excel.
*
* Build a list of business areas delimited by '!'
*
Loop at t_tgsb.
Concatenate w_gtexts t_tgsb-gtext
into w_gtexts
separated by '!'.
EndLoop.
*
* Put all this together for the macro call.
*
Move 'Module1.Format_Header(' to w_macro_string.
Concatenate w_macro_string '"' w_butxt '","' w_start_excel '",'
'"' w_end_excel '","' w_gtexts '")'
into w_macro_string.
BREAK-POINT.
Call method g_Document->Execute_Macro
Exporting Macro_String = w_macro_string
Receiving RetCode = pc_retcode.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Exit_Spreadsheet
*
* Purpose: Checks to see if spreadsheet has
* changed. If so offers save and then
* closes global control
*
* Entry: Global Objects
*
* Exit: Container Object Closed
* Return Code
*
* Called By: Perform Exit_Spreadsheet
* changing w_status.
* Calls:
*
* Modification History:
*
Form Exit_Spreadsheet Changing pc_retcode type t_oi_ret_string.
*
Data: w_do_close type i,
w_Has_Changed type i.
*
If not g_document is initial.
*
* Has the document changed ??
*
Call Method g_document->Is_Open
Receiving Ret_Value = w_do_close.
If not w_do_close is initial.
Call Method g_document->Close_Document
Exporting Do_Save = True
Importing Has_Changed = w_Has_Changed
RetCode = pc_RetCode.
Call Method c_oi_errors=>Show_Message
Exporting type = 'E'.
Else.
Call Method g_Document->Has_Changed
Receiving Ret_Value = w_Has_Changed.
EndIf.
*
* Has the document changed ??
*
If not w_Has_Changed is initial.
Move c_oi_errors=>ret_ok to pc_retcode.
Perform Save_Spreadsheet changing pc_retcode.
EndIf.
*
* If Save is ok, then continue with close
*
If w_retcode = c_oi_errors=>Ret_Ok.
Call Method g_Document->Release_Document
Receiving RetCode = pc_retcode.
Free g_Document.
*
* Stop the link server
*
If Not g_Link_Server Is Initial.
Call Method g_Link_Server->Stop_Link_Server
Receiving Retcode = pc_Retcode.
Free g_Link_Server.
Endif.
*
* And finally remove the control.....
*
If Not g_Excel Is Initial.
Call Method g_Excel->Destroy_Control
Receiving Retcode = pc_Retcode.
Free g_Excel.
Call Function 'CONTROL_EXIT'.
Endif.
Leave To Screen 0.
Else.
Message I000 with text-072.
EndIf.
EndIf.
EndForm.
Further information can easily be found in your docuprint CD under BC - Desktop Integration.
As you can probably see from above, I'm still struggling a bit with this but it is starting to work.
If you are on 4.6c, read the docuprint stuff first as it has been extended from 87 pages to 206 pages with the additional functionality that offers.
Above all, download the "Bang Head Here" kit from the internet and pin that up on the hardest wall you can find, 'cos you're going to need it......
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
