×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Non-reoccurring 400 error VBA Excel

Non-reoccurring 400 error VBA Excel

Non-reoccurring 400 error VBA Excel

(OP)
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

CODE

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 

RE: Non-reoccurring 400 error VBA Excel

Option Explicit is missing sad
current_section not defined sad
Your:
On Error Resume Next
always dangerous.

Did you try: Tools - Options... - General tab - Error Trapping 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

RE: Non-reoccurring 400 error VBA Excel

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

"C:\Users\username\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools"
to:
"Q:\remeng\OneDrive - HNI Corporation\Veneer Plan List\Industrial Eng Analysis Tools\Drive.BAT"

If so, wouldn't be easier to just do:

CODE

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

---- Andy

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

RE: Non-reoccurring 400 error VBA Excel

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?

RE: Non-reoccurring 400 error VBA Excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close