1 Reply Latest reply: May 15, 2014 9:53 AM by Data Kruncher RSS

    Extracting information into password-protected Excel file?

    fgorelik _

      I update this particular Excel sheet on a daily basis from new PDF files that arrive daily. The problem is - it's password-protected and Monarch seemingly cannot decipher that to add to the data, which means that I have to extract into a new Excel file and then copy-paste manually. Is there a way around that?

        • Extracting information into password-protected Excel file?
          Data Kruncher

          I don't think there's a way around this within Monarch.

           

          However, this problem seems like an ideal candidate for a little automation, as it has the same steps every day. So...

           

          I'll be the first to admit that this isn't the most robust code ever written. More quick and dirty than anything, but it works.

           

          This is an Excel macro. You should be able to copy and paste it into a macro module in a new workbook.

           

          [font="courier"]Sub UpdateProtectedWorkbook()

           

              Dim objMonarch As Object

              Dim bSuccess As Boolean

              Dim sMasterBook As String

               

              Set objMonarch = CreateObject("Monarch32")

               

              bSuccess = objMonarch.SetReportFile("C:MyTestKruncher data.txt", False)

              bSuccess = objMonarch.SetModelFile("C:MyTestKruncher model.xmod")

              bSuccess = objMonarch.JetExportTable("C:MyTestKruncher Export.xls", WorksheetFunction.Text(Now, "mmm d yyyy"), 0)

              objMonarch.CloseAllDocuments

              objMonarch.Exit

               

              'password is "test"

              Workbooks.Open "C:MyTestKruncher test.xls", , , , "test"

              sMasterBook = ActiveWorkbook.Name

               

              Workbooks.Open "C:MyTestKruncher Export.xls"

              Worksheets(ActiveSheet.Name).Copy after:=Workbooks(sMasterBook).Worksheets(Workbooks(sMasterBook).Worksheets.Count)

               

              'save and close the exported data workbook

              ActiveWorkbook.Close True

               

              'save and close the master workbook

              ActiveWorkbook.Close True

          End Sub[/font][/quote]Change the folder and file names as necessary, and the password, and you should be OK. Oh, and go to the Tools menu and set a Reference to Monarch (check the Monarch box).

           

          Of course, if someone posts a Monarch based solution, I've had some fun programming.    

           

          HTH,

          Kruncher