Excel制造仪表板与vba [英] Excel manufacturing dashboard with vba

问题描述

我有一个excel表，详细列出了设备列表（工作表中约12000行）。我想制作一个仪表盘，我输入设备的名称，并返回制造商，制造日期和描述。

最好的方法是什么？我正在考虑编写VBA代码，以便将输入匹配到对象类型并返回所需的值，但是我没有在VBA中编码，我不确定如何输入。

解决方案

请原谅我提出一种不同的方法，但考虑可以将设备名称的SQL（结构化查询语言）的可扩展的关系优势参数，并将您的数据查询到已过滤的表结果集中。如果使用Excel for PC，Excel可以使用Jet / ACE SQL Engine（Windows .dll文件）运行SQL，这是启动其同级MS Access的引擎。这种方法避免了数组公式，循环，if / then逻辑，复数多索引/匹配和vlookup。

下面的示例提示用户输入设备名称使用 InputBox ，然后将其作为参数传递到SQL查询的 WHERE 子句。我们讨厌恶意用户运行 SQL注入在输入框中，如： 1; DELETE FROM [DATA]; 。示例假设数据存在于名为 DATA 的标签中，列标题位于第一行，空标签名为 RESULTS 。可以进行调整。

  Sub RunSQL（）
 Dim conn As Object，rst As Object，cmd As Object 
 Dim equipmentVar As String，strConnection As String，strSQL As String 
 Dim i As Integer 
 Const adcmdText = 1，adVarChar = 200，adParamInput = 1 
 
 Set conn = CreateObject（ADODB.Connection）
设置rst = CreateObject（ADODB.Recordset）
 
'RECEIVE USER INPUT 
 equipmentVar = InputBox（输入设备名称。 ，设备搜索）
如果equipmentVar =然后退出Sub 
 
'CONNECTION STRINGS（TWO VERSIONS）
'strConnection =DRIVER = {Microsoft Excel Driver（* xls，* .xlsx，* .xlsm，* .xlsb）}; _ 
'& DBQ = C：\Path\To\Workbook.xlsm; 
 strConnection =Provider = Microsoft.ACE.OLEDB.12.0; _ 
& Data Source ='C：\Path\To\Workbook.xlsm'; _ 
& 扩展属性=Excel 8.0; HDR = YES;; 
 
 strSQL =SELECT [DATA $]。制造商，[DATA $]。设备_ 
& [DATA $]。[制造日期]，[DATA $]。[说明]_ 
& FROM [DATA $]_ 
& 在哪里[DATA $]。[Equipment] =？; 
 
'OPEN DB CONNECTION 
 conn.Open strConnection 
 
'SET CMD COMMAND 
设置cmd = CreateObject（ADODB.Command）
 
与cmd 
 .ActiveConnection = conn 
 .CommandText = strSQL 
 .CommandType = adcmdText 
 .CommandTimeout = 15 
结束
 
'BINDING PARAMETER 
 cmd.Parameters.Append cmd.CreateParameter（equipParam，adVarChar，adParamInput，255）
 cmd.Parameters（0）.Value = equipmentVar 
 
'EXECUTING TO RECORDSET 
设置rst = cmd.Execute 
 
'COLUMN HEADERS 
对于i = 1 To rst.Fields.Count 
工作表（RESULTS） .Cells（1，i）= rst.Fields（i  -  1）.Name 
 Next i 
 
'DATA ROWS 
工作表（RESULTS）。范围（A2 ）.CopyFromRecordset rst 
 
 rst.Close：conn.Close 
 Set rst = Nothing：Set cmd = Nothing：Set conn = Nothing 
 End Sub 
  




I have an excel sheet detailing a list of equipment (about 12000 rows in the sheet). I want to make a dashboard whereby I enter the name of the equipment and it returns the Manufacturer, Date of manufacture and description.

What would be the best way to go about this? I was thinking of writing VBA code in order to match an input to an object type and return the required value however I have not coded in VBA and I'm unsure how to type it out.

解决方案

Forgive me in suggesting a different approach but consider the scalable, relational advantage of SQL (Structured Query Language) that can take the Equipment Name as parameter and query your data into a filtered table resultset. If using Excel for PC, Excel can run SQL using the Jet/ACE SQL Engine (Windows .dll files), the very engine that powers its sibling, MS Access. This approach avoids array formulas, loops, if/then logic, complex multiple index/matching, and vlookups.

Below example prompts user for the Equipment Name using an InputBox which is then passed as a parameter to the WHERE clause of SQL query. We hate for a malicious user to run SQL injection in input box, something like: 1; DELETE FROM [DATA];. Example assumes data exists in a tab called DATA with column headers in first row and an empty tab called RESULTS. Adjustments can be made.

Sub RunSQL()    
    Dim conn As Object, rst As Object, cmd As Object
    Dim equipmentVar As String, strConnection As String, strSQL As String
    Dim i As Integer
    Const adcmdText = 1, adVarChar = 200, adParamInput = 1

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' RECEIVE USER INPUT
    equipmentVar = InputBox("Enter name of equipment.", "EQUIPMENT SEARCH")
    If equipmentVar = "" Then Exit Sub

    ' CONNECTION STRINGS (TWO VERSIONS)
'    strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
'                      & "DBQ=C:\Path\To\Workbook.xlsm;"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='C:\Path\To\Workbook.xlsm';" _
                       & "Extended Properties=""Excel 8.0;HDR=YES;"";"

    strSQL = " SELECT [DATA$].Manufacturer, [DATA$].Equipment, " _
                & "   [DATA$].[Date of Manufacturer], [DATA$].[Description] " _
                & " FROM [DATA$]" _
                & " WHERE [DATA$].[Equipment] = ?;"

    ' OPEN DB CONNECTION
    conn.Open strConnection

    ' SET CMD COMMAND
    Set cmd = CreateObject("ADODB.Command")

    With cmd
        .ActiveConnection = conn
        .CommandText = strSQL
        .CommandType = adcmdText
        .CommandTimeout = 15
    End With

    ' BINDING PARAMETER
    cmd.Parameters.Append cmd.CreateParameter("equipParam", adVarChar, adParamInput, 255)
    cmd.Parameters(0).Value = equipmentVar

    ' EXECUTING TO RECORDSET      
    Set rst = cmd.Execute

    ' COLUMN HEADERS
    For i = 1 To rst.Fields.Count
        Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
    Next i

    ' DATA ROWS
    Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

    rst.Close: conn.Close    
    Set rst = Nothing: Set cmd = Nothing: Set conn = Nothing    
End Sub

这篇关于Excel制造仪表板与vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！