BA cannot see the sheets in spreadsheet

[Josy]

Hello,

I have problem to connect to my spreadsheet.
It happens randomly. I tried all solutions I found on the Forum but no solution yet.
If I open a new file it can see the sheets in it.
It's still working when I put in my codes but when I try it next day it doesn't work anymore.
I removed BA 11066x83 and installed 11066x82 but no difference.
Anybody has a clue what it depends on that BA can see sheets or not?

BTW does it add any clue to the above:

System.Exception: Cannot create ActiveX component. at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class)
at Betting_Assistant.ExcelOptions.populateWorkBooks(String openWBName)
in C:\Betting Assistant Beta 1_1_0_66x68\Betfair Trading API Beta\ExcelOptions.vb:line 647

[MarkRussell]

Hi,

I've had this happen to me and discovered that there was something wrong in my code.
I corrected the code and then it worked ok.

Regards,
Mark

[Josy]

Thank you Mark for your reply.
That's what I am doing right now but didn't find it yet.
Be back if found.

[2020vision]

Anybody has a clue what it depends on that BA can see sheets or not?

Try putting all sheets in and loading from the same folder?

Any good? Michael

[Josy]

Try putting all sheets in and loading from the same folder?

Any good? Michael

Hello Michael,

I afraid there is a misunderstanding. BA can see my Workbook but couldn't see the Sheets inside it.

Anyway it looks solved but I am not satisfied as I don't know what fixed it as I gave up and made a new Workbook.
But now I have another problem.
My new Workbook can be connected to BA but when it starts recording Excel freezing completely (it takes 98-99% of CPU time).
There is only 1 file populated by BA - the BA Diagnostic.txt but I don't understand it.
If somebody can decrypt it and let me know what is the problem that would be great. Here it is:

System.Exception: Error copying Excel cells.
System.Runtime.InteropServices.COMException (0x80010105): The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object o, Type& objType, String name, Object[] args, String[] paramnames, Boolean OptimisticSet, CallType UseCallType)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase, CallType CallType)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments)
at Betting_Assistant.Excel.setCells(Object xlRange, Object[,] wCells, String action) in C:\Betting Assistant Beta 1_1_0_66x68\Betfair Trading API Beta\Excel.vb:line 988
at Betting_Assistant.Excel.ExcelUpdateThread() in C:\Betting Assistant Beta 1_1_0_66x68\Betfair Trading API Beta\Excel.vb:line 2244

[Josy]

Hello,

Anybody can help me with the above error message?
Thank you in advance.

[GaryRussell]

Does your workbook contain VBA code? It could be your code causing this issue.

Does this error appear in a new workbook without code?

[Josy]

Hi Gary,

Thank you for your answer.
This error doesn't appear with new workbook without code.
And yes, my workbook contain codes but I can't find any errors.
The code could be a bit long but simple.
It looks working with one market but as soon as I add one more it freezes the Excel
however the code in 2nd sheet is the same as in the 1st except I use different variables and record to different sheet
(2 market recorded to 2 different sheets).
Do you know what these 988 and 2244 errors mean?

[GaryRussell]

Those are line numbers in my code so yes I understand what they mean, but that just confirms BA is in some way being blocked from updating Excel because it receives RPC_E_SERVERFAULT error.

I strongly suspect your code is keeping Excel busy so that BA cannot update it. If you send a copy of the spreadsheet to admin@gruss-software.co.uk with instructions how to use it I could take a look to confirm this.

[Josy]

Thank you Gary.
I am on it.
Thanx.

[GaryRussell]

I have received your spreadsheet and replied to your email. There was an error message displayed in Excel preventing BA from updating it.

[Josy]

Thank you Gary so much.
I am going to check it right now.
Thank you for your time and help.

[Josy]

Thank you Gary again.
You found my mistake, it's not freezing the Excel anymore.

Now I have to face another problem as it stops recording after ~45-50 minutes...
Still looking for reason it stops.

[Josy]

Couldn't find the reason it stops recording after 1st half but have an idea.
Anyway I implemented a workaround so it's now working good.