OLEObject error with variable filename

richo120792

New Member
Joined
Jan 25, 2016
Messages
12
Hello, I am having trouble with the next code, I want the Filename to be defined from a string variable which is received in the sub as Nombre_Archivo_Old, but I can't get it to work, I am printing the variable in order to see if it has the correct information, and it had DTP.txt so it was supposed to work, but I know that the code is not working because of some data type error, because if I place Filename:=Filename instead of Filename:=nombre, the code works but the OLEObject does not gets the right name.


Sub carga_archivos_modulo(Nombre_Archivo_Old) 'Declaration of the String received
Dim sFile As String
Dim nombre As String
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
isBool = VarType(Filename) = vbBoolean
If isBool Then If Not Filename Then Exit Sub
If Dir(Nombre_Archivo_Old) <> "" Then
SetAttr Nombre_Archivo_Old, vbNormal
Kill Nombre_Archivo_Old
End If
sFile = Dir(Filename)
If sFile = "" Then
ExtFind = "No file"
Else
ExtFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
End If
nombre$ = Nombre_Archivo_Old & "." & ExtFind
MsgBox nombre
ActiveSheet.OLEObjects.Add(Filename:=nombre, Link:=False, DisplayAsIcon:=True _
, IconFileName:=nombre, IconIndex:=0, IconLabel:=nombre).Select
' ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:=False, DisplayAsIcon:=True _
' , IconFileName:=Filename, IconIndex:=0, IconLabel:=Filename).Select

End Sub


Thank you for the help.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I will try to explain better what I want to do, the user can upload 8 different files by clicking 8 different cells, so when a cell is clicked the macro must assing a specific filename IE: cell8, which is the way I want to call the file with its specific extension (which is obtained from the file), I need to name the OLEObject in a specific way in the case the user wants to change the file he has already upload thing I achieve doing this:

Nom
If Dir(Nombre_Archivo) <> "" Then ' Nombre_Archivo would be Cell8 in our example SE_Archivo = MsgBox("File already exists" & vbCrLf & "Want to overwrite it?", vbYesNo, "¡Caution!")
End If

If SE_Archivo = 6 Then
Call carga_archivos_modulo(Nombre_Archivo)
SE_Archivo = 0

End If


the problem is that the file is not being saved because the code crashes at the moment I type this OLEObjects.Add(Filename:=nombre

another solution could be to look for the IconFIleName instead but I don't know how to do it
 
Upvote 0
Pretty sure you'll need to supply the path and filename, unless the files are in the same folder as the file with the code.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top