VB Code gets different results with different users? - Anyone seen this before?

[Johnny Thunder]

Hi guys, saw something weird today. I wrote code that goes into my network at work and grabs a file from a specified folder for our current month "03 - Mar" and does a copy paste to another report. The issue that I saw today is that if another user runs the file the macro is grabbing the file from a prior month "02 - Feb" even though the code shows the march folder path.

Now, this isn't a case where the code is set to a drive letter that my computer has saved and another user may have that drive letter mapped to a different drive, I actually have the full network path to the server so it will work for any user.

Weird thing is we have about 5 files exactly like this but only 1-2 do this on occasion for certain users but never for me? Anyone seen this before?

 

[Joe4]

Without seeing your code, and what the contents of the folder are, it is hard to say.

 

[Johnny Thunder]

@Joe4 - Thanks for the response.

Below is my code. The workbooks.open line is where the code gets weird for other users but never for me.

Currently the code refers to the network destination 03 March, but a user manually updated this. But for some reason the user is taken to 02 February which is the original network destination before the update. Weird thing is, if I open the file after they updated to March, the code runs fine. I am so confused on this one.

Sub CopyfromOne()


Dim Ws1 As Worksheet, ws2 As Worksheet, nWS As Worksheet
Dim pl5 As Workbook, wb As Workbook, wb2 As Workbook


Set wb = ThisWorkbook
Set Ws1 = wb.Sheets("Tie out (H8)")
Set ws2 = wb.Sheets("PL_CONS Group Currency")
Set wb2 = ActiveWorkbook


Workbooks.Open ("\\WD\acctg\shared\RPTBUD\BUDGET\Actual\2018\03 March\Results\01 Tie Out\08 PL5 Report\" & File & "Income Statement (PL_5) YTD.xlsm"), UpdateLinks:=False


Set pl5 = ActiveWorkbook


Application.EnableEvents = False
Application.ScreenUpdating = False
With pl5
    .Sheets("PL_CONS Group Currency").Cells.Copy
        ws2.Range("a1").PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False


ws2.Range("k3").Value = "=TEXT(NOW(),""mm/dd/yyyy,  hh:mm am/pm"")"
    ws2.Range("k3").Copy
        ws2.Range("k3").PasteSpecial Paste:=xlValues
             Application.CutCopyMode = False
pl5.Close


MsgBox "PL5 Transfer Complete"


End With


ws2.Range("k3").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[Joe4]

Because I use the "Option Explicit" command at the top of all my code (very much recommended, as it helps catch errors), your code won't even run compile on my computer.
The issue is with this line here:

Workbooks.Open ("\\WD\acctg\shared\RPTBUD\BUDGET\Actual\2018\[B]03 March[/B]\Results\01 Tie Out\08 PL5 Report\" & [COLOR=#ff0000][B]File[/B][/COLOR] & "Income Statement (PL_5) YTD.xlsm"), UpdateLinks:=False

It looks like it is trying to use a variable named "File", that has not been declared or set to anything.
What is the purpose of this?
Is it a Global Variable declared elsewhere?
What does that code look like?

 

[Johnny Thunder]

Just went thru the code and there was no variable set with the "File" name. Still weird that the code runs fine for me but reverts to an old month for other users.

Because I use the "Option Explicit" command at the top of all my code (very much recommended, as it helps catch errors), your code won't even run compile on my computer.
The issue is with this line here:

Workbooks.Open ("\\WD\acctg\shared\RPTBUD\BUDGET\Actual\2018\[B]03 March[/B]\Results\01 Tie Out\08 PL5 Report\" & [COLOR=#ff0000][B]File[/B][/COLOR] & "Income Statement (PL_5) YTD.xlsm"), UpdateLinks:=False

It looks like it is trying to use a variable named "File", that has not been declared or set to anything.
What is the purpose of this?
Is it a Global Variable declared elsewhere?
What does that code look like?

 

[Joe4]

Have you personally verified it on the problematic people's computers?

The first thing I would do is go to their computer and make sure that they really have access to these drives.
So, open Windows Explorer, paste the following in the path: \\WD\acctg\shared\RPTBUD\BUDGET\Actual\2018\03 March\Results\01 Tie Out\08 PL5 Report\, and verify it goes to the correct place.
Then, try running the macro yourself on their computer and see if it works.

 

[Johnny Thunder]

Thanks for all the Help @Joe4 - Taking out that "File" line of the code seems to have corrected the issue. I have tried it on 2 different users computers and looks like were all good now.

Thanks!

Have you personally verified it on the problematic people's computers?

The first thing I would do is go to their computer and make sure that they really have access to these drives.
So, open Windows Explorer, paste the following in the path: \\WD\acctg\shared\RPTBUD\BUDGET\Actual\2018\03 March\Results\01 Tie Out\08 PL5 Report\, and verify it goes to the correct place.
Then, try running the macro yourself on their computer and see if it works.

 

[Joe4]

You are welcome.

One recommendation I have is to start using "Option Explicit". It helps identify issues like these, and typos you may have in your variables by forcing you to declare all variables before using them.
See here: http://www.excel-easy.com/vba/examples/option-explicit.html

I always use it as most serious programmers do! It helps to head off some common issues.

 

[Johnny Thunder]

I will give that article a read and start using that going forward. Thanks for the advice.