Oracle Forms

I recorded a macro in Excel and am attempting import a csv file using webutil client_ole2.

I can usually find a solution for a problem on the net.

This problem is beating me up.

I also did not find anything about the type mismatch error.

I am assuming that the syntax of the connection and destination are not correct.

I have tried many variations for the values of connection and destination.

Any help would be appreciated.

Thanks in advance.

Microsoft method ActiveSheet.QueryTables.Add

Invoking Add method using client_ole2.invoke_obj is not working

Error WUO-705: Unable to invoke Method: Add;

               Exception com.jacob.com.ComFailException:

               A COM exception has been encoutered:

               At Invoke of: Add

               Description: Type mismatch

Database 11g

Middleware 11.1.1.7.0

Web Logic 10.3.6.0

Oracle forms 11g 11.2.2.0

Excel Office 2010

Snippet of Code not working.

PROCEDURE EXPORT_SPREADSHEET IS

  application_obj client_ole2.obj_type;

  workbooks_obj   client_ole2.obj_type;

  workbook_obj    client_ole2.obj_type;

  worksheets_obj  client_ole2.obj_type;

  worksheet_obj   client_ole2.obj_type;

  window_obj      client_ole2.obj_type;

  column_obj      client_ole2.obj_type;

  row_obj         client_ole2.obj_type;

  cell_obj        client_ole2.obj_type;

  arglist         client_ole2.list_type;

  font_obj        client_ole2.obj_type;

  selection_obj   client_ole2.obj_type;

  range_obj       client_ole2.obj_type;

  sheet_object    client_ole2.obj_type;

  tables_object   client_ole2.obj_type;

  table_object    client_ole2.obj_type;

  connection_obj  client_ole2.obj_type;

  l_data_scale    varchar2(30);

  l_connection    varchar2(100);

  l_destination   varchar2(100);

  l_name          varchar2(1024);

  l_array         varchar2(250);

  l_file_delete   boolean;

BEGIN

  :control.message_line := 'Creating Excel spreadsheet.';

  synchronize;

  --

  -- Start Excel Application and hide from view

  application_obj := client_ole2.create_obj('Excel.Application');

  client_ole2.set_property(application_obj,'Visible',false);

  --

  workbooks_obj := client_ole2.get_obj_property(application_obj,'workbooks');

  workbook_obj := client_ole2.invoke_obj(workbooks_obj,'Add');

  worksheets_obj := client_ole2.get_obj_property(application_obj,'worksheets');

  -- Get active worksheet and rename

  worksheet_obj := client_ole2.get_obj_property(application_obj,'activesheet');

  client_ole2.set_property(worksheet_obj ,'Name','enVision');    

  --

  :control.message_line := 'Formatting columns in Excel.';

  synchronize;

  --

  -- Format spreadsheet columns

  --

  For i in rl_excel_export.report_item.first .. rl_excel_export.report_item.last

  Loop

    --SetHeadingColumn(row_id number, col_id number, item_id number)

    SetheadingColumn(1, (i + 1), i);

  End Loop;

  --

  :control.message_line := 'Importing csv file data into Excel.';

  synchronize;

  --

  l_connection  := 'Connection:="TEXT;C:\Temp\enVision_temp_18_nov_16_09_19_42.csv"';

  l_destination := 'Destination:=Range("$A$1")';

  arglist := client_ole2.create_arglist;

  client_ole2.add_arg(arglist, l_connection);

  client_ole2.add_arg(arglist, l_destination);

  --

  tables_object := client_ole2.get_obj_property(worksheet_obj, 'QueryTables');

  --ActiveSheet.QueryTables.Add returns a QueryTable object

  --Has 3 parameters Connection, Destination and SQL

  -- Connection and Destination are required, SQL is optional

  The following line of code always fails with error WUO-705 At Invode of: Add Description: Type mismatch

  table_object  := client_ole2.invoke_obj(tables_object,'Add', arglist);

  -- The excel macro I am attemping to code through client_ole2 follows the code

  -- Auto Freeze Top Row

  window_obj := client_ole2.get_obj_property(application_obj, 'ActiveWindow');

  client_ole2.set_property(window_obj, 'SplitColumn', 0);

  client_ole2.set_property(window_obj, 'SplitRow', 1);

  client_ole2.set_property(window_obj, 'FreezePanes', TRUE);

  -- Release objects

  client_ole2.release_obj(window_obj);

  --

  -- Release workbook objects

  client_ole2.release_obj(workbooks_obj);

  client_ole2.release_obj(workbook_obj);

  client_ole2.release_obj(worksheets_obj);

  client_ole2.release_obj(worksheet_obj);

  --

  -- Finished outputing data to spreadsheet

  --

  client_ole2.set_property(application_obj,'Visible',true);

  :control.message_line := 'Excel Spreadsheet completed.';

  --

  client_ole2.RELEASE_OBJ (application_obj);

  --

  -- Delete temporary csv file.

  --l_file_delete := webutil_file.delete_file(rl_excel_export.g_temp_file);

END EXPORT_SPREADSHEET;

Excel recorded macro

Sub Macro1()

Macro1 Macro

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;C:\Temp\enVision_temp_16_nov_16_09_59_02.csv", Destination:=Range( _

        "$A$2"))

        .Name = "enVision_temp_16_nov_16_09_59_02"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlDelimited

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = False

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = True

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    Cells.Select

End Sub