Problem locating application directory

[gangstergrene]

Hi all,

I recently upgraded an application I had in VB 6 to VB.net (2005). In my old application I had some macros that presented data stored in text files in Excel (Results.xls). In my upgrade I had to copy these macros to a module in a seperate Excel spread sheet (Module.xls) where I execute them. I am still learning how to program in bot .net and vb6 so your help is greatly appreciated.

The application runs well as long as I clearly specify the location of the folder containing the text files and the spread sheet used to present the data. This is the same folder that contains my executable. An example of the code I used with the location clearly specified is:

ChDir "C:\Work\Pipe02\"
Workbooks.OpenText Filename:=("COMPMASS.TXT"), Origin:= _
        xlWindows, StartRow:=1,.........

The problem I am having is that I cannot specify the location of the executable or the text files as the user may choose to install the application in whatever directory he/she may use. I tried using App.Path but that gives the directory as "". It goes without saying that my application crashes as the text files cannot be located.

The code I tried to use is:

ChDir (App.Path & "\Pipe02\")
Workbooks.OpenText Filename:=(App.Path &"\COMPMASS.TXT"), Origin:= _
        xlWindows, StartRow:=1,.........

Your help will be much appreciated in resolving this problem.

[westconn1]

probably this should be in the dot net forum

a simple search i found this

Public Function App_Path() As String
        Return System.AppDomain.CurrentDomain.BaseDirectory()
    End Function

[gangstergrene]

Thanks for your reply. I used that function in my .net application. However, my problem is occuring with my Excel macro. Is possible to use that function also in my macro?

[westconn1]

app.path is not valid in excel, it has no idea where your vb6 (or net) program is running from, you would need to pass the path into the excel worksheet or code module or something, from you vb code

[gangstergrene]

Thanks again. Is there an example of a code I could use to do this? I was thinking of storing the application location as a variable in memory then read it in Excel. The problem is I am not sure of how to implememnt it.

[Hack]

However, my problem is occuring with my Excel macro.

Then this is a VBA question.

Moved to Office Development

[gangstergrene]

Any assistance with this problem is greatly appreciated

[RobDog888]

If you are doing this from within your vb.net app then use ...

Application.StartupPath()

[westconn1]

Application.StartupPath()

yeah, but he needs to pass this to his excel macro

[gangstergrene]

Thanks again for you feedback guys. Is it possible to store the location then access it in my Excel macro?

[gangstergrene]

I just found the answer I was searching for
I can use:

strFilePath =  ThisWorkbook.Path

This returns the directory the workbook is in, then I use

Workbooks.OpenText Filename:=(strFilePath & "\COMPMASS.TXT"), Origin:= _
        xlWindows,..........

to execute the macro.

Thanks again for your help an support guys .

[RobDog888]

Um, but as from your first post you said you were doing this in vb.net?