Export tables to excel, including a macro

Question

For statistical reasons, I want an extensive analysis from a dataset. I already have a function that exports the data to Excel, but I have raw data that way; 500 lines, 35 columns, heaps of text sometimes...

Is it possible to include a macro into a function so that the excelfile is readymade to be analyzed?

I am using ASP, Javascript, and at the moment Excel 2003. This is the current function (written by one of my predecessors):

    function exporttoexcel()
    { //export to excel
        if (tableSortArray.length > 0)
        { 
          var t, arr;
          var tempArray=new Array();

          for(var i=0; i, i<tableSortArray.length; i++) {
              arr = tableSortArray[i].toString();
              arrr = (arr.split(","));
              if (i==0) { t = arrr[1]; }
              else { t += ','+arrr[1]; }
          }
          document.excel.t.value = t;
        }
        // I left out some mumbojumbo about sorting here
        document.excel.submit();
}

I mean macro's so that graphs are made "automatically" as well as some turntables...

Answer 1

Stolen from mrexcel.com (google + cut_paste = faster than typing):

' Delete any old stray copies of the module1
On Error Resume Next
Kill ("C:\MrXL1.bas")
On Error GoTo 0
' Export Module 1
ActiveWorkbook.VBProject.VBComponents("module1").Export ("c:\MrXL1.bas")
For x = 1 to 54   
ThisBroker = Sheets("BrokerList").range("A" & x).value

' customization of plan omited for brevity Sheets(Array("Menu", "Plan")).Copy NBName = ActiveWorkbook.Name
' new book name ' Import Module 1 to this new book Application.VBE.ActiveVBProject.VBComponents.Import ("c:\MrXL1.bas") ActiveWorkbook.SaveAs Filename:=ThisBroker ActiveWorkbook.Close Next x Kill ("C:\MrXl1.bas")

Alternatively you could also just setup a master excel file (say called "analysis.xls") that references the data in the "data" excel file, for example in a cell enter:

='Z:\excel-data[Current-data.xls]Sheet1'!$A$1

User opens up the master ("analysis.xls") and it in turn adds all the values from Z:\excel-data\Current-data.xls, just replace Current-data.xls with new data as needed.

Answer 2

Yes, that second option sounds better actually... I will need quite a lot of tables within the file, if I need to hardcode that into a function that might take a while. If I use a Master file, provide that as a downloadable source, write something so that it autoupdates upon opening... yes.

Thank you!