Macros won't run unless File/Open is used.

[Ben Oster]

Hi there,

So this is a weird problem but I have no way around it as I've inherited some pretty crappy Excel workbooks.  I have a Excel Template with built in macros that call a bunch of other sheets and pages.  The template itself is stored on an server '\\svr-shares\' and I have no trouble opening or accessing some of it's basic macros so I've got the language for that.  The issue I'm having, and this is whether I run the Macros through python or if I run them through Excel, is that the Macros won't run to completion unless I FIRST Open Excel and SECOND File\Open the template itself.

Double clicking on the template causes the macros to fail no matter what.  My question is simpe:  Is there language that allows me to Open Recent or File open from inside Excel as opposed to calling the file from it's location?  I can't seem to get this to work.  Thanks for any help you may be able to give.  I'm running the following in case it makes a difference or y'all have had experience with these issues before.

Windows 7 Pro

Python 2.7.5

Excel 2010

Thanks again,

Ben

[John Yeung]

On Fri, Aug 2, 2013 at 9:40 AM, Ben Oster <oste...@gmail.com> wrote:
> The issue I'm having, and this is whether I run the Macros through
> python or if I run them through Excel, is that the Macros won't run
> to completion unless I FIRST Open Excel and SECOND File\Open
> the template itself.

Maybe I'm dense, but I don't understand "run the Macros through
python". If they're Excel macros, then presumably they're in VBA for
Excel. How would you run them *without* Excel? And how do you "run
them through Excel" without opening Excel first?

Are you using pywin32 or pywinauto or something like that?

John Y.

[Ben Oster]

Sorry for the confusion.  To clarify they are certainly running in excel.  The issue is that with the win32com.client I can call the excel workbook and open it but the Macros won't execute that way.  Same as if I simply double clicked on the file to open it.  They only way the Macros run is if I open excel and then use file/open or file/recent to open the workbook.  It's strange I know, but what I'm really looking for is a way to call the file/open or file/recent command and open the file through Excel, after Excel has already been opened.  Does that clear it up at all?  I know, it's a confusing situation and I'm struggling to properly explain it.

[Ben Oster]

Here is what is currently running:

ChDir "C:\502"

        Workbooks.OpenText Filename:="C:\502\SALES.XLS", Origin:=xlWindows, _

        StartRow:=1, DataType:=xlDelimited, TextQualifier:= _

        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _

         :=False, Comma:=True, Space:=False, Other:=False, FieldInfo _

        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 9))

    Columns("C:D").Select

    Selection.Style = "Comma"

    Range("A2:D42").Select

    Selection.Copy

    Windows("POSITOUCH-TEMPLATE.xlt").Activate

    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

    Sheets("BAILEYS").Select

    Range("A5").Select

    ActiveSheet.Paste

    Windows("POSITOUCH-TEMPLATE.xlt").Activate

    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

    Application.Goto Reference:="date"

    ActiveCell.FormulaR1C1 = "=+R[2]C-1"

    Range("B34").Select

    Windows("SALES.xls").Activate

    Application.DisplayAlerts = False

    ActiveWindow.Close

I've attempted to replace the activate with some of the updated vba info on MSDN but the only thing way I can get the macro to run is with a .open command which is really slow and opens a ton of workbooks instead of working inside of just the one.  I've tried using the .Activate as well with the format changes that I've seen on MSDN as well.  No luck.  This macro was originally written in 1998 and has just been passed down to me.  Is this a situation in which I'm going to have to completely rewrite the macros if I want them to run through Python?

Thanks,

Ben