Excel VBA Failing on one particular laptop (1 Viewer)

[JMongi]

Anyone run into a situation where a stable macro failed on just one laptop? Any ideas on what to look for?
I've checked refererences, added debug displays. Can't seem to figure out A. What is happening. B. Why it's happening.

It's related to pulling the username and saving the file automatically. The path displays properly using MsgBox but then no save is actually accomplished (filename is not changed and no file shows up on the desktop of the user). Same OS, up-to-date office. No other users having issues. It's just odd. I'll try and post more info if I can on Monday.

 

[JMongi]

I realize actual code would be helpful. Here is the Temp_Save macro on button click:

Sub Temp_Save() 'Subroutine run on click of "Temporary Save" button

On Error GoTo EH
ErrName = "Temp_Save"
Call Unprot

Module1.BttnChk = 1     'For use verifying the save was initiated by a proper button click.
Module1.SaveName = "FSR Temp"

'Updating the report date before save
Application.EnableEvents = False 'turn events off!
Range("AE59") = Format(Now(), "mm-dd-yyyy hh:mm:ss AM/PM")
Application.EnableEvents = True 'turn events back on
Application.DisplayAlerts = False   'Disable alerts to automatically overwrite file

Call HideSheets

ThisWorkbook.SaveAs _
    Filename:=UserDesktop() & SaveName & ".xlsm", _
    FileFormat:=52

Application.DisplayAlerts = True    'Renable alerts

'User Display of Save Success
MsgBox "Filename = " & SaveName & vbNewLine & "File is saved to your desktop."

Module1.BttnChk = 0     'Reset BttnChk variable

Call UnhideSheets

Call Prot

Exit Sub

EH:
Call ErHa
Resume Next
      
End Sub

Here is the userdesktop code:

Public Function UserDesktop() As String
ErrName = "UserDesktop"
Module1.UserPath = Environ$("USERPROFILE")
Module1.Path = UserPath & "\Desktop\"
MsgBox "Path is " & Module1.Path
UserDesktop = Module1.Path
'change of stuff

End Function

 

[The_Doc_Man]

I would presume that ErHa does not get called? Otherwise you would know that an error had occurred. I might suggest setting a breakpoint - but there is also something you should disable as an IMMEDIATE test. Don't set .DisplayAlerts to False (at least for debugging).

 

[arnelgp]

see this code:


Public Function UserDesktop() As String
ErrName = "UserDesktop"
Module1.UserPath = Environ$("USERPROFILE")
Module1.Path = UserPath & "\Desktop\"
MsgBox "Path is " & Module1.Path
UserDesktop = Module1.Path
'change of stuff
End Function


should be:

Public Function UserDesktop() As String
ErrName = "UserDesktop"
Module1.UserPath = Environ$("USERPROFILE")
Module1.Path = Module1.UserPath & "\Desktop\"
MsgBox "Path is " & Module1.Path
UserDesktop = Module1.Path
'change of stuff
End Function

 

[JMongi]

@The_Doc_Man - ErHa does get called. The error is being first generated in the UserDesktop function I posted. Oddly, though, as I mention below, the MsgBox line always resolves correctly despite the error. Thanks for the tip on the alerts! I forgot that I toggled off the alerts to bypass some prompts. That will help.
@arnelgp - The line MsgBox "Path is " & Module1.Path line has always displayed the correct path even while the macro errored. However, you are correct that it is not consistent. Might as well fix it.

The trickiest part has been trying to troubleshoot this remotely through the end user without being able to use the VBA environment for troubleshooting. It sounds like our 3rd party IT company will figure out a way for me to remote into this laptop. Then I can actually try and figure out what's going on.

Thanks for the help. I may post back here again once I've got some more useful information.

 

[Isaac]

I would almost be certain the problem is with the desktop. meaning the file IS saving somewhere - but maybe not the path you think should be considered their desktop. After using excel vba across probably 15 employers, all of them corporate type settings, I've found that even there - in strict environments - the path to the desktop might be tricky. In fact, just a few months ago, I had to deploy a 'test' to Citrix users to determine what their path to the desktop was. I deployed the test with FOUR ways of getting the desktop. I settled on the way that worked correctly for all users, Citrix and non-Citrix.

