This thread is about lessons I have learned from working on an internship project.
The most recent issue I had was getting the code executing on both development and deployment systems. Initially, the code was developed on a single-core Pentium 4-M based laptop. After a time, the analysis of the data I was working with eventually eclipsed the laptop's capabilities; luckily, my buddy had an extra desktop laying around.
I then gained a dual-core setup, with much more memory (4 GiB compared to 1 GiB), which allowed the program to run with a quicker perceived speed. This benefited me as I could watch as the program worked, watching all the elements come together as the data was analyzed.
Then the server came into play, as my boss wanted to use the program when necessary, to make sure the numbers he was getting were correctly calculated in the reports the program was to be generating, so as to reduce the human element in the calculation process.
The main hurdle I had to jump, and it was an Everest!, was saving the analyzed report to disk, with the Excel 2007 XLSX format preference (I started with TXT files to ensure the numbers were generating correctly). The initial files were created, but no data was to be found in them. I have dedicated nearly 3 days of research and trial and error coding to get something to work for my boss (which I have a meeting with later today).
All the research pointed to something with Microsoft admitting to an 'issue' where if you copied one too many sheets before saving them, it would 'crash' or something similar. Great, except I am not copying sheets, except from an internal format (2-dimensional array) into a Worksheet object. So that 'resolution' worked quite well for some people; however, I am not part of that group.
Further researching yielded specific instructions to set up the variables as late-bound (set the variable to a general Object, then fill in the data with a more specific object reference (in the base case, I set the xlApp variable to just a regular Object, then used the CreateObject() method to fill in the actual details for the xlApp object variable), as opposed to early-bound (set the variable to a specific object in the code itself, such as xlApp is an Excel.Application object instance).
However, in the deployment, the actual Excel.exe file may not have identical properties, such that the entry points into the file for the object types will not match up; therefore the CreateObject() method allows for slightly dynamic? object instantiation.
After implementing this in the Excel wrapper module, I still had the errors. I tried all of these methods, hoping one would work:
where the <_....> is an instance of the enclosed object type.
On chance, I shot for the _Workbook.SaveCopyAs(fPath) but it did not quite work as I wanted (it asked the user if they want to save changes to 'Book1'), so I stopped using it, wanting one of the other methods to work.
Well, after more frustrating research, I recognized that a similar issue to data flow may be the issue as well, so I shifted the code to a more object-oriented paradigm, which worked slightly better, but still had the original issue. I decided to add a few more properties and methods, which would allow for multiple "Worksheets" into a "Workbook" (I wrapped some properties in them as well). Then I created my own save method (to encompass|wrap the save methods provided through respective objects from Excel).
Eventually, I returned to the SaveCopyAs() method. I just shot down the dialog box that asks the user if they want to save changes to 'Book1', because they would most likely not know what that workBOOK would contain; plus it really was just the report in the Excel spreadsheet in memory, of which I just saved a copy onto permanent storage (the disks), which meant I could just trash it.
So, if you are programming in Visual Basic (I am currently using 6, but I am quite sure a good bit of this will apply to .Net+), keep in mind that SaveCopyAs() may just be what you need, if your target system is not executing properly, when your development system runs your code perfectly.
Thanks for reading, and I hope you can make use of this information, whether directly or passing it on to a friend|colleague.
The best in your endeavors.
The most recent issue I had was getting the code executing on both development and deployment systems. Initially, the code was developed on a single-core Pentium 4-M based laptop. After a time, the analysis of the data I was working with eventually eclipsed the laptop's capabilities; luckily, my buddy had an extra desktop laying around.
I then gained a dual-core setup, with much more memory (4 GiB compared to 1 GiB), which allowed the program to run with a quicker perceived speed. This benefited me as I could watch as the program worked, watching all the elements come together as the data was analyzed.
Then the server came into play, as my boss wanted to use the program when necessary, to make sure the numbers he was getting were correctly calculated in the reports the program was to be generating, so as to reduce the human element in the calculation process.
The main hurdle I had to jump, and it was an Everest!, was saving the analyzed report to disk, with the Excel 2007 XLSX format preference (I started with TXT files to ensure the numbers were generating correctly). The initial files were created, but no data was to be found in them. I have dedicated nearly 3 days of research and trial and error coding to get something to work for my boss (which I have a meeting with later today).
All the research pointed to something with Microsoft admitting to an 'issue' where if you copied one too many sheets before saving them, it would 'crash' or something similar. Great, except I am not copying sheets, except from an internal format (2-dimensional array) into a Worksheet object. So that 'resolution' worked quite well for some people; however, I am not part of that group.
Further researching yielded specific instructions to set up the variables as late-bound (set the variable to a general Object, then fill in the data with a more specific object reference (in the base case, I set the xlApp variable to just a regular Object, then used the CreateObject() method to fill in the actual details for the xlApp object variable), as opposed to early-bound (set the variable to a specific object in the code itself, such as xlApp is an Excel.Application object instance).
However, in the deployment, the actual Excel.exe file may not have identical properties, such that the entry points into the file for the object types will not match up; therefore the CreateObject() method allows for slightly dynamic? object instantiation.
After implementing this in the Excel wrapper module, I still had the errors. I tried all of these methods, hoping one would work:
Code:
<_Workbook>.Close(True, fPath)
<_Worksheet>.SaveAs(fPath)
<_Workbook>.SaveAs(fPath)
<_Application>.openSaveBox(fPath)On chance, I shot for the _Workbook.SaveCopyAs(fPath) but it did not quite work as I wanted (it asked the user if they want to save changes to 'Book1'), so I stopped using it, wanting one of the other methods to work.
Well, after more frustrating research, I recognized that a similar issue to data flow may be the issue as well, so I shifted the code to a more object-oriented paradigm, which worked slightly better, but still had the original issue. I decided to add a few more properties and methods, which would allow for multiple "Worksheets" into a "Workbook" (I wrapped some properties in them as well). Then I created my own save method (to encompass|wrap the save methods provided through respective objects from Excel).
Eventually, I returned to the SaveCopyAs() method. I just shot down the dialog box that asks the user if they want to save changes to 'Book1', because they would most likely not know what that workBOOK would contain; plus it really was just the report in the Excel spreadsheet in memory, of which I just saved a copy onto permanent storage (the disks), which meant I could just trash it.
So, if you are programming in Visual Basic (I am currently using 6, but I am quite sure a good bit of this will apply to .Net+), keep in mind that SaveCopyAs() may just be what you need, if your target system is not executing properly, when your development system runs your code perfectly.
Thanks for reading, and I hope you can make use of this information, whether directly or passing it on to a friend|colleague.
The best in your endeavors.
