Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data from file (VBA/Excel/97)

    Is it possible to extract a single data point from a text file using VBA without having to pull an entire row or column?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Data from file (VBA/Excel/97)

    What is the format of the text file you want to read? There is a specific set of methods for reading INI-style text files, and you can use ADO to read CSV-formatted text files, but for other text files, you usually have to develop your own code...

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    It's a comma delimited text file.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Data from file (VBA/Excel/97)

    If you use ADO, you can query it. Without ADO, I think you'd have to read in each row and examine it in your code. My old <post#=133021>post 133021</post#> shows the general outline of ADO access to a CSV file, but your query and other processing will vary. Hope this helps.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    Okay, I'm new to VBA and definitely new to ADO but I think I'm following somewhat what your code is doing. I got it to extract a data point, but I need it to do something more. My data file has the names of the months in column one and some named variables (e.g., Average Balance) in row one. Is it possible for me to extract the Average Balance for June? If so, how would I code this?

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Data from file (VBA/Excel/97)

    Assuming you create a recordset corresponding to the entire CSV "table," you could try the Filter property, which takes a value similar to SQL's WHERE clause (e.g., " field1 = 'June' "), or the Find method. I haven't tested either of these with this provider, and others Loungers may have better (more efficient) suggestions.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data from file (VBA/Excel/97)

    You can build the restriction into the recordset, by using an SQL statement. Something like this (substitute the correct folder and file name):
    <pre>Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:Test"
    rst.Open "SELECT [Average Balance] FROM [Data.csv] WHERE [Month] = 'June'", _
    cnn, adOpenStatic
    MsgBox rst![Average Balance]
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    </pre>

    Before creating this code, select Tools | References... in the Visual Basic Editor, and set a reference to Microsoft ActiveX Data Objects ... Library (there will be a version number instead of ...)

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    I appreciate everyone's help.

    I'm just a little confused on the syntax in the code. If I wanted to use an inputbox (or some other method) to define Month, how would I specify this? rst.open...where [Month] = ? ...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data from file (VBA/Excel/97)

    Try this:
    <pre>Dim strMonth As String
    ...
    strMonth = InputBox("Enter month name")
    If strMonth = "" Then Exit Sub

    rst.Open "SELECT [Average Balance] FROM [Data.csv] WHERE [Month] = " & _
    Chr(34) & strMonth & Chr(34), cnn, adOpenStatic
    ...
    </pre>

    The Chr(34) are quotes to surround the value from the input box.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    I tried this and several variations, but I keep getting an error message-"Too few parameters. Expected 1". I'm not sure what's going on here. Any thoughts?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data from file (VBA/Excel/97)

    This means that one of the field names is not correct, so ADO interprets it as a parameter. The field names must be exactly as in the file.

  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    If I simply put 'June' in for the strMonth verbiage, the code outputs the correct value for Average Balance. Is it possible that the VBA inputbox is passing too much data for the strMonth variable? For example, after the error, the de###### shows strMonth's value as "June" and I would have expected this value to be simply June (without the quotes).

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Data from file (VBA/Excel/97)

    Not sure where you're seeing that... There are (at least) three ways to inspect the value of a variable in running code in the VB Editor:

    1. <LI>Insert a statement that displays the variable in a message box.

      MsgBox strMonth

      <LI>Insert a Stop statement into your code at the point where you want to inspect the value, and either float your mouse pointer over the variable name or, if that doesn't work, open the Locals window. The Locals window usually will display strings in quotation marks (this does not indicate that they are part of the variable's value).

      <LI>Set up a watch on the variable and step through your code one step at a time using the Debugging toolbar.
    Now, in this case, you want to use apostrophes rather than quotation marks around June, so you need to change from Chr(34) to Chr(39), otherwise you are closing the condition before you supply the value, which causes the missing parameter message. At least that's what it looks like from here.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data from file (VBA/Excel/97)

    No, that is just the de######'s way of showing that it is a string value.

    But the error was mine. You must use Chr(39) instead of Chr(34). Sorry - Chr(34) works in Access, not in ADO.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from file (VBA/Excel/97)

    Thanks guys. I had originally used chr(39) for the single quotes, but I guess I also did something else wrong at the same time. It is working now. Go figure.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •