Author Topic: Learning VBA  (Read 14017 times)

0 Members and 1 Guest are viewing this topic.

hendie

  • Guest
Learning VBA
« Reply #15 on: October 20, 2003, 10:32:27 AM »
Quote from: nivuahc
1. My company is cheap. I have Access but I'm the only one.

as long as the MBD is networked, users don't need Access installed, they just query the database through Acad using the MS JET engine.

Do you really want other users to access the text file ? someone can really muck things up if they aren't careful ! (again, if required, users can add records to the MDB using the same process)

hendie

  • Guest
Learning VBA
« Reply #16 on: October 20, 2003, 10:39:45 AM »
Quote from: nivuahc

How do I get the data in the TXT file displayed in the form (either in the combo box or the list box)?


check out Kenny's site ... here is a good intro to VBA and external text files.. http://www.afralisp.com/vba/extext.htm

nivuahc

  • Guest
Learning VBA
« Reply #17 on: October 20, 2003, 10:48:13 AM »
Quote from: hendie
as long as the MBD is networked, users don't need Access installed, they just query the database through Acad using the MS JET engine.

Do you really want other users to access the text file ? someone can really muck things up if they aren't careful ! (again, if required, users can add records to the MDB using the same process)



Actually, yeah, I do.

I make backups of every night so if a user mucks up the TXT file I can always restore the one from the previous day.

I'm also thinking that using TXT files would make this easy for anyone to use, not just us, and it would be helpful to have. Not everyone has MS Access available to them but everyone can edit a TXT file.

And, if I get really worried about someone mucking it up or if it gets to be a problem, I can always put the details and TXT files on the network and limit them to READ-ONLY for everyone but myself.

One of the perks of being the Network Administrator :D

Thanks for the link, I'll scour through it now. :)

hendie

  • Guest
Learning VBA
« Reply #18 on: October 20, 2003, 10:52:53 AM »
Okay, fair enough.
Once you've checked out Kenny's tutorial, have a go and let us know how you get on. any problems... give a shout.

hendie

  • Guest
Learning VBA
« Reply #19 on: October 20, 2003, 10:55:37 AM »
Quote from: Trev
Actually Hendie thats a good idea, easy to edit an existing entry etc. etc.
Just spark a whole load of new ideas in my head     :shock:
Damn you.................
 :P


heh

it might be worth starting a new thread if you decide to act on this, since nivuahc's going with the text file.
I suppose it will be good to have the two methods detailed so other users can make a comparison

Trev

  • Guest
Learning VBA
« Reply #20 on: October 20, 2003, 09:25:58 PM »
TO read an external file and place the contents into a list box

change the following line to suit the  location of your text file.
TextListFile = "c:\temp\testlistfile.txt"

also create a form with a combobox control named ComboBox1


Code: [Select]

Option Explicit
Dim TagList As New Collection
Dim TagListVal As Variant
Dim ArrayList() As String
Dim TextListFile As String


Sub Get_List()

On Error GoTo ERR_HANDLER
Dim iCounter As Integer
Dim sTemp As String
Dim nFile As Integer

    TextListFile = "c:\temp\testlistfile.txt"

    iCounter = 0
'   get the next free file number
    nFile = FreeFile
    ComboBox1.Clear
'   open the text file
    Open TextListFile For Input As #nFile
'   read value of each line in file, loop until the end of file
    While Not EOF(nFile)
        Line Input #nFile, sTemp
        ReDim Preserve ArrayList(iCounter)
        iCounter = iCounter + 1
        ComboBox1.AddItem sTemp
        TagList.Add sTemp
    Wend
    Close #nFile

Exit Sub

ERR_HANDLER:

MsgBox Err.Number & " " & Err.Description
Err.Clear
Exit Sub

End Sub



To input data into an external file
Code: [Select]

Sub Add2_TextFile()
On Error GoTo ERR_HANDLER
Dim myObject
Dim lay, i


'   get the next free file number
    nFile1 = FreeFile

'   open the file to append to
    Open TextListFile For Output As #nFile1

    lay = ComboBox1.List
    For i = LBound(lay) To UBound(lay)
        ReDim Preserve ArrayList(i)
        TagList.Add lay(i, 0)
