为多个库存Excel文件创建摘要 [英] Creating Summary for multiple inventory excel files

问题描述

美好的一天！



我需要帮助，我想要做的是创建一个包含每天所有库存数据摘要的Excel文件。

这是我到目前为止的代码..

Good day!

I need help, want I want to do is create an excel file that contains the summary of all inventory data per day.
Here's the code that I have so far..

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim msg As Integer, objExcel As Object, objWorkBook As Object, objWorkSheet1 As Object, a As Double, b As Double, c As Double, sum1 as Double, sum2 As Double, Sum3 As Double
        msg = MsgBox("Form will close when summary report is generated. Continue?", 1, "Generate report?")
        If msg = 1 Then

' 1st file
            objExcel = CreateObject("EXCEL.APPLICATION")
            ExcelPath = "C:\Nov012014Inventory.xlsx"
            objWorkBook = objExcel.Workbooks.Open(ExcelPath)

            objWorkSheet1 = objWorkBook.Sheets(1)
            a = objWorkSheet1.Range("C8").Value
            b = objWorkSheet1.Range("C9").Value
            c = objWorkSheet1.Range("C10").Value

            objWorkBook.Close()
            objExcel.DisplayAlerts = False
            objExcel = Nothing

' 2nd File
            Dim SecC8 As Double, SecD8 As Double, SecE8 As Double
             objExcel = CreateObject("EXCEL.APPLICATION")
             ExcelPath = "C:\Nov022014Inventory.xlsx"  
             objWorkBook = objExcel.Workbooks.Open(ExcelPath)

             objWorkSheet1 = objWorkBook.Sheets(1)
             SecC8 = objWorkSheet1.Range("C8").Value
             SecC9 = objWorkSheet1.Range("C9").Value
             SecC10 = objWorkSheet1.Range("C10").Value

             objWorkBook.Close()
             objExcel.DisplayAlerts = False
             objExcel = Nothing


' 3rd File
              Dim ThrC8 As Double, ThrD8 As Double, ThrE8 As Double
            objExcel = CreateObject("EXCEL.APPLICATION")
            ExcelPath = "C:\Nov032014Inventory.xlsx"
            objWorkBook = objExcel.Workbooks.Open(ExcelPath)

            objWorkSheet1 = objWorkBook.Sheets(1)
            ThrC8 = objWorkSheet1.Range("C8").Value
            ThrC9 = objWorkSheet1.Range("C9").Value
            ThrC10 = objWorkSheet1.Range("C10").Value

            objWorkBook.Close()
            objExcel.DisplayAlerts = False
            objExcel = Nothing



' this is the code I have for adding each file
       sum1 = a + SecC8 + ThrC8 + FrC8
       sum2 = b + SecC9 + ThrC9 + FrC9 
       sum3 = c + SecC10 + ThrC10 + FrC10 


' this is where the new excel file is created where it sums up each excel file cells

            objExcel = CreateObject("EXCEL.APPLICATION")
            objWorkBook = objExcel.Workbooks.Add(Template:="Workbook")

            objWorkSheet1 = objWorkBook.Sheets(1)
            objWorkSheet1.Range("C8").Value = sum1
            objWorkSheet1.Range("C9").Value = sum2
            objWorkSheet1.Range("C10").Value = sum3
           Call objWorkSheet1.Protect(Password:="passw0rd")


             objExcel.DisplayAlerts = False
             objWorkBook.SaveAs(Application.StartupPath & "\" & "TEST_InventorySummary" & datenow)
             Process.Start("explorer.exe", String.Format(Application.StartupPath))


	objExcel = Nothing
            Me.Close()
End sub

它工作正常，但我会将其编译为.exe所以我想使用OpenFileDialog并选择期望的库存天数会很方便，但我该怎么做呢？请帮帮我，谢谢你将来的帮助上帝保佑！

It works fine but, I'll be compiling this to an .exe so I thought using an OpenFileDialog and selecting the desired days of inventory would be convenient but how am I going to do that? Please help me, thanks for your future help God bless!

推荐答案

使用OpenFileDialog的想法很好，但我建议以不同的方式做到这一点，它使用 FormulaArray 中的文件名[ ^ ]属性从多个工作簿中的范围获得总和：

The idea to use OpenFileDialog is good, but i'd suggest to do that in a different way, which uses file names in a FormulaArray[^] property to get sum from ranges in multiple workbooks:

DestinationWorksheet.Range("C8:10").FormulaArray="SUM('FullPathToFile\[Workbook1Name.xlsx]Worksheet1'!C8:C10, 'FullPathToFile\[Workbook2Name.xlsx]Worksheet1'!C8:C10, 'FullPathToFile\[Workbook3Name.xlsx]Worksheet1'!C8:C10)"

或使用间接 [<小时ef =http://support.microsoft.com/kb/151323target =_ blanktitle =新窗口> ^ ]从文本构建引用的公式 - 推荐方法！



尝试

or use INDIRECT[^] formula to build reference from text - recommended method!

Try

这篇关于为多个库存Excel文件创建摘要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！