Marcro to open text file

[maacmaac]

I need help updating a Macro that opens a txt file, copies information from the txt file, and then pastes information into the excel spreadsheet that is open. The macro works fine now because I know where the txt file is located. The problem I have is that about 100 users will be using the excel file and will need to access a txt file that is exported to their own personal desktop on a daily basis. Is there anything I can do with the code that opens the file.

If the txt file is on "mike's desktop" the code to open is "C:\Documents and Settings\Mike\Desktop\trade_summary.txt"

If the txt file is on "Mary's desktop" the code to open is "C:\Documents and Settings\Mary\Desktop\trade_summary.txt",

In other words, the code would need to change as it relates to each user's desktop.

Please Login or Register  to view this content.

[shg]

How about,

Please Login or Register  to view this content.

[mrice]

You can make use of the

Please Login or Register  to view this content.

to deduce which user is using the macro and then use this to construct the string for the path.

[maacmaac]

Thanks for the response. I am not sure why I need to use the Dim s As String?

Please Login or Register  to view this content.

Can't I just use:

Please Login or Register  to view this content.

The marco works without the Dim s as String. Any comments as to if I really need this. The reason I did not use it is because I can't figure out how to embed it into the macro I have.

[jindon]

try

Please Login or Register  to view this content.

[shg]

The macro works without the Dim s as String. Any comments as to if I really need this.

If you put Option Explicit at the top of all your modules, as you should, you must declare all your variables for your code to compile.

If you don't, you will sooner or later be posting here to ask someone to explain some apparently anomalous behavior that is a consequence of not doing so.