Object Error

MarcMeg · Aug 3, 2018

Hello!

I am trying to extract data from an Excel document through to my EXTRA! X-treme application (to put in an array and run through a loop).

I currently have the code:

Code:

Dim obj as object
        Dim objWorkbook as object
        Set obj = CreateObject("Excel.Application")
        obj.visible = True
        obj.workbooks.open "F:\actuarial\Shared\Product_Support\ALBA\Incident_1840\Testing.xls" 
        obj.WorkSheets("Testing").Cells(15,"A").Value = 10

Which I have effectively copied from other posts on this forum.

My code opens Excel and the desired file correctly however fails on the final line due to an "Object Error". The Excel document only has 1 tab named "Testing". The only thing I can think of is that my EXTRA! X-treme application is opened via a remote desktop.

Any help would be greatly appreciated!

Thank you,

Marc

SkipVought · Aug 3, 2018

Hi,

Make sure that your sheet name has no trailing or leading SPACES by Editing the Sheet Tab.

If you are using Excel in any manner in your process, I would recommend coding your entire process in Excel VBA. I can remember coding in Extra VB once, and being frustrated by the unsophisticated editor. Consequently, I’ve coded all my Attachmate screen scraping in Excel, as the tools in Excel VBA are much more sophisticated than Extra VB.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

MarcMeg · Aug 6, 2018

Hi Skip,

I have tried the above and the sheet name is exactly the same, to the point of copying it over!

Unfortunately I cannot code my process in Excel VBA as the program, EXTRA! X-treme is opened via a remote desktop. For example, we run an application which opens a temporary remote desktop, it opens EXTRA! X-treme as an application. (This is also done on a remote desktop, very confusing I know)

Could this perhaps be the reason why I am struggling to interact between the two?

I have tried a few different things but it seems that as soon as I have the excel document open I cannot interact with it, including just the simple line

Code:

set x = obj.ActiveSheet

I do notice that when the excel document is open it cascades with the remote EXTRA! X-treme session (the remote desktop used to open EXTRA), please see the attachment...

https://files.engineering.com/getfi...-af08-4f15-a22e-de646ef02d10&file=Capture.PNG

Thank you,

Marc

SkipVought · Aug 6, 2018

I'm not sure how EXTRA! X-treme is different than plain vanilla Extra, but my experience coding in Extra VB--I was spoiled from coding in Excel VBA. The difference was night and day! The difference was like going from a fine luxury car to a Yugo.

Your Remote Desktop should make no difference. But I'd be a bit more careful with Excel objects by setting object for workbook...

Code:

'
    Dim oXL As Object
    Dim oWB As Object
    
    'instantiate the oXL Excel Application object
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    
    'instantiate the oWB Excel Workbook object
    Set oWB = oXL.Workbooks.Open("F:\actuarial\Shared\Product_Support\ALBA\Incident_1840\Testing.xls")
    
    oWB.Worksheets("Testing").Cells(15, "A").Value = 10

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

MarcMeg · Aug 6, 2018

Hi Skip,

I appreciate your help but it seems like we're still having no luck!

Unsure as to why this isn't working. I hope this isn't too much of a challenge!

Please find the attached screenshot.

Thanks,

Marc

MarcMeg · Aug 6, 2018

Hi Skip,

Not sure whether this will help but...

The Excel application opens with "- \\Remote" afterwards as show in the attachment. Maybe something extra needs to be added to the code to account for this?

Thanks,

Marc

SkipVought · Aug 6, 2018

Using a mapped drive (F: in your code) can be a problem. I’d use the Server logical name instead...

Code:

Set oWB = oXL.Workbooks.Open("\\YourServerName\actuarial\Shared\Product_Support\ALBA\Incident_1840\Testing.xls")

NOTE: your server may also include a subserer name as well like \\Server\subserver...

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

MarcMeg · Aug 7, 2018

Hi Skip,

I have tried the above with the code:

Code:

    Dim oXL As Object
    Dim oWB As Object
    
    'instantiate the oXL Excel Application object
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    
    'instantiate the oWB Excel Workbook object
    Set oWB = oXL.Workbooks.Open("\\co004\Public\actuarial\Shared\Product_Support\ALBA\Incident_1840\Testing.xls")
    
    oWB.Worksheets("Testing").Cells(15, "A").Value = 10

Yet we are still falling at the same hurdle

.

I am pretty certain I have used the correct name and have attached the server mapping for reference.

Thanks,

Marc

jeaneshe · Nov 20, 2018

Can you try putting it in a local folder and see if it will open in a simple path. Also I would not use the // as I believe it indicates something is behind the // yet not included in the path. Looking at your last snip, I would start the path with "F:\Public...."

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Object Error

MarcMeg

Technical User

SkipVought

Programmer

MarcMeg

Technical User

SkipVought

Programmer

MarcMeg

Technical User

MarcMeg

Technical User

SkipVought

Programmer

MarcMeg

Technical User

jeaneshe

Programmer

Similar threads

Part and Inventory Search

Sponsor