Display a text file to which the results are written..

[krishnak]

I have a form for the Access database that filters the records and writes to a .txt file in the "C:\Temp" folder.

At the end of the program, after I close the recordset and db objects, I want the macro to display the text file on the screen.
Then I can copy and paste the results in an Excel worksheet.

I am not finding the method Application.OpenTextFile in Access. There must be a simple solution for this but I could not get it from the internet.
There is a ton of info on reading, writing or appending to a text file by the FileSystemObject and TextStreams, but I need the file just to be displayed.

Can someone show me the direction how to do it?

[Imdabaum]

I'm not sure how you are writing out to the text file if it's as simple as an export, or the I/O Open statement or if you have some VBA that performs this, but ultimately if you can store the path you are writing to, then the following should work.


....
'Set filePath to the txt file path
Shell "notepad.exe " & filePath, vbMaximizedFocus

[krishnak]

Thanks, Imdabaum. The code works well.

I created a FileSystemObject and a TextStream to write the results to the text file.

[Imdabaum]

Just out of curiosity, was there any reason you didn't just use OutputTo, or TransferTEXT?

[krishnak]

There is no specific reason. I am more conversant with the FileSystemObject. I really do not know much about TransferTEXT etc.
Are they going to be more useful? I can go through the documentation for these functions and make use of them.

[Imdabaum]

I suppose it all depends on what your need is. If you are simply outputting exact data directly into a text file or excel sheet, then I think you might be able to cut down on a lot of code.

Ultimately though, if it ain't broke, don't fix it... unless you have time to refactor.

[krishnak]

I had a brief look at the functions. I find it more convenient to open an Excel workbook and export the values from the Access table with the Recordset properties.

Thanks for the tip. Maybe someday I need to use these functions.

[OBP]

Why not just write staright ot Excel, it would cut out the Copy & paste.

[krishnak]

In fact that is what I am doing. I declared a workbook (and worksheet) in the code and write the values of the recordset fields into the columns and rows of the worksheet.
This works pretty good.
Any suggestions about any special error handling for this type of writing to the Excel workbook?
Thanks for the response.

[OBP]

The only thing that I cam think of is to ensure that the data types are how you are going to want them in Excel, so it may need some modification in an output query.

[Imdabaum]

I know this is probably old and dealt with, but I also found this bit of code (http://forums.techguy.org/business-applications/473952-access-2003-correct-use-vba.html) that might be relavent to opening files that you have created in VBA, through exporting to txt or csv, etc. It allows you to not only open the output file, but it doesn't require you to use the corresponding application type (ie notepad.exe, Adobe.exe, etc) This code will allow Windows to open the file with the default application that handles that specific extension.

Shell "cmd.exe /c Start ""Window title"" ""path to file\file.ext""", vbHide
'Double quotes to pass as a single quote, in case of spaces...

[krishnak]

Thank you, Imdabaum, for the tip.

This would be very useful if I choose to open another file through VBA code.