使用vba将.csv文件中的单元格中的信息拉入Excel中的变量

I'm working on a vba macro for an excel sheet and I'm not sure how to go about doing one of my functions. I have a private sub in the macro that is used to get the path of a .csv file (say C:/files/file.csv stored as variable 'csvfile').

我正在为一个excel表格的vba宏工作,我不知道如何去做我的一个功能。我在宏中有一个私有子,用于获取.csv文件的路径(比如C:/files/file.csv存储为变量'csvfile')。

What I need to do at this point is automatically pull information from that csv file according to a certain formula and save it as a variable:

此时我需要做的是根据某个公式自动从该csv文件中提取信息并将其保存为变量:

=COUNTIFS(F2:F10000,"=medium",Z2:Z10000,"=Open")

So in summary, in a macro in spreadsheet Main.xlsx, I need to run the above formula on the file whose path is stored in variable csvfile, and save the returned number as a variable within the macro so I can make use of that number in my macro.

总而言之,在电子表格Main.xlsx的宏中,我需要在路径存储在变量csvfile中的文件上运行上面的公式,并将返回的数字保存为宏中的变量,这样我就可以使用这个数字了。在我的宏。

I'll need to do this nine times actually with the formula slightly different each time, but once I have the single variable worked out I think I'll be able to modify it to produce all the results I need.

我需要这样做九次实际上每次配方略有不同,但是一旦我得到单个变量,我想我将能够修改它以产生我需要的所有结果。

Thanks

1 个解决方案

#1

Here's an example of one way to do it:

以下是一种实现方法的示例:

Sub OpenAndCount()
    Dim sFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cnt As Long
    Dim rng1 As Range
    Dim rng2 As Range


    sFile = "c:\files\file.csv"
    Set wb = Workbooks.Open(sFile)
    Set ws = wb.Sheets(1)
    Set rng1 = ws.Range("F2:F100000")
    Set rng2 = ws.Range("Z2:Z100000")

    cnt = Application.WorksheetFunction.CountIfs(rng1, "=medium*", rng2, "=open")

    Debug.Print cnt
    wb.Close
End Sub