Path Recognition?

[Bobby]

Hi,
I would like to know how/where to write global variable path depending
on the environment
so I don't have to comment or uncomment path!

In the General Declarations I wrote:


If ThisWorkbook.path = "K:\Indic_Entr\DEV" Then
Public Const pathgra As String = "K:\Indic_Entr\DEV"

else

if Public Const pathgra As String = "K:\Indic_Entr\TEST" Then

Public Const pathgra As String = "K:\Indic_Entr\TEST"

End If
End If

THIS does not WORK! Any suggestions?

Thank's ahead.

 

[Dave Peterson]

I'm not sure what you're doing, but why not just use:

Thisworkbook.path

when you need that string.

If you actually change the pathgra variable to something else, then declare the
variable outside any procedure and update it what you want:

Option Explicit
Public pathgra as string 'or Dim if the scope is limited to this module.
sub test()
if lcase(thisworkbook.path) = lcase("K:\Indic_Entr\DEV") then
pathgra = "K:\Indic_Entr\DEV"
...

End sub

 

[Bobby]

I'm not sure what you're doing, but why not just use:

Thisworkbook.path

when you need that string.

If you actually change the pathgra variable to something else, then declare the
variable outside any procedure and update it what you want:

Option Explicit
Public pathgra as string 'or Dim if the scope is limited to this module.
sub test()
  if lcase(thisworkbook.path) = lcase("K:\Indic_Entr\DEV") then
      pathgra = "K:\Indic_Entr\DEV"
  ...

End sub












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Make I lot of sense. Thank you Dave.

 

[Bobby]

I'm not sure what you're doing, but why not just use:

Thisworkbook.path

when you need that string.

If you actually change the pathgra variable to something else, then declare the
variable outside any procedure and update it what you want:

Option Explicit
Public pathgra as string 'or Dim if the scope is limited to this module.
sub test()
  if lcase(thisworkbook.path) = lcase("K:\Indic_Entr\DEV") then
      pathgra = "K:\Indic_Entr\DEV"
  ...

End sub












--

Dave Peterson- Hide quoted text -

- Show quoted text -

P.S: Dave I was trying to make these variables to all modules in the
Workbook

 

[Dave Peterson]

Thisworkbook.path will always be available.

But if that string could change, then make sure you put that declaration in a
General module:

Public PathGra as string

Then it'll be visible from any module and procedure.

 

[Bobby]

Thisworkbook.path will always be available.

But if that string could change, then make sure you put that declaration in a
General module:

Public PathGra as string

Then it'll be visible from any module and procedure.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave I did put this in may main module:

Option Explicit
Sub Picture1_Click()
Public pathgra As String

some code.........

End Sub


But I keep getting Compile Error:
Invalid attribute in Sub or Function!

Why?

 

[Dave Peterson]

Put the public statement at the top of the module--not within any procedure.

But again, if that is just a variable that holds the same value as
thisworkbook.path, I wouldn't use it. It's less informative and easier to mess
up.

 

[Bobby]

Put the public statement at the top of the module--not within any procedure.

But again, if that is just a variable that holds the same value as
thisworkbook.path, I wouldn't use it.  It's less informative and easierto mess
up.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

I will go to bed smarter tonight because of you! It works!
Thank's again!

 

[Bobby]

I will go to bed smarter tonight because of you! It works!
Thank's again!- Hide quoted text -

- Show quoted text -

Dave help!

I did the changes on my main module. In my module5 I do a verification
like this:

If Dir(pathSAP) = ""

The module does not react like before. If I stop it and look at the
path variable I get this:

pathSAP = "D:\................etc"

before I was getting only

"D:\................etc"

Please tell me why and how to fix it!

 

[Dave Peterson]

Are you seeing the "pathSAP = " along with the "D:\...." stuff?

How are you looking at this variable? How are you displaying what's in it.

Maybe you could add:

Debug.print PathSAP

and just check the immediate window when you're stepping through the code.

(I don't really understand what you're seeing and describing.)

 

[Bobby]

Are you seeing the "pathSAP = " along with the "D:\...." stuff?

How are you looking at this variable?  How are you displaying what's init.

Maybe you could add:

Debug.print PathSAP

and just check the immediate window when you're stepping through the code..

(I don't really understand what you're seeing and describing.)







Bobby wrote:









--

Dave Peterson- Hide quoted text -

- Show quoted text -

