[RESOLVED] Can't see file opened with 'Open file for Input', etc, etc

[mikeymay]

I am using the fiollowing code to check for files in a dir and then open sequencially.

Do Until EGFile = ""
FileNum = FreeFile
Open EGFile For Input Access Read Shared As FileNum

'more code here'

Close FileNum
EGFile = Dir
Loop

My problem is that after the 'Open EGFile....' code I cannot see the file in Excel or use any code to pull data from various cell references within the worksheet. Do I need to activate the file somehow?


Thanks in advance.

[RobDog888]

In the "more code here" do you have the "Input FileNum, strMyVar"?

[mikeymay]

Just had code to pull bits of data from the 'opened' file.

Strangely did manage to get this to work yesterday, but can't make it work anymore.

What else do i need to activate the file?

[RobDog888]

What do you mean by cant see the file in Excel? Is it a csv or plain text?

[mikeymay]

Have just tried -

Workbooks.Open EGFile

and this seems to have worked.

I am somewhat confused tho. Why didn't 'Open EGfile for Input', etc work? Thought this would open the file for Read access?

[mikeymay]

File is .xls and when Alt+Tab to Excel App file isn't open.

[RobDog888]

Open EGFile for Input is Basic File I/O where as Workbooks.Open is an Excel method for opening a file IN Excel. One is behind the scenes and the other is in front of the scene, so to speak.

[mikeymay]

I see, so if I want to use 'Open EGfile', can I not then pull data from the worksheets?

Like I said, I did manage to do this yesterday, but unsure how I did it.

[RobDog888]

Yes you can but you need to either loop reading each line of the text file placing each value in the sheet or reading it in all at once into an array and porting that into your sheet.

[mikeymay]

So when using .xls files and needing data from specific cells I assume it would be best to use 'Worksheets.Open' command rather than 'Open EGFile' command?

If using this routine do I need assign a filenumber and then use 'Open EGFile' or can I do away with this code altogether?

[RobDog888]

If your opening xls files then you need to better use the .Open method. If you needing a plain textfile, no delimiters, the Basic File I/O is what you need.

[mikeymay]

Ok, think I am sorted now.

Many thanks for your help with this.

[RobDog888]

No problem Glad to have helped.

Ps, sop was it just a textfile or a xls file that you wanted to open?

[mikeymay]

file is a .xls

[RobDog888]

Oh, ok. Then definately yes as you would do a ...

Application.Workbooks.Open "C:\MyBook.xls"
MsgBox Application.Workbooks("MyBook.xls").Sheets(Sheet1").Cells(1, 1)

Type of logic. If you need to do this from inside Excels VBA then its easier then doing it from another language like VB6 although its almost the same.

[mikeymay]

Much appreciated!!!