But Citrix was just 'my' wildcard factor - you may have others.

For example, once I switched from Contractor to FTE. They changed my network user id, but were lazy about resetting my windows profile on my laptop. Thus, my path to my desktop was actually NOT user profile\desktop (or something like that). It was olduserprofile\desktop.
Furthermore, the awfully hideous proliferation of OneDrive has messed things up royally. Some user's path to their desktop as seen by VBA actually has the word "onedrive" in it.
In this case I am referencing for my own path, code like userprofile\desktop actually WOULD have returned a path that existed - BUT - I would not have seen any file created on what I considered to be my desktop, because there was another desktop folder (or 2) in existence.

Desktop paths are all about windows profiles, environment variables, and nasty unpredictable stuff like ONedrive installations.

Thus, I would recommend you play around with the several ways of getting a desktop.

"c:\" & environ("username") & "\desktop"

createobject("wscript.shell").specialfolders("Desktop")

+ the way you are using

Plus I think there is one more I can't remember now and no longer work at the place where my recent code was.

Easy way to troubleshoot, deploy them a test file that records the result of all of those methods. That's what I do anytime I have doubt.

lastly, just as possible as a cause- definitely try doing this while commenting out application.displayalerts=false. In your particular case in this code, turning alerts off may have a similar effect as ignoring errors. the file may be saving to some other default location (rather than asking a user "you can't save to this location, do you want to save somewhere else) instead of asking the user.

also remove your error handler completely, then tell the user do click Debug (if you can afford to give them a non-pw-protected vba project), which will be like troubleshooting gold.

 

[JMongi]

@Isaac - That was my initial thought as well after perusing some similar internet posts. But, my msgbox line I inserted has always shown the correct path. That's why I'm confused.

I've definitely seen a robust desktop function that basically cycles through all four possibilities you are referencing with various validation checks along the way. I do believe that pulling the username and then appending desktop is not the best way to do it. I belive the special folders way is the "best" presuming that it works. I believe there is also an API call that can be implemented with the various 32/64 bit caveats and complexity typically associated with it.

Hopefully, I'll be able to do all of the mucking around debugging myself soon. I'll post here once I discern the issue, thus recording it for all time!

 

[The_Doc_Man]

It occurs to me that since you are opening an Excel object in order to be able to reference "ThisWorkBook" that the file MIGHT be listed in the "recent files" list, which you could examine to see where it went. Not going to swear to that - but it's a simple test so doesn't cost too much time.

 

[Isaac]

or since it's a SaveAs, simply add a msgbox showing ThisWorkbook.Fullname and tell the user to send you a screenshot

 

[JMongi]

So, i've been able to remote in, comment out my error handling and reenable alerts and...

Error 400

So, in the code I posted above everything gets to the SaveAs block without a problem.
The SaveAs block is initiated
This triggers the BeforeSave subroutine

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'On Error GoTo EH                'Error Handling when workbook is unprotected so it doesn't lose it's protection on an error.

If Module1.BttnChk <> 1 Then     'Save Initiation Check
    Cancel = True
    MsgBox "Please Use Form Save Button", vbOKCancel + vbExclamation, "SAVE CANCELLED"
    Exit Sub
End If

Exit Sub

EH:
Call ErHa
Resume Next

End Sub

When I set a break and step through the code there are no issues all the way to the completion of the If statement (thus passing and authorizing the actual Excel saveas) and then on the way back to the calling subroutine...BAM...error 400.

 

[The_Doc_Man]

Excel run-time error 400 relates to a problem in working with files.

VBA Error 400

In this tutorial, you will learn why VBA Error 400 occurs and how to deal with it while write a VBA code.

excelchamps.com

VBA Error 400: What Are the Causes and How to Fix It? - VBA and VB.Net Tutorials, Education and Programming Services

Contents Introduction It is often said that debugging is part of programming, and through error messages, the programmer can easily identify where there are problems in his/her code. But as you can see from the above screenshot the VBA Error 400 is one of those

software-solutions-online.com

Redirecting

answers.microsoft.com

The suggestions have in common that most likely one of two things is going on.

A. Corrupt code requiring that you move it (via copy/paste) to a new module, delete the old module, and recompile
B. Some confusion regarding the file name/path such that it THINKS the file was created to one location but closed in another, OR the file no longer exists when trying to save it.

 

[JMongi]

It's definitely "B". The sad thing is that this is all a result of the one time I personally didn't setup this laptop.
Our third party IT company set this up at our main office location (I work at our production shop). They managed to completely screw it up. All the user locations are pointing to the office server location for some reason instead of the local laptop. So everything is pointing to a server that he doesn't have access to. *sigh*

 

[Isaac]

So it did turn out to be the desktop or save path? Sort of?
Just trying to follow all this.

I had no idea there was a before save event, that definitely adds a lot of complexity potentially.

One thing I got used to was find and replace all On Error with 'On Error in the vba project. Can be very useful at times!

 

[Isaac]

Oh, one more thing. Are you familiar with the SaveAsCopy method? It's potentially very useful.
I used to use only SaveAs and I had to get so creative and complicated with it all but SaveAsCopy can come in handy too..depending..

 

[The_Doc_Man]

It's definitely "B". The sad thing is that this is all a result of the one time I personally didn't setup this laptop.
Our third party IT company set this up at our main office location (I work at our production shop). They managed to completely screw it up. All the user locations are pointing to the office server location for some reason instead of the local laptop. So everything is pointing to a server that he doesn't have access to. *sigh*

I fully understand a deep sigh at this point. The good news is that you have now got a handle on it. As I often tell folks, FIXING a problem is easy. It is finding it that is the hard step.

 

[JMongi]

@The_Doc_Man - Definitely a *sigh*. So, I shouldn't malign our 3rd party IT. They didn't "screw it up". But, they did appear to make some assumptions that burned me (and me making assumptions too in the code).

@Isaac - The SaveAsCopy method is interesting. I originally did SaveAs for a few reasons. I'll have to revisit my process logic and see if SaveAsCopy might be more useful.

So, the grand story is that I have taken on the mantle of local IT at our production shop. I've been slowly getting things organized, tagging and recording device information, standardizing on setup for new users, etc. The general process for a new user is for me to ensure that the new (to them) device has our remote monitoring software installed and then the 3rd party IT company handles all the user profile/email setup (we use Exchange Server and Active Directory I believe). There is a server at the main office and a server at our production shop. They are glorified file servers but they also manage the active directory/users.

For logistic reasons, it was decided to do some laptop swaperoo for a couple of field service techs at our main office location instead of here where they usually are accomplished under my purview. At the risk of venturing into the land of assumption again, it appears the standard user profile setup in our office location is done ON their server with Sync Station active so these sever files can be worked on locally offline. I've not seen ANY of the devices I've worked on configured in this way. They've all been local user profiles. So, even though they were supposed to be setting these up for me for use through our shop, they configured it for the office because that was the network they were connected to at the time when they were remotely connecting. Really a minor thing but it's caused me a month of headaches at this point.

So the "displayed" path of the Environ() call was correct but didn't actually exist locally in the real file structure. It was pointing to //<SERVERNAME>/users/<username>. Then I was trying to direct write to that location which was inaccessible, lacking permissions, or both (I didn't really investigate further to find out). It sounds like using a scripting object MAY have saved me a headache. But I haven't tested it out to see. That's the point I'm at now. The problem is that since I have no systems set up in such a manner, I can't really test against that scenario.

 

[JMongi]

All this has really done is add more weight on this "this needs to be redeveloped as a database form instead of an excel file" side of the equation. The only tricky part is what I've posted before in the past. Figuring out how to manage the data on a service tech laptops with spotty internet connections, the perfect storm for db corruption.