Pulling information from a cell in a .csv file into a variable in Excel using vba

Question

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

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:

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

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

There's at least two paths you could take. 1) Open the .csv as a textstream and parse it for the information you want all in VBA, or 2) Open the .csv to a worksheet, use the worksheet functions (through VBA) to find what you want, then delete the worksheet. — OldUgly
– OldUgly, Commented May 4, 2016 at 17:32

basodre · Accepted Answer · 2016-05-04 17:38:00Z

3

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

answered May 4, 2016 at 17:38

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kefka Over a year ago

Thanks, this does appear to work for what I need. Now I just need to convert it to do what I actually require, which I think will be doable using this format.

Collectives™ on Stack Overflow

Pulling information from a cell in a .csv file into a variable in Excel using vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related