Solved

Weird Issue During Excel Export

Posted on 2014-01-10
13
589 Views
Last Modified: 2014-11-12
Hello,

I'm encountering a weird error that we seem unable to resolve.  I have an MS Access application that exports data into an Excel worksheet and formats the worksheet to look like a Gantt chart.  This process has worked great for years across multiple OS's, processor types, etc.

I recently deployed this application at a new client and it is not working properly.  The back-end is in Azure, SQL Server 2012.  The front-end is in MS Access 2013 and works great in my environment no matter how many variations of VM's I create to test it, and it also works great for the client's IT machines.

However, the report/export does not work properly on the machines of the end users.  Basically, it stops formatting altogether around column "IK" (245).  After that, it no longer can set column widths, merge cells, add borders, etc.

Any thoughts on what might be going on?
0
Comment
Question by:Torrwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 11

Expert Comment

by:MajorBigDeal
ID: 39771761
Without doing any debugging, a wild guess would be that it is a memory constraint. I know that historically Excel has been very sensitive to the amount of available RAM.  Is the a difference in the amount of RAM on the machines where it works vs the machines where it does not work?
0
 
LVL 13

Author Comment

by:Torrwin
ID: 39771793
That was a thought I had too, but those users have 4GB.  Shouldn't that be enough to handle a spreadsheet?
0
 
LVL 10

Expert Comment

by:Korbus
ID: 39771807
Does the destination spread sheet already exist, and you only modify it, or does it create a new excel sheet?    I ask because I suspect the destination excel, if modified and not created at run time, may be corrupted.
0
Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

 
LVL 13

Author Comment

by:Torrwin
ID: 39772283
It's created at run-time and saved after it's finished being created.  I supposed I could try saving it first and then formatting it?
0
 
LVL 11

Expert Comment

by:MajorBigDeal
ID: 39772308
LOL, I would think that 4GB would be enough.   I think this is one of those situations that might benefit from some creative debugging by trying to create some boundaries to the problem in the hope of generating some clues.  

How many different end-user machines do you have available for you to try this on?  Are they all configured identically?   Do any of the "end users" have admin rights on their machines?  the idea being to see how consistent the misbehavior is.

Also, can you reduce the amount of data in each column but still leave the same number of columns?  The idea being to see if the place where it starts acting weird stays the same or changes.

Can you check to see how much of the end-user RAM is used and how much is available?  

Can you look at the windows event log after you run this to see if it is complaining about anything?

Have you looked at the processes of the end-user machines to see what they are running and if any of it looks unusual?
0
 
LVL 13

Author Comment

by:Torrwin
ID: 39776991
I removed the data and only did the formatting (merge, forecolor, backcolor, etc) and it still failed at the exact same point.  Also, we tried it on one of their more powerful machines in the office with 12GB of ram and it still failed at the same point.

We've tried various combinations of being admin/non-admin, on/off the domain, etc.
0
 
LVL 10

Expert Comment

by:Korbus
ID: 39777120
Hmm,  I'm starting to suspect thier network and servers.  Is anything that might be related showing up in the logs on those machines (azure and SQL hosts) when you run the process?

Is it possible to test with a local datasource? (I know you said you removed the data-  does its still talk to azure and SQL though?)  Or perhaps you can test running the program directly on the servers?
0
 
LVL 13

Author Comment

by:Torrwin
ID: 39777183
I'm waiting to hear back on the logs.  The reason the database is in Azure is because they don't have a SQL Server environment.

However, when the formatting process fails the data is still entered, the spreadsheet just looks horrible.
0
 
LVL 11

Expert Comment

by:MajorBigDeal
ID: 39777188
I think that the fact that it fails consistently at the same point is good news because it should make it easier to crack the problem.  Can you try removing one type of formatting (but leave the others) and see if you can get it to behave any differently?  At some point, it has to start working again and the you will have a boundary to work with.
0
 
LVL 13

Accepted Solution

by:
Torrwin earned 0 total points
ID: 39780185
Here's what fixed it, though I have no idea why their machines were set the other way to start with...

1)  Open a blank workbook
2)  Go to "File" -> "Options"
3)  Select “Save” from the left navigation menu
4)  Select “Excel Workbook" from the "Save files in this format" drop-down list
5)  Click "OK" to save the changes
0
 
LVL 10

Expert Comment

by:Korbus
ID: 39780930
Wow, good catch, I never would have thought of that.  What format WAS it using by default?
0
 
LVL 13

Author Comment

by:Torrwin
ID: 39782604
Excel 97-2003 Workbook
0
 
LVL 13

Author Closing Comment

by:Torrwin
ID: 39791942
Thanks for the help, I think we may have all learned something today.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Optimized for private cloud infrastructures and datacenters, Nano Server is minimalistic, yet super-efficient, OS for services such as Hyper-V and Hyper-V cluster. Learn how you can easily deploy Nano Server and unlock its power!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question