[Help] Help With Vb Programming

alo,

i tried some programming boards for help, but i haven't received much feedback. since i know some of you now, perhaps i can get some help here.

(btw, this has nothing to do with customizing... this is assistance needed for an independant project)

since i'm still learning programming, i don't really know too much about external data retrieval yet...

what i'm trying to do is the following:

have a text field... enter a number in it... click the search button, it does a search in an excel file for that string, if it finds it, a return of true, if not, then false...

plus, not sure if this is possible, have it so it doesn't open up excel... have the search done in the background?

would anyone here know how to do this, or can point me to a good reference point that can teach me...

by the way, as i appreciate anyone's help, this is vb only, don't rant to me about the joys of perl, c, or python... i'm trying to learn one language at a time...

thanks much

#140592

ask herd, he is also a VB-guru, if I am not wrong :D

#140595

herd? is that a name of someone here at A-S?

#140599

Yep, his avatar is a herd of penguins (I think. I know it's some animal and has black and white).

#140624

i sent him a pm... hopefully, he'll either see this or the message... but if anyone else has any info, by all means, jump right in.

thanks everyone

#140630

You can open up the spread sheet as a text input, just like you would any other file you would read or wrtie to/from, then search the for the string by reading the file. Youll read some screwd up charicters, but i dont think the xls format has many numbers in it. Kinda a ghetto way of doing it, but it gets the job done.

#140632

You can open up the spread sheet as a text input, just like you would any other file you would read or wrtie to/from, then search the for the string by reading the file. Youll read some screwd up charicters, but i dont think the xls format has many numbers in it. Kinda a ghetto way of doing it, but it gets the job done.

Don't do that, Excel files are a binary file format, so you don't know which byte representate which value. Also, there must be some kind of Automated way.

#140638

Originally posted by lostspyder@Apr 6 2004, 04:46 PM

You can open up the spread sheet as a text input, just like you would any other file you would read or wrtie to/from, then search the for the string by reading the file. Youll read some screwd up charicters, but i dont think the xls format has many numbers in it. Kinda a ghetto way of doing it, but it gets the job done.

what do you mean you don't think the xls format has many numbers in it?

the spreadsheet i want it to read from has the numbers... all in column 'a', going down to about #4000 or so.... + it has 4 different sheets in this file.

edit---i think i understand what you mean now... i don't know if that would work because it might bring over garbage text converting from xls to txt format.

#140642

Originally posted by AndreasV@Apr 6 2004, 10:55 PM

Don't do that, Excel files are a binary file format, so you don't know which byte representate which value. Also, there must be some kind of Automated way.

I cracked it open in notepad, and you can very clearly see the 'entrys' in the spread sheet. Any strings that excel puts in there are seperated by spaces, so thoes would return a not match. As long as it doesnt matter where in the file/cell# etc it is, it works. All of the garbage text is like squares, funny ys and stuff.

#140644

Is there any reason why you are using an xls?

#140646

I cracked it open in notepad, and you can very clearly see the 'entrys' in the spread sheet. Any strings that excel puts in there are seperated by spaces, so thoes would return a not match. As long as it doesnt matter where in the file/cell# etc it is, it works. All of the garbage text is like squares, funny ys and stuff.

That's the point. 'Garbage'-text are just normal bytes too!

And what if Microsoft decides to change the format to a less transparent format?

#140650

Originally posted by AndreasV@Apr 6 2004, 11:07 PM

That's the point. 'Garbage'-text are just normal bytes too!

well im undre the impresion there all ASCII value and intergers are 30h-39h, where all the other charicters arnt thoes, therefor searching the file for a set of charicters within that range of hex values would tell weither or not the string of intergers existed in the file.

Of corse if you looked through the binary of it, compairing bit by bit, im shure the string would pop up once or twice.

To try it out, just drag an excel file into notepad and do a find for an interger, it will show you weither or not it works (it works :P)

#140652

ok... let me try to explain what i'm doing, so it can provide a clearer picture of why i'm doing it like this....

at my job, we have a new selling program to try to improve our image... out of approx. 100000 different parts, 17000 are now guaranteed to be in stock or we pay for the shipping.

the ~17000 parts are on a spreadsheet so someone in our customer service dept can look it up to see if it's part of this guarantee. since i had created some other simple vb apps for my company in the past, someone suggested that i try to make one to make it easier to search than the spreadsheet, and also so they don't have to have excel open all day. (that's why i also asked about for it to be able to do it in the background without excel opening)

but this is the reason why it's reading from an excel file, because all of our part numbers are in these spreadsheets...

for the sake of doing the app, i can take it all out of the xls and do whatever i want with it, i thought keeping the xls file would be the easiest way.

so, someone types in '34524', if it found that specific part number, it says "YES" or if it's not found, "NO"

#140654

In that case, i would just make a whole new database program, it woulnt be that hard realy, just inputing thoes numbers would suck.

#140655

what do you mean make a whole new database prog?

#140766

Make a program that would use its own file to read from

#140837

It's no good idea to read Excel files in binary, unless you can parse so called structured storage files, since you may want to skip embedded images, word documents etc. correctly.

FYI, this is what I sent Chewbacca.

Hi, WookieTV

I haven't done much excel programming meself but I would do the following:

Within VB, Open Project/Preferences and check Microsoft Excel xx.xx Object Library, depending on the Version of excel you have.

Now hit F2, Object browser opens. Select excel within the upper combo box. Now you see all objects and properties excel has for automation.

The simplest you can do is:

Dim wb As WorkbookPrivate Sub Form_Load()    Set wb = CreateObject("C:\somepath\somefile.xls")End Sub

This will load some part of office, but the excel user interface remains closed.

Given this workbook object, you can enumerate its sheets, perform various actions on Range objects, which my be entire rows, columns or an arbitrary range of one or more cells. As for searching, I have no idea. In the object browser window, the second combo box does search the selected libraries object model. Typing search there, I find Excel.WorkSheetFunction.Search and SearchB functions, which seem to be replaced with Find & Replace functions in Office 2000 and later.

I'd guess you'd do the following, as from MSDN, this snippet replaces the value 2 with 5 in a determined range.

With Worksheets(1).Range("a1:a500")    Set c = .Find(2, lookin:=xlValues)    If Not c Is Nothing Then        firstAddress = c.Address        Do            c.Value = 5            Set c = .FindNext(c)        Loop While Not c Is Nothing And c.Address <> firstAddress    End IfEnd With

hth,

herd

PS: My Avatar is the cover of the Equinoxe LP, released about 1978, from Jean-Michel Jarre. He's a Synthesizer pioneer and had about 10 releases with instrumental synthesizer music. (C64 lovers, the "Trollie Wallie" jump and run game features the first three tracks of this LP)

#140900

thanks for your help... i put all of the data into a simple txt file instead...

herd (or anyone!), if you read this, maybe you can help me with the same thing, but with a text file instead, not an excel.

btw, if you're reading this last post first for some reason, i just want to be able to search for a user inputted string in a text file, if it finds it, little pop-up says 'found it' or if not 'not there'

(little graphical interpretation)

#141839

You gave up too soon with excel!

Anyway - here it is:

Public Sub FindPartNoInfile(ByVal FileName As String, ByVal PartNo As String)
 Dim fp As Integer, Line As String
 fp = FreeFile
 If Dir(FileName) = "" Then
   MsgBox "File Not Found!"
   Exit Sub
 End If

 Open FileName For Input As #fp
 Do While Not EOF(fp)
   Line Input #fp, Line
   If InStr(1, Line, PartNo) Then
     MsgBox "Found!"
     Exit Sub
   End If
 Loop
 Close #fp
 MsgBox "Not Found!"
End Sub

just call it with the filename and the part number as strings, be sure that

the text file has one part number per line, as in exporting CSV-files from excel.

hth,

herd

#142097

this worked for me, thanks herd...

#142653

i feel like an ass because i thought it was working right... it is somewhat, the only thing it's not doing right is that it's not searching 'whole word' only... e.g. if an entry in the text file is 19177, if you search for 9177 or 917, etc., it will come up with a result of "Found"....

herd, i'm going to request your assistance once again in implementing a whole word search only.

here's the code i used... changed a bit, it's all controlled under the search button_click.

Private Sub Command1_Click()
Dim fp As Integer, line As String
fp = FreeFile
If Dir("c:\Program Files\fast.txt") = "" Then
   MsgBox "Data file was not found", vbOKOnly, "File Not Found"
   Exit Sub
End If

Open "c:\Program Files\fast.txt" For Input As #fp
If Text1.Text = "" Then
   MsgBox "Enter a part number", vbOKOnly, ""
   Exit Sub
End If
If Text1.Text < 1000 Then
   MsgBox "There is a minimum of 4 digits", vbOKOnly, ""
   Exit Sub
End If
Do While Not EOF(fp)
  Line Input #fp, line
  If InStr(1, line, Text1.Text) Then
    MsgBox "This is a good part number", vbOKOnly, "Found"
   Exit Sub
  End If
Loop
Close #fp
MsgBox "Not a good part number", vbOKOnly, "Not Found"
End Sub

#142696

This depends on how the text is organized in the file.

Version a

19998

19999

20000

Version b

19998, "Toaster with bells on", 33.95$

19999, "Toaster with no bells on", 30.00$

20000, "Toaster with bells on and microchip brownness detector", 90.00$

For version a, change

 If InStr(1, line, Text1.Text) Then

to

 If line = Text1.Text Then

For version b, change it to

Dim FoundStr As String
FoundStr = Left(InStr(Line, ",")-1)
If FoundStr = Text1.Text Then

Or, if the position of the partno varies, you can use v = Split(Line, ",") to have an array of all items, v(0) will be the first item in Line etc. There is also a Filter command thats really fast. Send me an excerpt of your Fast.txt, tell me how large it can grow and I'll code you the fastest version.

hth,

herd

#142760

it looks like it's working 100% now... you were right with version a, just a long list of numbers.

thanks for your help and being so patient with me... this is helping me learn for a future project as well.

and how did you know my company sold toasters with bells and microchip brownness detectors?? are you a corporate spy!!?? ;):lol:

#142852

Originally posted by wookietv@Apr 11 2004, 06:09 PM

and how did you know my company sold toasters with bells and microchip brownness detectors?? are you a corporate spy!!?? ;)  :lol:

:lol: Just an educated guess. I am a freelance coder and have been working for various corporate bloodsuckers almost 15 years now. When I get the notion of the actual corporation's tick in my water, then its time for me to mount the saddle again.

(Lights a cigarette and rides into the sunset to the tunes of "Lonesome Cowboy")

#143198