Excel - Reading a single value from a txt file into VBA

Hi All,

I can already do this, but I suspect my method, whilst good in the general case, is not efficient for this simple scenario - I have code already that opens a txt file, reads in N lines of data to an array etc etc etc.

But today I have a need to import a single value from a txt file and use it in excel.

The text file contains just that single value.  It can be assumed to always be a long - no need to do any error checking or type conversion for this question - it can never be anything else.

Assume the filename is:

C:\Temp\Alan.txt

and it contains just a number (as a text string in the text file I suppose):

4.5678

So, who can get it into VBA in the simplest code, and assign it to a simple Long variable called lOutput (say).

Points go to simplest, but shorter is good too (since the two aren't always the same!)

Thanks,

Alan.
LVL 24
AlanConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Dim FF As Integer
    FF = FreeFile
    Open "C:\Temp\Alan.txt" For Input As FF
    lOutput = Input(LOF(FF), FF)
    Close FF
0
AlanConsultantAuthor Commented:
Hi Martin,

Looks good, but it bombs out on:

lOutput = Input$(LOF(FF), FF)

with the error:

Run Time Error 62 - Input past end of file

I have attached the text file in case I misled on what it is / contains.

Alan.
Alan.txt
0
CharlesSenior Software EngineerCommented:
Public Function Get_Alan() As String
Open "C:\Temp\Alan.txt" For Input As #99
   Line Input #99, Get_Alan
Close #99
End Function

Hi above function will do
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martin LissOlder than dirtCommented:
I don't think that will work since there are two unpritable characters at the start of the file.

Try this. Note that lOutput is a Double


Dim FF As Integer
Dim lOutput As Double
Dim tmp As String
    FF = FreeFile
    Open "C:\Temp\Alan.txt" For Input As FF
    Line Input #FF, tmp
    tmp = Mid(tmp, 3)
    lOutput = tmp
    Close FF

Open in new window

0
AlanConsultantAuthor Commented:
Hi scharless,

That's the leader so far!

Is there any way to shorten / simplfy it?

Thanks,

Alan.
0
Martin LissOlder than dirtCommented:
To get rid of those characters like I did in my most recent code do

MyStringVar = Mid(MyStringVar, 3)
0
AlanConsultantAuthor Commented:
Hi Martin,

Can we be sure that it will always be exactly two of those 'random' characters?

Presumably there is some reason for them being there?  What causes it?

Alan.
0
CharlesSenior Software EngineerCommented:
Hi Alan,

Try the below

MyStringVar = Line Input Open "C:\Temp\Alan.txt"
0
Martin LissOlder than dirtCommented:
The first unprintable character is Ascii 255 and the second is 254.
0
Martin LissOlder than dirtCommented:
Here's what Wikipedia says about 255.
In word processing and digital typesetting, a non-breaking space, also known as a no-break space or non-breakable space (NBSP), is a variant of the space character that prevents an automatic line break (line wrap) at its position. In certain formats (such as HTML), it also prevents the “collapsing” of multiple consecutive whitespace characters into a single space. The non-breaking space is also known as a hard space or fixed space.


and 254 is called a "text qualifier" whatever that means.
0
AlanConsultantAuthor Commented:
Hi scharless,

The line:

MyStringVar = Line Input Open "C:\Temp\Alan.txt" 

Open in new window

give me an error:

Compile Error - Expected: End of statement

Do you get the same thing?

Alan.
0
Martin LissOlder than dirtCommented:
You can't do that intone line. See our previous code to see how Line Input is used.


BTW why do you care about the number of lines. Just pick one of the already working solutions (I don't care at this point if its mine or not) and go with it.
0
CharlesSenior Software EngineerCommented:
Hi Alan,

Ya, So please use the below function itself

Public Function Get_Alan() As String
Open "C:\Temp\Alan.txt" For Input As #99
   Line Input #99, Get_Alan
Close #99
End Function

you need to call Get_Alan() single line alone ,

Also in your attachment alan.txt some special character is taken place, This 2 special character may disturb your code, If this file is generated by any system , Please inform them to correct it. meantime just check using IsNumeric and alert a message saying not a number .Alan.tx opened using  DOS prompt
0
AlanConsultantAuthor Commented:
@Martin:

As I mentioned right up front - I can already achieve this without posting this question - I could have just re-rolled my library code reading the text file into a single element array and going from there.

The question is purely 'out of interest' as to how others would do this in the simplest, shortest way possible - elegance if you like.

@scharless:

That's well spotted - I had not noticed that the text file is 'unclean' in that way.  It is being generated out of a database application by someone else (FileMaker I think).  I will raise that with them, but potentially I have to just accept what it gives me anyway, and sanitise the inputs as we would often do anyway.

Thanks,

Alan.
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

If this is just as an exercise, then I will suggest a different approach:

Public Function dblRead_File(Optional ByVal strFilename As String = "c:\temp\Alan.txt") As Double

  dblRead_File = CDbl(Replace(Mid$(CreateObject("Scripting.FileSystemObject").OpenTextFile(strFilename, 1).ReadAll, 3), Chr$(0), ""))

End Function

Open in new window


Usage as a quick test:

Debug.Print dblRead_File

or to conform with your question:

Dim lOutput As Double       ' Although, the return is a Double data type; I would typically use dblOutput, or dblValue, or similar

lOutput = dblRead_File()

Arguably, the Function to read the file contents could be named lOutput, I suppose:

Public Function lOutput() As Double

  lOutput = CDbl(Replace(Mid$(CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\Alan.txt", 1).ReadAll, 3), Chr$(0), ""))

End Function

Open in new window



Note that a Double is returned (not a Long data type) as you mentioned in your question (due to the fact that the value inside the file is a decimal).

I also skipped the first two characters, as previously mentioned; Chr$(255) & Chr$(254).

Also, I had to Replace(...) the occurrences of Chr$(0) in the file due to the way in which the contents are stored.

<Chr255><Chr254>4<Chr0>.<Chr0>3<Chr0>6<Chr0>2<Chr0>5<Chr0>

Or in Hexadecimal:

FF FE 34 00 2E 00 33 00 36 00 32 00 35 00


The file's default location I retained as "c:\temp\Alan.txt" to match previous comments.

BFN,

fp.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Not shorter but just another way:
Function ReadValue() As Double
   With CreateObject("ADODB.Stream")
      .Open
      .LoadFromFile "C:\temp\alan.txt"
      .Position = 2
      ReadValue = CDbl(.ReadText)
   End With
End Function

Open in new window

0
[ fanpages ]IT Services ConsultantCommented:
^ :)

How long will it be before somebody just opens the file as if it were a workbook & reads the contents from the first cell?
0
Martin LissOlder than dirtCommented:
you say
@scharless:

That's well spotted - I had not noticed that the text file is 'unclean' in that way.

before that in post 39508013 I said
I don't think that will work since there are two unpritable characters at the start of the file.
and in the next post I described them so in frustration I'm out of here.
0
AlanConsultantAuthor Commented:
@Martin - Apologies, you did point it out earlier, so 'well spotted' to you.

As I mentioned above, this is a search for elegance, not a quick and dirty solution - what would be the point when I could have gone with what I had written years ago, and was already working perfectly well?



@FanPages - I had considered that, but it just felt like a poor option - there was always likely to be a better way :-)  However, this is my favourite so far:

Public Function lOutput() As Double

  lOutput = CDbl(Replace(Mid$(CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\Alan.txt", 1).ReadAll, 3), Chr$(0), ""))

End Function

Open in new window


It still has that hardcoded '3' (making the assumption that there will always be exactly two of those non-printing characters that Martin identified though).

It would be nice to cover off the possibility that database wonk doesn't muck it up by making a change to the format of the text file (even thought the spec says they will output it as a simple text file containing nothing but the numeric value).


@RoryA - Same issue with the hard coded position, but I like that too.


@All - I am waiting to hear back from the database guys today.  They should be able to output a clean text file I would hope.  It should contain purely this I think (in Hex):

34 00 2E 00 33 00 36 00 32 00 35 00

If they can't, then I will go with @FanPages version from above anyway, and accept the unfortunate reality of the hardcoded positioning, even though I just *know* it will be broken somewhere down the track :-)


Happy to take comments (with justifications) on which solution is 'better' to use though.


Alan.
0
[ fanpages ]IT Services ConsultantCommented:
@All - I am waiting to hear back from the database guys today.  They should be able to output a clean text file I would hope.  It should contain purely this I think (in Hex):

34 00 2E 00 33 00 36 00 32 00 35 00

If you are asking for a change, why not ask them to output this instead...

34 2E 33 36 32 35

i.e. exactly the value, without nulls between each character:

4.3625

?

My suggestion would then become:

Public Function lOutput() As Double

  lOutput = CDbl(CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\Alan.txt", 1).ReadAll)

End Function

Open in new window

0
AlanConsultantAuthor Commented:
@Fanpages - Yes, that is actually what I asked for :-)

I have no experience with FileMaker, so no idea if it can do that, but if not, the 'replace' on Chr(0) is okay.

Thanks,

Alan.
0
AlanConsultantAuthor Commented:
Thanks Gyus.  The database wonks couldn't work it out (or couldn't be bothered to work it out), so I said I'd fix their issue in Excel, and have hardwired the 'third character' in starting point, plus removing the Chr(0)s.

Thanks for engaging.

Alan.
0
[ fanpages ]IT Services ConsultantCommented:
:)

Thanks for the fun diversion from the normal question content, Alan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.