Open File for Input erring out[RESOLVED]

[perlmonk]

Im trying to code a macro using VBA for excel...and im having a problem trying to open a txt file for input.

I'll let the following code do the talking:

Const ICDUsersFile = 1
Open "C:\Program Files\MicroStrategy\Administrator\Command Manager\ICD_Users.txt" For Input As ICDUsersFile

Dim counter As Integer
counter = 1

Dim strTemp As String

While Not EOF(ICDUsersFile)
Line Input #ICDUsersFile, strTemp
UserArray(i) = strTemp
counter = counter + 1
Close #1
Wend

Close ICDUsersFile

I have made sure that the file specified in the path exists at the location. The syntax seems to be right...I have spent so much time on this, and i am still not being able to debug this issue...any correspondence will be highly appreciated!! The code errs out right after the open file statement...

Thanks in advance!!

[RobDog888]

Welcome to the Forums.

You need to either use the pound character '#' or use the FreeFile function
to assign the file handle number.

Open "C:\Program Files\MicroStrategy\Administrator\Command Manager\ICD_Users.txt" For Input As FreeFile
'Or
Open "C:\Program Files\MicroStrategy\Administrator\Command Manager\ICD_Users.txt" For Input As #1

[perlmonk]

Thank you so much for your help...one more question...how would I apply #1 to the while loop im using...

I tried While not EOF(#1)

but that doesnt seem to work.

Thanks again!

[RobDog888]

No prob.

Use this syntax.

While not EOF(1)

[perlmonk]

Okay...I tried both the modifications you suggested....i tried #1 and FreeFile both....its still not proceeding beyond that line (the open file for input statement).

Is it some declaration issue...?

Thanks,
Kunal

[RobDog888]

Post your modified code and what is the actual error message?

[perlmonk]

The code is as follows:

Open "C:\Program Files\MicroStrategy\Administrator\Command Manager\ICD_Users.txt" For Input As #1
Dim counter As Integer
    counter = 1
    
    Dim strTemp As String
    
    While Not (EOF(1))
        Line Input #1, strTemp
        UserArray(i) = strTemp
        counter = counter + 1
        
        Wend
         
    Close #1

The code skips onto the following errorhandler after that statement:

Err_createUsersScript:
    Call GlobalErrorHandler(Err.Number, Err.Description, "Subroutine: createUsersScript")
    Resume Exit_createUsersScript

[RobDog888]

If its erroring out on the Open line then I would say that either the file is not
there or spelling is different or are there any permissions on that directory?
This is how I would write it.

Open "C:\Program Files\MicroStrategy\Administrator\Command Manager\ICD_Users.txt" For Input As #1
    Dim counter As Integer
 
    counter = 1
    Dim strTemp As String
    
    Do While EOF(1) = False
        Line Input #1, strTemp
        UserArray(counter) = strTemp 'Should it be counter instead of i ?
        counter = counter + 1
    Loop
         
Close #1

You should have four references at a minimum for a standard exe project:
1. Visual Basic For Applications
2. Visual Basic Runtime Objects and Procedures
3. Visual Basic Objects and Procedures
4. OLE Automation

HTH

[RobDog888]

Just realized that this is in Excel VBA so your references should be:
1. Visual Basic For Applications
2. Microsoft Excel xx.0 Object Library
3. OLE Automation
4. Microsoft Office xx.0 Object Library

[perlmonk]

The References I have are:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library

Im not too sure, if those are the same references as you were suggesting...but, i tried to find the references that you mentioned in your previous post, and I could not find two of them in the available list (Visual Basic Runtime Objects and Procedures and Visual Basic Objects and Procedures).

Regarding the file name...the spelling is right..there is no permission on the directory...I'm being able to create a file in the same directory using open append, so there are no apparent issues with that. Concerning this input file (ICD_Users.txt), I have tried putting in integer data, character data, no data in this file...still, its not being able to open.

I am more of a perl hacker..and was kind of thrown into this VB project..so, I hope you can see where I am coming from. Once again, thanks for your interest and support!!

[RobDog888]

Those are the correct references for Excel VBA. The other ones I posted were for VB.

Try changing the textfile name to something like test.txt and see if that
works. Also, place a breakpoint before your error handler function to get the
original error number and description.

[perlmonk]

I tried changing the name from "text.txt" to "text" in the folder and it worked from the VB code, when i called for "test.txt". Bizzare stuff...but hey, it works.

Im facing another problem now...in the same while loop, I have this array which is string type and Im trying to equate it with a variable which is also string type (strTemp):

Dim strTemp As String
  
  
        Do While EOF(1) = False
        Line Input #1, strTemp 'XXX
        UserArray(counter) = strTemp 
        counter = counter + 1
    Loop
         Close #1

The error that I get when I try to equate the above, is "Subscript out of range" which is error number 9.

The input right now is purely numeric, but I believe that a string shud be able to hold a numeric value as string...nevertheless, I also tried changing both the array type and the variable (strTemp) type to integer...and then i get the error "Type mismatch" at the line in the code marked XXX

I tried to google for equating strings....dint really find anything in connection to what i was doing...any suggestions??

[RobDog888]

Ahh! I have seen that before. What it is is that you have your folder view
setting to hide extensions. So when you had named your file ICD_Users.txt it
was actually ICD_Users.txt.txt so thats why we were getting the error that
the file could not be found. That is why I wanted the original err msg.

As for the array error 9, arrays start at element 0 unless otherwise specified
in code. So change counter to start at 0 and you need to ReDim Preserve
UserArray in your loop to increase the number of elements since you dont
know how many lines are in the file.

[perlmonk]

Hey, that actually did work..thanks so much.

I have some other bugs in the code, but I'll open up another thread if I need assistance...thanks again!! Your assistance was really helpful.

[RobDog888]

No prob. Anytime.