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.

 

[Norie]

Add a check on Filename to check it has a valid path and filename.

If Len(Dir(Filename)) = 0 Then
    MsgBox "Invalid filename entered."
    Exit Sub 
End If

ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select

 

[cerfani]

Well you didn't say what you want to happen if they cancel the filename selection. If they do cancel then Filename will be false and you decide what to do when false. This will Exit the sub if they cancel...

Filename = Application.GetOpenFilename _(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)


isBool = VarType(Filename) = vbBoolean


If isBool Then If Not Filename Then Exit Sub


ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select

 

[richo120792]

Well you didn't say what you want to happen if they cancel the filename selection. If they do cancel then Filename will be false and you decide what to do when false. This will Exit the sub if they cancel...

Filename = Application.GetOpenFilename _(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)


isBool = VarType(Filename) = vbBoolean


If isBool Then If Not Filename Then Exit Sub


ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select

thank you for the help, actually I just don't want it to crash so your code was perfect.

 

[cerfani]

your inbox is full so you cant receive messages... im replying to your pm here...

what if the user mistaken with the file he uploaded and wants to load a different one in the same position overwriting the one previously uploaded with the code you corrected:

Sub carga_archivos_modulo()
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
isBool = VarType(Filename) = vbBoolean
If isBool Then If Not Filename Then Exit Sub
ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select
End Sub

you need to check if the file exists before loading it and if it does exist then you delete it.

Look here... Deleting a file in VBA - Stack Overflow

 

[richo120792]

Hello, thanks for the help, I've already read the link and I have some questions:
1) In the first code how can I define the File so I can search for it with the second code? Does it has to be a string variable?

Sub carga_archivos_modulo()
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
isBool = VarType(Filename) = vbBoolean
If isBool Then If Not Filename Then Exit Sub
ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select
End Sub

2) Once I've determined the name of the files, how do I look for them, just looking for the filename I defined?

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub DeleteFile(ByVal FileToDelete As String)
If FileExists(FileToDelete) Then 'See above
SetAttr FileToDelete, vbNormal
Kill FileToDelete
End If
End Sub</code>3) Do I have to send the variable FileToDelete at the moment I call the Sub?

 

[cerfani]

Sub carga_archivos_modulo()    Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)


    isBool = VarType(Filename) = vbBoolean


    If isBool Then If Not Filename Then Exit Sub

    'this code checks if the filename exists and if it does it deletes it
    If Dir(Filename) <> "" Then
        SetAttr Filename, vbNormal
        Kill Filename
    End If


    ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
    , IconFileName:=Filename, _
    IconIndex:=0, IconLabel:=Filename).Select
End Sub

just add the code to your sub, i am guessing the above is what you wanted

 

[richo120792]

Thanks, something similar, but I can start working with that, I will try to explain the whole situation so you can understand what I'm trying to do, this code is for a form that I'm going to use in my work, the worksheet have 8 different cells the user can click to upload different files, I want to call the same Sub but send the String of the file name depending on which cell the user clicks, so actually I want to call the file as the string I sent to the Sub, and if the user clicks again look for the file, if it exists to ask him if he wants to overwrite it, if so to deleted as the way you do on your code, if not to continue.

 

[cerfani]

also here is the answer to your PM, it still won't allow me to reply... this is the error message when replying...

"richo120792 has exceeded their stored private messages quota and cannot accept further messages until they clear some space."

1. yes filenames are strings so pass something like "C:\file.xlsx"
2. Dir(filename) <> "" ... if that is true then you found it

look here https://msdn.microsoft.com/en-us/library/dk008ty4(v=vs.90).aspx

 

[richo120792]

Sorry about the PM, anytime I send or receive messages I have to delete it because MRExcel only allows me to have 1 stored message.
2.- I guessed it would work with that so I used it in the validation outside the Sub, hope it works.
1.- how would I determine the name of the file with the string that is sent to the Sub.

Sub carga_archivos_modulo(ByVal Nombre_Archivld As String) 'Declaration of the String received
Filename = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
isBool = VarType(Filename) = vbBoolean
If isBool Then If Not Filename Then Exit Sub
If Dir(Filename) <> "" Then
SetAttr Filename, vbNormal
Kill Filename
End If
ActiveSheet.OLEObjects.Add(Filename:=Filename, Link:=False, DisplayAsIcon:=True _
, IconFileName:=Filename, _
IconIndex:=0, IconLabel:=Filename).Select
End Sub

In other words how do I name the Filename with the string that is sent to the Sub?

Thanks for the help, sorry for being so annoying I'm new with macros.