Macro does not work on OneDrive. It only works in local folder

Copper Contributor

Good to all, I need help with a macro that at the time of executing a command when I have the files loaded in a folder in Onedrive it does not run, but when I have all in a folder locally it takes them and runs super well. And now I need that it can be run from Onedrive since I have to share it with my boss and his boss and the file is changing day by day. Thank you very much and I hope someone can help me

The code I have for the routes is the following:

 

Ruta_Personas = ActiveWorkbook.Path & "\Imagenes\Personas\"

Ruta_Alertas = ActiveWorkbook.Path & "\Imagenes\Alertas\"

Ruta_Clientes = ActiveWorkbook.Path & "\Imagenes\Clientes\"

 

__________________

 

y parte del extendido es:

 

Function cargar_perfil(value As String, lbl_nombre As Object, lbl_apellido As Object, lbl_TerminoS As Object, lbl_rol As Object, lbl_dias_vacaciones As Object, _

 lbl_contraparte As Object, lbl_antiguedad As Object, img_persona As Variant, img_cliente As Variant, _

 img_alerta_servicio As Variant, img_alerta_token As Variant, img_alerta_vacaciones As Variant, img_cumpleanos As Variant)

 

Dim lookuprange2 As Range

 

Dim ubicacion As String, ubicacion2 As String, apellido_paterno As String, apellido_materno As String, _

rutaimagen As String, rutaimagen2 As String, rutaimagen3 As String, rutaimagen4 As String, _

rutaimagen5 As String, rutaimagen6 As String, num_dias_vacaciones As Integer, _

num_años As Integer, plural_singular As String, _

ubicación As String, num_alerta As Variant

 

Dim lookuprange As Range, Ruta_Personas, Ruta_Alertas, Ruta_Clientes

Set lookuprange = ThisWorkbook.Sheets("BBDD_General").Range("B1:CC100") 'rango donde buscar

Set lookuprange2 = ThisWorkbook.Sheets("BBDD_General").Range("B1:CC1") ' Primera columna

 

    Ruta_Personas = ActiveWorkbook.Path & "\Imagenes\Personas\"

    Ruta_Alertas = ActiveWorkbook.Path & "\Imagenes\Alertas\"

    Ruta_Clientes = ActiveWorkbook.Path & "\Imagenes\Clientes\"

 

        'Siguiente función para asignar nombre persona:

            ubicacion = Application.Match("Nombres", lookuprange2, 0)

            lbl_nombre.Caption = Application.VLookup(value, lookuprange, ubicacion, False)

            ubicacion = Empty

        'Siguiente función para asignar apellido persona:

            ubicacion = Application.Match("Apellido_Paterno", lookuprange2, 0)

            ubicacion2 = Application.Match("Apellido_Materno", lookuprange2, 0)

            apellido_paterno = Application.VLookup(value, lookuprange, ubicacion, False)

            apellido_materno = Application.VLookup(value, lookuprange, ubicacion2, False)

            lbl_apellido.Caption = apellido_paterno & apellido_materno

            ubicacion = Empty

            ubicacion2 = Empty

 

        'Siguiente función para asignar foto persona:

 

            ubicacion = Application.Match("Nombre_Persona", lookuprange2, 0)

            rutaimagen = Ruta_Personas & Application.VLookup(value, lookuprange, ubicacion, False) & ".png"

           ' img_persona.Fill.UserPicture (rutaimagen)

            If Dir(rutaimagen, vbDirectory) <> "" Then

            img_persona.Fill.UserPicture (rutaimagen)

            Else

            ubicacion = Application.Match("Sexo", lookuprange2, 0)

            rutaimagen = Ruta_Personas & Application.VLookup(value, lookuprange, ubicacion, False) & ".png"

            img_persona.Fill.UserPicture (rutaimagen)

            End If

 

Thanks

5 Replies

@Guille_Pazos  I'm struggling with a similar problem with saving items on sharepoint.  I believe the problem is that the path is an explorer/OS type of action while sharepoint/onedrive is driven through web api.  I have been able to make it work if I map a local drive to the sharepoint/onedrive location, but have no guarantee my colleagues have that drive mapped.  I have tried forcing a drive to get mapped, but haven't gotten that working right yet either (still trying).  I have also been able to changing the path from:

https://name.sharepoint.com/path... 

to

\\name.sharepoint.com@SSL\path...

but that only works if I have that server marked as a trusted server through IE

I don't know if any of that is of help but I will keep an eye here to see anyone else has more help

I am having a similar problem. Activeworkbook.Path shows https://mysp-cloud.kp.org/personal/.. .instead of C:\Users\<USERID>\OneDrive - <ORGNAME> ...

@Guille_Pazos If you store the file locally on the computer it will work, but I suppose it defeats the purpose of cloud storage.

 

Another option is to use Google drive as you don't have this problem when its in the cloud.

I recently needed to work with OneDrive files in Excel VBA. I wrote a blog post about it. Maybe this will help.

https://www.williammax.com/blog/how-to-work-with-onedrive-files-using-vba/