Good afternoon.
I have a process which opens an Excel workbook, hides a worksheet and then downloads it for the user.
This works for most workbooks, however there is a particular one that is causing an issue and it isn't clear as to why.
Debugging the code, it seems to pass through the hiding of the worksheet ok, but when it reaches the Workbook_GetBinaryData process, it hangs until it is timed out and the error returned is "Column number out of bounds".
Does anyone know where we should start to look where the issues lies? We are stripping out worksheets, trying to hide different worksheets, etc.
Cheers,
Russ.
Good afternoon. Apologies for not responding sooner, but we have finally got around to investigating this issue and the onerous task of stripping the workbook down and building it up again and again and again :)
We have found that the issue was due to an object (or group of objects) that were slightly overlapping a column that was already hidden. We had ALL columns from column AA to the end hidden on a worksheet, and the group of objects slightly overlapped column AA.
When we either unhid those columns or reduced the size of the grouped objects, the error disappeared.
Thank you to everyone who contributed to this thread. It was much appreciated.
Hi Russel,
Would it be possible to share the .oml file to get a better understanding of what’s happening? Also, does this particular Excel file have any specific formatting?
Hi Beatriz. Thank you for responding so quickly.
Unfortunately I cannot include the oml, but I can include a snapshot of the process and highlight where the error occurs.
The workbook has a lot of formatting, VBA, formulae, etc, but so do other workbooks we are running this on and they work ok.
The issue only occurs when we try to hide worksheets in this workbook specifically, even if we add a completely blank worksheet and try to hide that one, which suggests that there may be something corrupt somewhere in the file, but I don't know where to start to look for it. Especially as the message actually suggests it is a column issue.
I think the issue might be related to VBA. Could you send a similar Excel file so I can used to try to reproduce the logic on my side?
Hi Beatriz.
Unfortunately I am unable to send the workbook due to the sensitive nature of it, and it would take a considerable amount of time to strip it back enough to be able to send it.
I will try and remove all VBA first and see if that resolves the issue, and then work through it from there.
I will report back when/if I find anything, and even if I don't in case this helps anyone else.
Thanks again.
Hi Russel.
In the meantime I will try to replicate the issue on my side as well. Let me know if I can help you with anything else.
I would start with a step by step debug, but if I would have to guess I would say you might have a infinite loop, hiding columns.
Hi Carlos. Thank you for your quick response.
We have debugged the process and it does not go down the "hiding columns" path as we did not set any columns to be hidden.
It successfully loops around the "hiding sheets" path, then stops on the Workbook_GetBinaryData process.
Hi Russell, in that case I'd suggest to "slice" the problem. First try not hiding any sheet and see if you have the error or not. Then each time include one sheet to hide, to identify which is causing the issue.
Thanks, Carlos.
Yes, I will try that, as well as filtering out other elements.
I was hoping someone may know what drives that error message so I could narrow down the issue, but no worries if not.
Hi Russell
A cursory Google search suggests it might be related to an image insert.
Are you inserting images anywhere or perhaps have existing images in the file?
Hanno