'       write to the file
        If Not lay(i, 0) = "" Then
            Print #nFile1, lay(i, 0)
        End If
    Next
    Print #nFile1, TagListVal
'   close the file
    Close #nFile1

Exit Sub

ERR_HANDLER:
MsgBox Err.Number & " " & Err.Description
Err.Clear
Exit Sub

End Sub



Create a text file named testlistfile.txt and place the following lines into it.
Code: [Select]

This is a Sample Text List File
LINE 1
Line 2
line 3



Hopefully I got it all.
I also have one which allows for multi column arrays
eg: the text file contains info in the following format.
Electrical,LayerName,Description,LayerName,7,CONTINUOUS

this is good for adding description of items etc.
unfortunately I need a little fixing of my file.
my main file got corrupted and is no longer readable and I've slowly
but surely been rewriting it, (backup huh! whats a back up?)
Well my original was written in lisp and I was in the conversion process of making it a VBA program. (thats my excuse) I'll try and sort it out this arvo and post it later on.

Trev

  • Guest
Learning VBA
« Reply #21 on: October 20, 2003, 09:35:39 PM »
Yeah I'll do that Hendie.
It's definately worth doing the database version.
Like you say the user does not need ms access on their pc

nivuahc

  • Guest
Learning VBA
« Reply #22 on: October 21, 2003, 08:40:24 AM »
Quote from: Trev


To input data into an external file
Code: [Select]

Sub Add2_TextFile()
On Error GoTo ERR_HANDLER
Dim myObject
Dim lay, i


'   get the next free file number
    nFile1 = FreeFile

'   open the file to append to
    Open TextListFile For Output As #nFile1

.......







If you're Appending to the list, shouldn't that be

Code: [Select]
Open TextListFile For Append As #nFile1


Also, the variable nFile1 isn't declared anywhere (or am I missing something?).

Trev

  • Guest
Learning VBA
« Reply #23 on: October 21, 2003, 10:16:21 AM »
oops
I cut & pasted out of my program so I may have missed a couple of things
so if there are any other variables not declared just add them in.

Dim nFile1 As Integer

also you could try your suggestion and see how it works
Open TextListFile For Append As #nFile1

I know the method I used works, it does append to the end of the text file.
if both work all right, then take your pick. :)

nivuahc

  • Guest
Learning VBA
« Reply #24 on: October 21, 2003, 10:51:52 AM »
What actually led me to that was that I tried your method and it removed all of the lines from my file.

And, according to what I've read, Append would be the proper usage, no?

Remember, I'm no expert. :P

Trev

  • Guest
Learning VBA
« Reply #25 on: October 21, 2003, 07:47:07 PM »
Ok now I remember (I think)
I had a number of items in a listbox and what I probably did
was take all items in the listbox and replace whatever was in the existing
file. As I had a provision to saveas a different file. That way you could create a number of different text files, I also had a button to select your text file, so if you had multiple files you could just select the one you required.
Otherwise if you only have the one file and appending an item to the file then yes use 'Append'
Sorry about that.    :oops:
There was a method to my madness.

Ben

  • Guest
Learning VBA
« Reply #26 on: October 30, 2003, 10:54:49 PM »
Yep, perfect sense.

Here's a little function that you could use for reding the info into your form.

Option Explicit 'always a good habit to get into, forces you to define your variables
Sub GetTextInfo() 'This is the main sub heading

   Dim MyLine as String
   Dim MyVars() as String
   Dim VarCount as Integer
   Dim Cntr as Integer
   Dim SingleDig as String

'First, open your text file so that we can get stuff out of it.
   Open("path to your text file") for Input as #1
   VarCount = 0
   Do While Not EOF#1
        Input#1, MyLine
'Now to split the line up into it's two parts and put them into an array
        For Cntr = 0 to Len(MyLine) -1
              SingleDig = Right(MyLine, Cntr)
              If SingleDig = "|" Then
                   ReDim Preserve MyVar(0 to VarCount, 0 to 1)
                   MyVar(VarCount, 0) = Left(MyLine, Len(MyLine)-(Cntr + 1))
                   MyVar(VarCount, 1) = Right(MyLine, Cntr)
              End If
        Next Cntr
   Loop
