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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
Without seeing your code, and what the contents of the folder are, it is hard to say.
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
@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.

Rich (BB code):
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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:
Code:
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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
I will give that article a read and start using that going forward. Thanks for the advice.
 

Forum statistics

Threads
1,082,344
Messages
5,364,810
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top