Error in getopenfilename when closing without choosing a file

[richo120792]

Hello, I have the following code in a module and works fine, the problem is when the user closes the window without choosing any file the macro crashes and displays error 1004, I tried some codes from the forum but nothing worked.

Filename = Application.GetOpenFilename _
(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select


Thanks for the help.

 

[richo120792]

I analized my code, would it work with this change right:

ActiveSheet.OLEObjects.Add(Filename:=Nombre_Archivld,

 

[richo120792]

I am almost done but I am getting an error message with this code, none of the two options of adding OLEObject worked for me, I guees is something with data types but I don't know why it's failing:

Sub carga_archivos_modulo(Nombre_Archivld) 'Declaration of the String received
Dim sFile 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_Archivld) <> "" Then
SetAttr Nombre_Archivld, vbNormal
Kill Nombre_Archivld
End If
sFile = Dir(Filename)
If sFile = "" Then
ExtFind = "No file"
Else
ExtFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
End If
strCompFilePath = Nombre_Archivld & "." & ExtFind
MsgBox strCompFilePath
ActiveSheet.OLEObjects.Add(Filename:=Nombre_Archivld & "." & ExtFind, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, IconIndex:=0, IconLabel:=Filename).Select
' ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:=False, DisplayAsIcon:=True _
' , IconFileName:=Filename, IconIndex:=0, IconLabel:=Filename).Select

End Sub

THe MSGBOX was added to know the variable, and it was correct it was DTP.txt

 

[cerfani]

yeah that is how you reference the passed in argument/parameter anyways in another sub you need to then call this sub...

Call carga_archivos_modulo Filename

maybe put that in a selection change event and check the target cell to determine if you should call your sub and if so pass in the cell string value

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Address = "$A$1" Then
        Call carga_archivos_modulo CStr(Target.Value2)
    End If
End Sub

something like that will run when you click on a new cell, it will check the address and if it is an address that you know has a file name then call your sub with the cell value

 

[richo120792]

Yes I have already done that, as in the post above I have a problem with adding a OLEObject and don't know why:

I am almost done but I am getting an error message with this code, none of the two options of adding OLEObject worked for me, I guees is something with data types but I don't know why it's failing:

Sub carga_archivos_modulo(Nombre_Archivld) 'Declaration of the String received
Dim sFile 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_Archivld) <> "" Then
SetAttr Nombre_Archivld, vbNormal
Kill Nombre_Archivld
End If
sFile = Dir(Filename)
If sFile = "" Then
ExtFind = "No file"
Else
ExtFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
End If
strCompFilePath = Nombre_Archivld & "." & ExtFind
MsgBox strCompFilePath
ActiveSheet.OLEObjects.Add(Filename:=Nombre_Archivld & "." & ExtFind, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, IconIndex:=0, IconLabel:=Filename).Select
' ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:=False, DisplayAsIcon:=True _
' , IconFileName:=Filename, IconIndex:=0, IconLabel:=Filename).Select

End Sub

THe MSGBOX was added to know the variable, and it was correct it was DTP.txt

 

[cerfani]

ive never used OLEObjects before so no idea what causes them to error out. Just have to read this https://msdn.microsoft.com/EN-US/library/office/ff195728.aspx and make sure you are using correctly. Make sure you are passing in the proper parameters. Check when you debug and step through your code with F8 and use msgboxes to tell you stuff like you are already doing.

Also just google the specific error message you are getting. Stackoverflow is a good website to check, probably someone had the same problem and asked about it.