I am looking a it by stopping the execution and passing the cursor
over the variable. I hard code the path and it worked!
I when back to my main module and I get the same result looking at the
path right after it is assigned!

 

[Bobby]

I am looking  a it by stopping the execution and passing the cursor
over the variable. I hard code the path and it worked!
I when back to my main module and I get the same result looking at the
path right after it is assigned!- Hide quoted text -

- Show quoted text -

If I look at the variables with the locals(Locals Windows) it looks
OK. I Don't understand..!

 

[Bobby]

If I look at the variables with the locals(Locals Windows) it looks
OK. I Don't understand..!- Hide quoted text -

- Show quoted text -

Dave sorry I did not answer your question that was:
Are you seeing the "pathSAP = " along with the "D:\...." stuff?

The answer is yes!
Before the change with the public I was seen just:
"D:\..............etc"
now with the change I get:
"pathSAP = D:\................."

 

[Dave Peterson]

That's the way the VBE works. It's trying to help you see what's going on.

You're not doing anything wrong and neither is excel!

 

[Bobby]

That's the way the VBE works.  It's trying to help you see what's goingon.

You're not doing anything wrong and neither is excel!







Bobby wrote:





--

Dave Peterson- Hide quoted text -

- Show quoted text -

There is got to be something wrong with the DIR function!
What do you thing?

 

[Dave Peterson]

First, you may have noticed that in the MS excel newsgroups, most people
top-post. If you're going to hang out around here, you may want to start doing
that, too. (Yes, it's different than most of the rest of Usenet.)

But I don't understand what's wrong.

You wrote:

If Dir(pathSAP) = ""

But you didn't really explain what was in PathSap ("D:\...........etc" doesn't
mean too much to me) and what the problem is (what you expected and what really
happened).


(Or if you did explain it, I missed it.)

 

[Dave Peterson]

ps.

I'm assuming that pathSAP is really just a folder name.

If you're looking to see if that path exists:

if dir(pathsap & "\nul") = "" then
'not there

If you're looking for a specific file or filetype, you could use:

if dir(path & "\myfilename.xls") = "" then
'not there

or

if dir(path & "\*.xls") = "" then
'no .xls files there

========
Actually, I'd use something like this if there was a chance that the path
pointed at an unavailable drive:

Dim TestStr as string
teststr = ""
on error resume next
teststr = dir(pathsap & "\nul")
on error goto 0
if teststr = "" then
'not there
....

 

[Bobby]

ps.

I'm assuming that pathSAP is really just a folder name.

If you're looking to see if that path exists:

if dir(pathsap & "\nul") = "" then
  'not there

If you're looking for a specific file or filetype, you could use:

if dir(path & "\myfilename.xls") = "" then
    'not there

or

if dir(path & "\*.xls") = "" then
    'no .xls files there

========
Actually, I'd use something like this if there was a chance that the path
pointed at an unavailable drive:

Dim TestStr as string
teststr = ""
on error resume next
teststr = dir(pathsap & "\nul")
on error goto 0
if teststr = "" then
  'not there
...







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Actually I am looking if there are files in that specific directory.
Like your stated:
if dir(path & "\myfilename.xls") = "" then
"Not there"

But the point here is that DIR Function operate differently base on
how you define the variable.
I find that unusual coming from the function(DIR) or the EXCEL
interpreter!
But anyway thank you for your time!

 

[Dave Peterson]

If you're looking for a count of files...

Dim FSO As Object
Dim myPath As String

myPath = "C:\my documents\excel"
Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(folderspec:=myPath) Then
MsgBox FSO.GetFolder(folderpath:=myPath).Files.Count
End If

But I've never seen a difference in the Dir function. I bet there's something
else going wrong in your code.

 

[Bobby]

If you're looking for a count of files...

    Dim FSO As Object
    Dim myPath As String

    myPath = "C:\my documents\excel"
    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(folderspec:=myPath) Then
        MsgBox FSO.GetFolder(folderpath:=myPath).Files.Count
    End If

But I've never seen a difference in the Dir function.  I bet there's something
else going wrong in your code.







Bobby wrote:





--

Dave Peterson- Hide quoted text -

- Show quoted text -

For your info.
The only difference between the before and after is the definition:
Before in the: "Genaral declaration" definition I had this:

Public Const pathgra As String = "D:\............etc"

So if I would stop the module at the DIR(pathgra) statement I would
see: "D\...........etc"