End Sub

That will give you a two dimensional array that contains all the contents of your text file broken down into the two halves.

If that doesn't work (hey, who knows :) ) or you need further help in getting that info into a combobox, or listbox, then let me know.

nivuahc

  • Guest
Learning VBA
« Reply #27 on: November 03, 2003, 12:24:50 PM »
Thanks Ben!

Code: [Select]
Sub GetTextInfo()

Dim MyLine As String
Dim MyVars() As String
Dim VarCount As Integer
Dim nFile As Integer
Dim Cntr As Integer
Dim SingleDig As String

VarCount = 0
nFile = FreeFile

Open ("C:\clc\acadmenu\DETAILS\DETAILS.txt") For Input As #nFile

Do While Not EOF(nFile)
    Input #nFile, MyLine

    For Cntr = 0 To Len(MyLine) - 1
    SingleDig = Right(MyLine, Cntr)
    If SingleDig = "|" Then
        ReDim Preserve MyVars(0 To VarCount, 0 To 1)
        MyVars(VarCount, 0) = Left(MyLine, Len(MyLine) - (Cntr + 1))
        MyVars(VarCount, 1) = Right(MyLine, Cntr)
    End If
    Next Cntr
Loop

Close nFile

End Sub



Okay, I made a couple of changes to the above code (mainly adding the nFile[/i] variable and using FreeFile[/i] then closing the file at the end) and, I admit, this is where I'm having my main problems at the moment.

First of all, in the snippet you posted, at what point does the variable VarCount ever change to anything other than 0?

And I'm going to try to take this code line by line and explain it so that any of you reading this can see if I understand what's going on.

Please, please, please correct me when I'm wrong:

Code: [Select]
Sub GetTextInfo()

Dim MyLine As String
Dim MyVars() As String
Dim VarCount As Integer
Dim nFile As Integer
Dim Cntr As Integer
Dim SingleDig As String


Simple enough, I'm naming this routine GetTextInfo and I'm declaring the variables and their type.

Code: [Select]
VarCount = 0
nFile = FreeFile

Open ("C:\clc\acadmenu\DETAILS\DETAILS.txt") For Input As #nFile


I'm setting the value of VarCount to 0 and I'm setting the value of nFile to the next available free file number. Then I open my text file for input and tell the system that the file identified as nFile is in fact the text file I specified.

Code: [Select]
Do While Not EOF(nFile)
    Input #nFile, MyLine


Here I'm telling the system to keep doing what I tell it to until it gets to the end of my text file then I'm reading my text file for input. I'm setting the variable MyLine to equal the first line of text in the text file.

Code: [Select]
   For Cntr = 0 To Len(MyLine) - 1

Get the length (or number of characters) of the variable and set the variable Cntr to equal that number. Set a factor of -1 to be the increment that the variable Cntr changes each time it steps through the For loop.

Code: [Select]
   SingleDig = Right(MyLine, Cntr)
    If SingleDig = "|" Then
        ReDim Preserve MyVars(0 To VarCount, 0 To 1)
        MyVars(VarCount, 0) = Left(MyLine, Len(MyLine) - (Cntr + 1))
        MyVars(VarCount, 1) = Right(MyLine, Cntr)
    End If
    Next Cntr


Set the variable SingleDig to equal the last character in the variable MyLine, our line of text from our text file. If the character encountered is the pipe ("|") then this is where I begin to get confused.

According the the VBA help file concerning the ReDim statement:

Quote
The subscripts argument uses the following syntax:

[lower To] upper [,[lower To] upper] . . .

When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.


Also

Quote
The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless you’re using the Preserve keyword, in which case, no changes of data type are permitted.

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.



Okay, so in english, what does the next line of our code say? I have no idea.

But, putting that aside I'll assume, for the moment, that it does something spectacular. The next line, I believe, sets the first element in the variable MyVars to equal anything to the left of the pipe symbol. If that is, in fact what it's doing then the next line should be setting the second element in the MyVars variable to anything to the right of the pipe symbol. I could be wrong though.

