19 Replies Latest reply: May 15, 2014 10:07 AM by joey RSS

    Excel 5.0/95

    LCampbell _

      I have Excel 5.0/95 reports that I need to process through Monarch DataPump 10.5.  The system I use to retrieve the reports will only export them as Excel 5.0/95 so I can't change the version in the actual source download.  Currently I open the reports and save them as a newer version of Excel and then run Monarch on them.  I am trying to automate and schedule this process using Monarch DataPump.  Does anyone know how I can convert the Excel 5.0 files to a newer version as part of the scheduled process?  I am guessing this could be done as a script but not really sure how to start.  I am just now learning DataPump.

        • Excel 5.0/95
          joey

          Monarch lists Excel 95 as a valid input file format.  Were you not able to read this by Monarch without converting it?

          • Excel 5.0/95
            LCampbell _

            The file technically opens when OPEN DATABASE... is selected but the "Database-Select a dataset" screen does not list any available worksheets and the NEXT button is not available.  Both "List Named Ranges" and "List Worksheets" are checked.  In order to get the worksheets to list, I have to open it in Excel and save it in a more current version of Excel.

            • Excel 5.0/95
              joey

              What version of Excel are you using?

                • Excel 5.0/95
                  LCampbell _

                  Excel 2007

                    • Excel 5.0/95
                      joey

                      Hmmmm...

                       

                      I looked online for a batch conversion utility, and there are plenty of them out there but they look like third party and require a purchase.  I don't know if that can be ruled out.

                       

                      A free option:

                      Record a script in Excel (new file, for example Excel Formatter.xlsm)

                      -Open File  (the file you want to convert)

                      -Save as (the name you want to save the .xslx as)

                      -Quit

                       

                      From Data Pump call the script in the pre-process tab.  You'll monitor for the .xls file, but the input to Data Pump will be the .xlsx.

                       

                      If anyone has better ideas feel free to chip in.  I can give you more details if you'd like to persue.

                        • Excel 5.0/95
                          LCampbell _

                          I had thought of that - recording a macro in excel to open the file and save as another version - but then wasn't sure how to call and kick off the macro in the pre-process script since this would be the first time I was attempting the script portion of DataPump.  I could use some tips on that.  Thanks.

                            • Excel 5.0/95
                              joey

                              When you have the process window up for your job, there is a script tab.  Click edit...

                              Under the PreProcessTabl, select insert script>, and select Execute an external command line.

                              Enter the path of your .bat file that calls the macro.

                              Enter a timeout limit

                              There will be a 'to do item added to the script for waht to do if the script fails.  Type the text return false.  That will cause your job to bomb if the macro times out.

                               

                              One word on Data Pump - it needs UNC paths, it can't handle drive mapping.  SO make sure in every piece including your script and macro - use UNC paths.

                               

                              This script will call the batch script (and hence macro) when Data Pump starts the process.

                               

                              There is a help topic about the script editor in the Data Pump help.

                               

                              Give it a try and see how far you get.  Let me know if you get stuck.

                                • Excel 5.0/95
                                  LCampbell _

                                  I am stuck.  Since the files actually do open in Monarch, couldn't you just have a pre-script that would open the Excel 95 file and save them as Excel 2003 or Excel 2007?  If so, how would you write that pre-script?  I can get an Excel macro to open the files and save them as another version but I still have to go in Excel manually and tell it run this macro.  I am trying to get the whole process to run hands-off.

                                    • Excel 5.0/95
                                      joey

                                      I'm not very familiar with VBA for excel, but you can start recording a macro, and then do your file save as, and save it as what you like, and then stop recording.  That should give you the VBA code needed.  I gave it a try with Excel 2007, and it was something like this:

                                       

                                      ActiveWorkbook.SaveAs Filename:= _

                                              "C:\Documents and Settings\user\Desktop\Test.xlsm", FileFormat:= _

                                              xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False[/code]

                                       

                                      Since your spreadsheet will be coming as a new file each run time, I suggest creating an Access database called Excel Formatting or something like that.  Create a new vba module.  You are going to have to have code to open up the excel file, and the perform the code you generated from the macro recorder. 

                                          Dim objXL As Excel.Application

                                          Set objXL = CreateObject("Excel.Application")

                                           

                                          objXL.Workbooks.Open (strWorkbook)

                                        'Insert code here

                                       

                                          objXL.ActiveWorkbook.Save

                                          objXL.Application.Quit

                                       

                                      /code

                                       

                                      Once you have this Access module tested to save your excel file as the new format, you can call it from a batch script. 

                                       

                                      Does that help you get any further?

                                      • Excel 5.0/95
                                        Data Kruncher

                                        I can get an Excel macro to open the files and save them as another version but I still have to go in Excel manually and tell it run this macro. /quote

                                         

                                        Open the file that has this macro, and add the following code to the ThisWorkbook code sheet:

                                        Private Sub Workbook_Open()

                                            'code to convert file format goes here

                                        End Sub[/code]

                                         

                                        Replace the comment with the code in your existing macro.

                                         

                                        Now to avoid having Excel prompt you to "Enable" the macros when you open the workbook, you need to configure Excel to automatically enable all macros when opening workbooks. Generally, this is not regarded as a great idea, but if you're certain that it'll be a safe activity on this machine, then in Excel click Office logo, Excel Options, Trust Center, Trust Center Settings... Macro Settings, and select Enable all macros. Click OK to save your changes and return to Excel.

                                         

                                        Edit: I see that Joey replied while I was composing this.

                                          • Excel 5.0/95
                                            LCampbell _

                                            Thanks.  The "ThisWorkbook" part helped.  I had found that tidbit earlier but had the wrong placement. 

                                            Open the file that has this macro, and add the following code to the ThisWorkbook code sheet:

                                            /QUOTE 

                                             

                                            The macro runs as soon as the excel file is open. 

                                             

                                            Now how do I call the macro workbook so the workbook will open on it's own?  Can DataPump call it and open it as part of the pre-script?

                                              • Excel 5.0/95
                                                Data Kruncher

                                                Out of my realm now, I'm afraid, as I don't use Data Pump.

                                                 

                                                I'm certain that one of the experts will be along shortly to fill in the details.

                                                  • Excel 5.0/95
                                                    joey

                                                    Question: Is the Excel 95 file you are using the same each time? Or do you get a new file each time with the source?  

                                                     

                                                    If it is a new file each month, you won't want the VBA code in the excel spreadsheet, as you will need to manually add it each time.

                                                     

                                                    If that is the case, you will need to have the code in another spreadsheet or in Access.  You need to open the spreadsheet and perform the code.  See my code examples from the earlier post on how to do that.

                                                      • Excel 5.0/95
                                                        LCampbell _

                                                        The data in the Excel 95 file is different each time.  The file name is the same - recycled.  The macro is in a separate file.  The macro says open filexyz.xls (Excel 95), save it as filexyz.xls (Excel 2003 or Excel 2007).

                                                         

                                                        Private Sub Workbook_Open()

                                                            Application.DisplayAlerts = False

                                                            Workbooks.Open Filename:="C:\scheduler\Monarch\input\Part1.xls"

                                                            ActiveWorkbook.SaveAs Filename:="C:\scheduler\Monarch\input\Part1.xlsx", _

                                                                FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

                                                            ActiveWindow.Close

                                                            Workbooks.Open Filename:="C:\scheduler\Monarch\input\Part2.xls"

                                                            ActiveWorkbook.SaveAs Filename:="C:\scheduler\Monarch\input\Part2.xlsx", _

                                                                FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

                                                            ActiveWindow.Close

                                                            Application.DisplayAlerts = True

                                                        End Sub

                                                          • Excel 5.0/95
                                                            RalphB _

                                                            LCampbell, I don't use Datapump so I can't answer if you can schedule the macro to run there but you can set up a Scheduled Task in Windows to run your script at a scheduled time and on certain days.  I have several set up to run at night and also early in the AM when I'm sure all the reports I need are done.

                                                              • Excel 5.0/95
                                                                joey

                                                                Ok, you'll need to call the macro from a batch script.  This is an example using Access, but I immagine Excel would be similar.

                                                                 

                                                                "C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE" "C:\TEST.mdb" /x "macDoStuff"

                                                                /code

                                                                Run it to make sure it works.

                                                                 

                                                                 

                                                                Next, you'll need to add this to the pre-process script.

                                                                 

                                                                -In the process window, click the script tag

                                                                -Click edit (lower left)

                                                                - Click the Insert script button and select Execute and External Command Line

                                                                -Enter the path of your batch script.  You'll probably also want to make it time out, say after 5 minutes. 

                                                                -In the code that was generated, you'll have a 'TODO line. Add the code return false. That will cause your process to bomb if the batch script times out.

                                                                 

                                                                Click OK on everything

                                                                  • Excel 5.0/95
                                                                    RalphB _

                                                                    Yes the batch file would be similar for Excel but you do not need to include the macro call portion since this is an autoexecute macro.  Just leave off the "/X .....

                                                                      • Excel 5.0/95
                                                                        LCampbell _

                                                                        I think that will work.  I have a batch script running the original report anyway so I can just throw that in the end of the batch and then kick off DataPump.

                                                                         

                                                                        Can either of you recommend a good reference material on batch scripts in general and/or scripting for DataPump? 

                                                                         

                                                                        Thanks.

                                                                          • Excel 5.0/95
                                                                            joey

                                                                            I wish I could, but I can't. Data Kruncher can probably help with batch scripting. I just google what I need to know, or use the windows help. I do VERY VERY little batch scripting, and it is usually copying and pasting from another example of something I did in the past.

                                                                             

                                                                            As far as the scripting in Data Pump, it is VB .Net code, so learning VB .Net would be helpful. There are some references and code examples in the Data Pump directory, but I haven't dived very deep into what can be done with Data Pump scripting. All that I usually do is call a batch script, which usually calls a VBA module if I need to do anything advanced.