xla startup macro

[wasabi]

I have created a program that creates an xml file which is "fully-compatible" with Excel (as compatible as an xml file can be, anyway). I now want to create a macro which creates a graph out of the information available. I know that xml files cannot carry VBA/macro information, so I've turned to creating an .xla file which runs whenever a file is opened with Excel and creates the graph. However, I am unable to make the .xla file run its macro at startup, even for .xls files.

The .xla is composed only of Module 1:

Private Sub Workbook_Open()
With ActiveWorkbook
    Range("A1:B9").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B9")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
End Sub

I am quite an amateur at VBA, so I wouldn't be at all surprised if this is completely wrong.

If I go into the VB Editor and click "Run Sub/UserForm," the above code works just fine. However, it doesn't run automatically, which, by my understanding, is what Workbook_Open() is supposed to do.

So I am merely asking if there's some mistake in my work and, as a tangential question, is there a way of attaching a macro or .xla to an .xml file so that the user doesn't need to install the .xla file in their Application Data?

I am using Windows and Office XP.

Thanks,
Wasabi

 

[wasabi]

I apologize for the bump, but this is somewhat urgent.

 

[wasabi]

I'm no longer allowed to edit my previous post, so I'm forced to triple-post. My apologies.

I've moved the code to the .xla's ThisWorkbook and it now runs automatically at a file's startup. However, it now gets stuck on the code itself ("Method 'Range' of Object '_Global' failed" in the above code).

 

[xenou]

.xla files are hidden by default and also will start up every time a user uses Excel (assuming the addin is installed). I doubt you want your user to run this macro 20 times a day, or every time they use Excel however many times that is!

Can you not put the subroutine in a normal workbook? Then it would run only when they open that workbook. Also, it will solve the last error you have, since the workbook opened will also be the active workbook.

ξ

 

[wasabi]

Unfortunately, the files that would use the subroutine are .xml files. That's because they are automatically generated by a program I created in C++ and since .xls' code is proprietary, I couldn't exactly generate them in .xls. So I instead got a template file created as an .xls, saved it as an .xml and then altered it for my own uses.

The .xml files work just fine, but have the huge disadvantage of not supporting VBA, as far as I know (I'm hoping I'm wrong). My original idea was to do exactly that, to build the subroutine into the .xml file, or at least have some reference to the subroutine so that when the file opens, it launches the subroutine, but that's also impossible with an .xml file as far as I know. The files' names are somewhat predictable, so I could just build a restriction based on the worksheet's name, but I'll add that later. I'm just trying to make the super-simple version of it for now.

And I used Msgbox ActiveWorkbook.Name once to see that the .xla was indeed working on my "test.xls" (I'm not even trying it with .xml files yet) file, but the result was, unfortunately, "Graph.xla." I'm guessing that's why the Range method is failing.

 

[wasabi]

I've also noticed that using Application.[something] does not grant me access to any standard Excel functions. Application.round or Application.exact (the only two functions I've looked for but which I know should be present) give me an "object does not support method" error and IntelliSense doesn't bring them up, either.

 

[xenou]

You're looking for:

Application.[I]WorksheetFunction[/I].Exact()

I can't comment on the use of .xml files as containers for vba-executable code that would work with Excel graphs or whatever this means. Beyond me.