Topic
  • 6 replies
  • Latest Post - ‏2014-02-28T14:36:28Z by Estebell
Estebell
Estebell
59 Posts

Pinned topic translating VBA Code in DXL

‏2014-02-25T15:48:47Z |

Hi,

I'm trying to translate VBA code (from excel) to DXL with OLE automation but it returns an error...

VBA is : Sheets("Feuil2").Select

My code is :

const string cPropertySheet = (NLS_("Sheets"))
OleAutoObj objWorkbook = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoArgs args   = create

bool oleYesToAll = false 

void checkRes(string res) {
    
    if ((!oleYesToAll) && (res != (NLS_("")))){ 
        string answers[] = {LS_("String_Yes",NLSTEMP_("Yes")), LS_("String_Yes_to_all",NLSTEMP_("Yes to all")), LS_("String_No",NLSTEMP_("No"))}
        int answer = messageBox(dxlStrformat(getExtMsg("String_OLE_method_failed_n_sn_n_Do_you_want_to_continue",NLSTEMP_("OLE method failed: \n%s\n\nDo you want to continue?")), res), answers, msgWarning)
        if (answer == 2)
        {
            progressStop() 
       halt()
   } else if (answer == 1)
   {
       oleYesToAll = true    
   }
 }
}

 

clear(args)
put (args, "(\"Feuil2\")")
checkRes(oleGet(objWorkbook,cPropertySheet,args,objSheet))
checkRes(oleGet(objSheet, "Select", objCell))
clear(args)

 

Can someone help me ?

  • Mathias Mamsch
    Mathias Mamsch
    2147 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-25T16:07:43Z  

    Well "Sheet.select" is a method not a property, therefore you cannot use oleGet to call it. You need to use oleMethod. See here:

    http://msdn.microsoft.com/en-us/library/Microsoft.Office.Interop.Excel.Worksheet.aspx

    Regards, Mathias

  • Estebell
    Estebell
    59 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-25T16:16:36Z  

    Well "Sheet.select" is a method not a property, therefore you cannot use oleGet to call it. You need to use oleMethod. See here:

    http://msdn.microsoft.com/en-us/library/Microsoft.Office.Interop.Excel.Worksheet.aspx

    Regards, Mathias

    If I replace checkRes(oleGet(objSheet, "Select", objCell)) by checkRes(oleGet(objSheet, "Select", objCell)), it doesn't work anymore....

  • Mathias Mamsch
    Mathias Mamsch
    2147 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-26T16:30:20Z  
    • Estebell
    • ‏2014-02-25T16:16:36Z

    If I replace checkRes(oleGet(objSheet, "Select", objCell)) by checkRes(oleGet(objSheet, "Select", objCell)), it doesn't work anymore....

    Is this a typo: "if a replace ... oleGet ... by oleGet ... it doesn't work" ? I assume yes and you tried oleMethod and it didnt work. Can you please post the traceback and the exact code, so that we see at least in what line number the code is failing? Its always hard to tell without trying which parameter is incorrect. A fully working example (i.e. with declaration of workbook variables running on an Excel queried by oleGetAutoObject) would reduce answering efforts ...

    Also please post the Excel Version you are using.

    Regards, Mathias

    Updated on 2014-02-26T16:30:57Z at 2014-02-26T16:30:57Z by Mathias Mamsch
  • Estebell
    Estebell
    59 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-28T10:48:35Z  

    Is this a typo: "if a replace ... oleGet ... by oleGet ... it doesn't work" ? I assume yes and you tried oleMethod and it didnt work. Can you please post the traceback and the exact code, so that we see at least in what line number the code is failing? Its always hard to tell without trying which parameter is incorrect. A fully working example (i.e. with declaration of workbook variables running on an Excel queried by oleGetAutoObject) would reduce answering efforts ...

    Also please post the Excel Version you are using.

    Regards, Mathias

    Hi, here is my code in attachment.

    I run the code from an open module.

    the error message is :

    -R-E- DXL: <Line:189> Paramètre null OleAutoObj envoyé dans la position d'argument 1

    Traçage :
    <Line:189> 
    <Line:202> 
    <Line:206> 
    -I- DXL: Exécution arrêtée
     

    My Excel version is Excel 2010.

     

    Hope it will help us...

    Attachments

  • adevicq
    adevicq
    154 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-28T13:06:54Z  
    • Estebell
    • ‏2014-02-28T10:48:35Z

    Hi, here is my code in attachment.

    I run the code from an open module.

    the error message is :

    -R-E- DXL: <Line:189> Paramètre null OleAutoObj envoyé dans la position d'argument 1

    Traçage :
    <Line:189> 
    <Line:202> 
    <Line:206> 
    -I- DXL: Exécution arrêtée
     

    My Excel version is Excel 2010.

     

    Hope it will help us...

    Hi,

    To my knowledge there is no direect way to retrieve a sheet by its name.

    Use the number instead and it will wort. To retrieve a sheet by its name I loop through all the sheets, read each name and stop when I have found the right one...

    oleGet(objSheets, cPropertyCount, numSheets) // retrieves the number of sheets
    oleGet(objSheet, cPropertyName, sheetName)   // Gets the name of a sheet
    

     

    Hope this helps???...

    Alain

     

  • Estebell
    Estebell
    59 Posts

    Re: translating VBA Code in DXL

    ‏2014-02-28T14:36:28Z  
    • adevicq
    • ‏2014-02-28T13:06:54Z

    Hi,

    To my knowledge there is no direect way to retrieve a sheet by its name.

    Use the number instead and it will wort. To retrieve a sheet by its name I loop through all the sheets, read each name and stop when I have found the right one...

    <pre class="javascript dw" data-editor-lang="js" data-pbcklang="javascript" dir="ltr">oleGet(objSheets, cPropertyCount, numSheets) // retrieves the number of sheets oleGet(objSheet, cPropertyName, sheetName) // Gets the name of a sheet </pre>

     

    Hope this helps???...

    Alain

     

    Sorry, but when I try to retrieve the number of sheets, the result is "Problem related to the OLE argument names." !!

    I thought it returned me an integer value...

    result = (oleGet(objSheet,cPropertyCount,SheetCount))
    print result
    

     

    Updated on 2014-02-28T14:37:42Z at 2014-02-28T14:37:42Z by Estebell