将表导出为ex​​cel，包括宏

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...

出于统计原因,我想从数据集中进行广泛的分析。我已经有一个将数据导出到Excel的函数,但我有这样的原始数据; 500行,35列,有时是一堆文字......

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

是否可以在函数中包含一个宏,以便可以对excelfile进行分析?

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

我正在使用ASP,Javascript,以及Excel 2003。这是当前的功能(由我的前任之一编写):

    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...

我的意思是宏,以便图表“自动”以及一些转盘......

1 个解决方案

#1

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

从mrexcel.com窃取(google + cut_paste =比打字更快):

' 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")

'为了简洁而省略了计划的自定义表(数组(“菜单”,“计划”))。复制NBName = ActiveWorkbook.Name'新书名''将模块1导入到这本新书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:

或者,您也可以设置一个引用“data”excel文件中的数据的主excel文件(称为“analysis.xls”),例如在单元格中输入:

='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.

用户打开主服务器(“analysis.xls”),然后依次添加Z:\ excel-data \ Current-data.xls中的所有值,只需根据需要将Current-data.xls替换为新数据。