Creating a text file for a database 2

[shogunmark69]

I have a macro with a function that has about 3k lines. This function contains rows of this:

If WC="123456" then Entity="a"
If WC="654321" then Entity="a"
If WC="012345" then Entity="b"

etc... i have written macro before where it will pull lines by line out a text file, but i would need this to search the file, and im not sure how to even start that.

any help would be greately appreciated!

 

[SkipVought]

Hi,

"...macro with a function that has about 3k lines..."

Chances are, your functon is not well designed or your logic could be better organized. 3,000 lines of code???

There must be a better way!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

...for instance, your example could be simplified...

select case WC
   case "123456","654321"
     Entity="a"
   case "012345"
     Entity="b"
end select

But, even more important, it would be imperitive to analyze the entire process: logic, data, purpose etc.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shogunmark69]

Thank you skip, your right, my coding is messy.. i have been picking apart other macros and learning on the fly, so your example will come in handy. I can clean it up that way. But the original file idea i had wouldnt be bad yet if you have quick way to explain some way to do that, then i could easily share any WC updates to my workgroup.

 

[SkipVought]

Post your code.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shogunmark69]

skip, is there a way i could email this whole thing to you? There is alot of propreitary info in this and would hate to post the whole code since i would have to change a ton.

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

You sent a .rar file

All i need a a TEXT file, please, with your VBA code.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

1. Make a text file Table with the DB code, WC & ENTITY values. Open the file and read into an array at the top. Replace all your xxdatabase functions with ONE lookup function using the array.

2. Avoid GOTO like the plague. What you have is typical of second and third generation programming language spagetti code. Strive to STRUCTURE you code so that it flows from top to bottom without GOTO statements. Look at some code examples in STAR posts.

Structured code generally has the following form...

If [i]expression[/i] Then
  'code for the TRUE result of the expression

Else
  'code for the FALSE result of the expression

End if

This control structure can be nested as well like...

If [i]expression[/i] Then
  'code for the TRUE result of the expression
   If [i]other expression[/i] Then

   Else

   End if
Else
  'code for the FALSE result of the expression
   If [i]tuther expression[/i] Then

   Else

   End if
End if

I never use GOTO. I occasionally will use a GoSub ... Return structure.

3. It is best to perform Functions by passing a ByRef (which is default) argument, rather than by using a PUBLIC variable. Check out the Function Statement in VBA Help.

4. A procedure generally ought to be no more than several dozen lines. Look for repeated series of statements to incorporate into Procedures that you can call. Also make procedures for chunks of code that perform a part of the process, like InitializeSession.

5. Put your Sess.Screen.Search values in an array and search in a loop.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

...also place all your variable declarations at the top of each procedure. Module level declarations cannot appear anywhere BUT the top of the module.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shogunmark69]

skip, i cant thank you enough for the tips you have given me thus far! Just with that little bit my code is looking alot cleaner and functioning better!

 

[SkipVought]

Glad to hear.

Believe me, it's a process. Code that I have written a year ago, I look at and chastise myself for having been so sloppy.

Working thru these issues, (first working the logic to refine it and then the code to streamline), is an evolutionary process. As you learn things, you will get better.

Believe me, the learning never stops! Press on!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[shogunmark69]

so i dont look like a troll... i ended up make a text file for something else and wanted to add it here to show how i did it so i could give back. Now i know skip suggested to avoid goto, but in this instance i wasnt sure on how else to accomplish my goal, im sure there is a way to specify on what to do if EndOfFile is reached. Also the format of the text file is:

"userid"
"userid1"
"userid2"

also you can name the text file anything, i personally named mine DoNotDelete.jpg so that it looks like an image, but the macro will still read it as a text file.

the code i used was:

Dim WN as Object
dim userid as string
'*********************************************************
Set WN = CreateObject("Wscript.Network")
userid = wn.username
dim blah As String
    open "\\Networklocation\textfile.txt" for Input As #1
    do while not eof(1)
        Input #1, blah
        If ucase(blah) = userid then goto authorized
    loop
    close #1

notauth:
            close #1
		Msgbox "You are not authorized to Execute this macro!"
		Exit sub
authorized:
    close #1

 

[Skie]

No gotos...

dim WN as Object
dim userid as string
'*********************************************************
Set WN = CreateObject("Wscript.Network")
userid = wn.username
dim blah As String
[red]dim isAuthorized
isAuthorized = false[/red]
    open "\\Networklocation\textfile.txt" for Input As #1
    do while not eof(1)
        Input #1, blah
        [red]If ucase(blah) = userid then 
            isAuthorized = true
            Exit Do
        End If[/red]
    loop
    close #1

    [red]If isAuthorized = false Then
        Msgbox "You are not authorized to Execute this macro!"
        Exit sub
    End If[/red]

 

[shogunmark69]

i love you guys.. you never cease to amaze me, i have learned so much by reading the countless threads on this board! and i really appreciate everyone's help!