VBA Hint Needed - Change Way An Existing Project Grabs Data

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Thinking out loud here...wonder if the xlWhole option would work but where?

I'm unsure about that one as I've never used it.

garak0410

Though some research and assistance, looks like this might get me started...

Within this function:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String

sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function

Looking at adding the following:
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

Now to learn where to put this and what to do if it doesn't match...

garak0410

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String
sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String
sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?

dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String
sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?
dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)

Tried that, now it says COMPILE ERROR, OBJECT REQUIRED. It Yellow Highlights the Function line and blue Highlights XlWhole after the Set LookAt = .

I wonder if I need to make the variables Explicit and Public?

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:
@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Here's where I am now:

First up, as explained before, a part of this project looks for job numbers listed in Column W:

For Each rCell In Worksheets("REPORT").Range("W2:W50")
If IsEmpty(rCell.Value) Then Exit Sub
Debug.Print rCell.Value:
sJob = rCell.Value
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
As you can see, it then goes to a function called FindJobDir:

Function FindJobDir(ByVal strPath As String) As String
Dim sResult As String
sResult = Dir(strPath & "*", vbDirectory)
FindJobDir = UCase$(sResult)
Do While sResult <> ""
sResult = Dir
If Len(sResult) > 0 Then FindJobDir = FindJobDir & "," & UCase$(sResult)
Loop
End Function
What I need it to do is make sure what is listed in Range W2:W50 is an exact match for what it is looking for in that function. Basically, what is in W2:W50 need to match exactly to the folder it is looking for at the network location.

It has been suggested I try

Dim sResult As String
Dim sResults As Range
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt = xlWhole)

I still need to work on a condition and make a warning if it doesn't match but that is yet to come. I've put that last block of code into the function.

It fails with VARIABLE NOT DECLARED and highlights the entire variable declaration when I add that Set sRestults statement. It does refer to the strPath in that statement. Thinking the problem is there but it gives no hints to why it says this.

What about the LookAt = xlWhole? Maybe you should...?
dim LookAt
set LookAt=xlWhole
Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath, LookAt)
Tried that, now it says COMPILE ERROR, OBJECT REQUIRED. It Yellow Highlights the Function line and blue Highlights XlWhole after the Set LookAt = .

I wonder if I need to make the variables Explicit and Public?

What if you take out the dim, and LookAt=xlWhole completel, and remove the LookAt part on the last line so that it becomes..

Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath)

?

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath)

If I do that, it does finish the Macro but doesn't do the exact match...guessing now if Set sResults = Worksheets("REPORT").Range("W2:W50").Find(strPath) is what I needed, then I perhaps need a conditional statement now?

garak0410

What is interesting about learning while you debug is how lurching it can be...like right now, I don't think we need to key on strPath but insread sJob...sJob is the parsed strPath with just the job number...

It is declared and used in another area of code and not visible to this function so I need to get around that.

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

What is interesting about learning while you debug is how lurching it can be...like right now, I don't think we need to key on strPath but insread sJob...sJob is the parsed strPath with just the job number...

It is declared and used in another area of code and not visible to this function so I need to get around that.

Pass in sJob as another parameter to the function?

garak0410

When this macro goes to call FindJobDir, it is included in this line with sJob:

vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

When this macro goes to call FindJobDir, it is included in this line with sJob:

vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?

Possible. Does vJobFolders return a single folder, an array of folders?

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

When this macro goes to call FindJobDir, it is included in this line with sJob:

vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?

Possible. Does vJobFolders return a single folder, an array of folders?
When debugging the code step by step

At one point, vJobFolders does get the value of W2 assigned to it...that is after it is verified that is an existing folder at the network location......SJob equals what is typed into Column W but it still doesn't guarantee it will be at the network location that we are looking for an exact match for...

So thinking aloud, maybe if sJobs doesn't equal the value of sJob at some point, then it should throw up a message that the job folder doesn't exist. That way, not only will it match exactly, but also alert the user to a possible typo...

Maybe that is a little simplistic for my neophyte VBA mind...

garak0410

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

When this macro goes to call FindJobDir, it is included in this line with sJob:

vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")

Wonder if I need my little code to be after this line rather than in the function for FindJobDir ?

Possible. Does vJobFolders return a single folder, an array of folders?
When debugging the code step by step

At one point, vJobFolders does get the value of W2 assigned to it...that is after it is verified that is an existing folder at the network location......SJob equals what is typed into Column W but it still doesn't guarantee it will be at the network location that we are looking for an exact match for...

So thinking aloud, maybe if sJobs doesn't equal the value of sJob at some point, then it should throw up a message that the job folder doesn't exist. That way, not only will it match exactly, but also alert the user to a possible typo...

Maybe that is a little simplistic for my neophyte VBA mind...

And interesting...I think I may be getting somewhere but now the dreaded NEXT WITHOUT FOR message. I did next this idea in a FOR statement which reads:

For i = 0 To UBound(vJobFolders)

Should we do one for sResults?

Again, thinking out loud here...

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe that is a little simplistic for my neophyte VBA mind...

The simpler you can make it, the better off everybody will be, lol.

garak0410

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe that is a little simplistic for my neophyte VBA mind...

The simpler you can make it, the better off everybody will be, lol.

I still have a lot to learn, no time to learn it and still be Mr. Jack of All Trades SOLO IT Guy...I've literally poked at this code all week in between day to day and not a lot to show but perhaps closer to the solution.

dafyre

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@dafyre said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

@garak0410 said in VBA Hint Needed - Change Way An Existing Project Grabs Data:

Maybe that is a little simplistic for my neophyte VBA mind...

The simpler you can make it, the better off everybody will be, lol.

I still have a lot to learn, no time to learn it and still be Mr. Jack of All Trades SOLO IT Guy...I've literally poked at this code all week in between day to day and not a lot to show but perhaps closer to the solution.

Hopefully you'll get it! Keep poking at it... just don't miss and accidentally poke the bear with a stick!