When I began experminenting with the ole automation samples provided with A5V5 I lamented that the "read from" capability available in V6 (see the section in the .chm "Reading an Excel Spreadsheet with OLE")
was not available to me. I wanted to read through an excel spreadsheet, excerpting certain portions and saving the result as an Alpha import compatible excel version. I found the limitations of ole in V5 can be circumvented.
The attached script processes a spreadsheet report that is available in my company. The original of the report contains data pertaining to three sales divisions. I was asked to take the data for one division and make it available in my in-house application. This is easily accomplished if I manually strip out the data I want, title the columns as needed to fit a predefined import operation, then save the file as Excel ver 3/4, then run the import. The problem with that scenario is that I must perform all these steps once a week. I was determined to automate the whole process.
Using the code snippets others have posted on the board attributed to John Magno - http://www.JohnMagno.com, I came up with the attached script. It is not a finished product that you can use in your own work "as is" but it should provide a basis for creating your own process. It is fairly well commented so I will finish by pointing out that the script....
opens an excel file of an version suported by your excel application
reads the excel file looking for predefined values in certain cells
deletes the sections of the file that I don't need
renames the columns with usable values
saves the file as excel v 4, ready for import
was not available to me. I wanted to read through an excel spreadsheet, excerpting certain portions and saving the result as an Alpha import compatible excel version. I found the limitations of ole in V5 can be circumvented.
The attached script processes a spreadsheet report that is available in my company. The original of the report contains data pertaining to three sales divisions. I was asked to take the data for one division and make it available in my in-house application. This is easily accomplished if I manually strip out the data I want, title the columns as needed to fit a predefined import operation, then save the file as Excel ver 3/4, then run the import. The problem with that scenario is that I must perform all these steps once a week. I was determined to automate the whole process.
Using the code snippets others have posted on the board attributed to John Magno - http://www.JohnMagno.com, I came up with the attached script. It is not a finished product that you can use in your own work "as is" but it should provide a basis for creating your own process. It is fairly well commented so I will finish by pointing out that the script....
opens an excel file of an version suported by your excel application
reads the excel file looking for predefined values in certain cells
deletes the sections of the file that I don't need
renames the columns with usable values
saves the file as excel v 4, ready for import
Comment