Research Outline

Converting Data from Excel to Oracle

Goals

To understand how to successfully convert an Excel macro to Oracle from a 64-bit machine for Office 365.

Early Findings

  • Microsoft Excel can access data from an Oracle database using an ODBC connector.
  • With an ODBC Driver, data can be imported directly into an Excel Spreadsheet and presented as a table.
  • It is necessary to use a matching Excel and ODBC Driver (e.g. if a 64-bit version of Excel is being used, a 64-bit ODBC Drive should also be used.)
  • Oracle Web Applications Desktop Integrator supports both the 32-bit release and the 64-bit release of Office 365 ProPlus Excel 2016.

Enabling Macros Security Settings

  • In order to enable the macros used by Oracle Web Applications Desktop Integrator the user's preferred macro security settings should be selected in Microsoft Excel.
  • If the OOXML format is not being used, "then Oracle Web Applications Desktop Integrator creates an initial document named WebADI.xls on the server."
  • After this document is downloaded, "it creates the actual integrator document on the desktop using VBA macros. Macros are also used for lists of values and features in the Oracle ribbon tab, including upload. "
  • If OOXML format is being used, then "Oracle Web Applications Desktop Integrator creates the document on the server without using macros, but limited macros are used for lists of values and features in the Oracle ribbon tab, including upload. "
  • A medium macro security level can also be selected, in which case "there will be a prompt to enable the macros each time an Oracle Web Applications Desktop Integrator spreadsheet is opened."
  • Another option is to set the BNE Enable Digital Signature profile option in Oracle E-Business Suite to have "Oracle Web Applications Desktop Integrator affix a digital signature to the spreadsheets that are created. In this case, a high macro security level in Microsoft Excel can be selected."
  • After the "Introduction to Oracle Web Applications Desktop Integrator 1-5 Oracle Web Applications Desktop Integrator" macros is selected as coming from a trusted source, Microsoft Excel automatically allows the macros to run.

Other Macros Notes:

  • If the macros is not enabled with the appropriate setting, "according to whether a digital signature is used or not, then the Oracle Web Applications Desktop Integrator features that use macros will not work."
  • If OOXML is not used and macros is not enabled, then "Oracle Web Applications Desktop Integrator cannot create spreadsheets."
  • If OOXML is used but macros is not enabled, then Oracle Web Applications Desktop Integrator can create spreadsheets in the OOXML format on the server, but the lists of values or the Oracle ribbon tab features cannot be used, including upload, when those spreadsheets are being worked on.