Non-reoccurring 400 error VBA Excel

[remeng]

So I have an interesting situation that I have ran into a wall on. When the macro is run in the VBA screen, I have zero errors. When I use a button (shape) located in the worksheet, exactly every other time I get a 400 error.

I've tried to use a "launching" macro that calls the main macro and added a simple message saying the macro is done. Same issue with ever other launch of the macro giving a 400 error.

I've tried setting a breakpoint on Sub map_q_drive_from_SP() and the error occurs before it gets to the breakpoint.

The code is launching a .BAT file. If I try putting the .BAT on a server location, no issue. When it is on the SharePoint site, that's when it errors 400. The .BAT file does work on SP and that is where the final home needs to be for it.

Any ideas?

Thanks,

Mike

Sub map_q_drive_from_SP()



Dim user_name As String
Dim total As Integer
Dim Result() As String
Dim strFolderName As String
Dim strFolderExists As String

On Error Resume Next

'Sets the username for access to SharePoint
    
    bat_file_location = "C:\Users\username\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools"
    
    user_name = VBA.Interaction.Environ$("UserName")    'Currently Logged In User Name
    
    
    'default value
    
    current_section = 0
    
    '----------
    
   
    Result = Split(bat_file_location, "\")
    
    total = UBound(Result())
    
    Do Until current_section > total
    
        section_value = section_value & Result(current_section)
    
        If current_section = 2 Then
        
            section_value = section_value & "\"
        
            current_section = current_section + 1
    
        ElseIf current_section = 0 Then
    
            section_value = Result(current_section)
        
    
        
            section_value = section_value & "\"
    
            current_section = current_section + 1
                    
                    
        ElseIf current_section = 1 Then
        
            
            section_value = section_value & "\" & user_name & "\"
                    
            current_section = current_section + 2
            
            
        ElseIf current_section = total Then
            
            section_value = section_value
            
            current_section = current_section + 1
            
            
        Else
              
    
        
        section_value = section_value & "\"
        
        
        current_section = current_section + 1
        
        
        End If
    
    
    
    Loop
    

bat_file = section_value & "\Drive.BAT"

'Check SharePoint connection


strFolderExists = Dir(bat_file, vbDirectory)

If strFolderExists = "" Then

        MsgBox "Cannot map the  Q:\   drive." & Chr(13) & Chr(13) & "Check your connection to SharePoint" & Chr(13) & Chr(13) & "\\Waygnl-flpin01p ", vbExclamation, "CANNOT MAP   Q:\   DRIVE"
        
        End
Else

        On Error GoTo end_macro

       
        
        ThisWorkbook.FollowHyperlink Address:=bat_file, NewWindow:=True 'Open Website
        
        
End If


'MsgBox section_value & "\Drive.BAT"




end_macro:





End Sub

 

[Andrzejek]

[tt]Option Explicit[/tt] is missing :-(
[tt]current_section[/tt] not defined :-(
Your:
[tt]On Error Resume Next[/tt]
always dangerous.

Did you try: [tt]Tools - Options... - General[/tt] tab - [tt]Error Trapping[/tt] frame
and set it to Break on All Errors option?

You code should stop at the error line when the error happens.

You can also see this: How To Fix VBA Error 400 Running An Excel Macro

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Do I see it right? Is the entire logic in [tt]Sub map_q_drive_from_SP()[/tt] to convert hard-coded:

[tt]"C:\Users\username\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools"[/tt]
to:
[tt]"Q:\[blue]remeng[/blue]\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools\Drive.BAT"
[/tt]
If so, wouldn't be easier to just do:

bat_file = "Q:\" & VBA.Interaction.Environ$("UserName") & "\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools\Drive.BAT"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Er, the code here does not appear to do any mapping nor accessing Sharepoint. What it seems to be doing is refactoring (somewhat laboured, as Andy points out) the path to a file on OneDrive, and then attempting to open that file via passing the refactored path as a file URI (not a URL) to FollowHyperlink.

So does this mean that the code we have been shown is NOT the code that is causing the problem, but some sort of test code? Or is the description of what you are trying to do inaccurate?

 

[remeng]

Hi guys,

SharePoint allows for you to "Add Shortcut to OneDrive" which acts like a shortcut stored in the OneDrive. Our desktops are saved to OneDrive and not linked to a C:\ drive. It allows for server backups I believe. Because the permissions for SharePoint are variable by the user, setting up the shortcut will keep the path to the SharePoint site constant. The only thing in the address string that changes is the username.

The code posted is the actual code that is being run. The code utilizes the username and the OneDrive path to navigate to the SharePoint folder via the OneDrive shortcut. I am running this code in other macros without any issue so I'd expect that it would normally work. Like I mentioned, ever other instance of the code being run is causing the 400 error.

Andy,

That is the first time I've seen that particular method. That definitely makes sense.

I haven't had time to play with this code since it is a low priority right now. I'll post in the future when I have some time to deep dive the 400 error's root cause.

Stay tuned!

Mike