Being stuck on what's supposed to be happening with that ReDim statement has my brain twisted in knots.

That being said, it appears that the For loop continues on to the proceeding character, working it's way backwards, in the line of text if the character it's reading right now isn't the pipe symbol.

Code: [Select]
Loop

Close nFile

End Sub


Not the end of our file yet? Loop back and do this all again until it is. Once finished, close the text file we opened and end our subroutine.

How's that so far?

nivuahc

  • Guest
Learning VBA
« Reply #28 on: November 03, 2003, 04:16:02 PM »
Okay, using Debug.Print I figured out that I'd need to change the code around a little bit. I renamed some of the variable because of personal preference and I changed some of the math around following that ReDim statement (still confused about that bit).


I made a form with a combobox labeled ComboBox1 on it and I added this code to it.


Code: [Select]
Private Sub UserForm_Initialize()

Dim MyLine As String
Dim MyVars() As String
Dim VarCount As Integer
Dim nFile As Integer
Dim CharCount As Integer
Dim Delimeter As String

VarCount = 0
nFile = FreeFile

Open ("C:\clc\acadmenu\DETAILS\DETAILS.TXT") For Input As #nFile

Do While Not EOF(nFile)
    Input #nFile, MyLine
    For CharCount = 0 To Len(MyLine) - 1
    Delimeter = Right(MyLine, CharCount)
    If Left(Delimeter, 1) = "|" Then
        ReDim Preserve MyVars(0 To VarCount, 0 To 1)
        MyVars(VarCount, 0) = Left(MyLine, Len(MyLine) - (CharCount))
        MyVars(VarCount, 1) = Right(MyLine, CharCount - 1)
        ComboBox1.AddItem MyVars(VarCount, 1)
    End If
    Next CharCount
Loop
End Sub



Seems to work great! But it's a bit deceiving:



Code: [Select]
Debug.Print VarCount
Debug.Print MyVars(VarCount, 0)
Debug.Print MyVars(VarCount, 1)



Addin that to the end of the routine, just before the End Sub bit will return the following:

Quote
0
POWER
Power Details


It seems that the pesky old VarCount is rearing it's ugly head. Putting on my LISP cap I add the following right after the ComboBox.AddItem' line:

Code: [Select]
VarCount = VarCount + 1

Which gives me a 'Subscript out of range (Error 9)'

What am I doing wrong and why?

Anyone?

SomeCallMeDave

  • Guest
Learning VBA
« Reply #29 on: November 03, 2003, 05:58:53 PM »
I think that error is caused because redim can only redimension the last dimension of an array.  Your code is trying to dimension the first of two dimensions.

One way (probably not the best way) would be to read your 2 strings into a single 2 element array and then store that array in another array.

Something like
Code: [Select]

Private Sub FileReadTest()

Dim MyLine As String
Dim SingleLine(0 To 1) As String 'new array
Dim MyVars() As Variant  'changed data type
Dim VarCount As Integer
Dim nFile As Integer
Dim CharCount As Integer
Dim Delimeter As String
Dim i As Integer ' new variable

VarCount = 0
nFile = FreeFile

Open "C:\testMe.txt" For Input As #nFile

Do While Not EOF(nFile)
    Input #nFile, MyLine
    For CharCount = 0 To Len(MyLine) - 1
    Delimeter = Right(MyLine, CharCount)
    If Left(Delimeter, 1) = "|" Then
        ReDim Preserve MyVars(0 To VarCount)
        SingleLine(0) = Left(MyLine, Len(MyLine) - (CharCount))
        SingleLine(1) = Right(MyLine, CharCount - 1)
        MyVars(VarCount) = SingleLine
        'ComboBox1.AddItem MyVars(VarCount)(1)  'remove comment to write to the combobox
        VarCount = VarCount + 1
    End If
    Next CharCount
Loop
Close nFile

'check to see if it worked

For i = 0 To UBound(MyVars)
  Debug.Print MyVars(i)(0) & " --- " & MyVars(i)(1)
Next i

End Sub


The debug.print shows how to access the individual elements.

Hope this helps.  Again, not the best way,  but a way